report.sql

SELECT fundables.id AS project_id,
	DATE_PART('year', fundables.started_at) AS start_year,
	DATE_PART('year', fundables.started_at + INTERVAL '1 day' * fundables.duration) AS end_year,
	ARRAY_AGG(pledges.id) AS pledge_ids
FROM fundables
	INNER JOIN pledges
		ON pledges.project_id = fundables.id
WHERE type = 'Project'
	AND fundables.successful = 'f'
	AND DATE_PART('year', fundables.started_at) != DATE_PART('year', fundables.started_at + INTERVAL '1 day' * fundables.duration)
GROUP BY fundables.id