SIMPLIFYING ANALYSIS FOR COMPLEX QUESTIONS
SELECT
EXTRACT(YEAR FROM doc."date") AS year,
prt.name AS party,
COUNT(*) FILTER (WHERE doc."text" ILIKE '%Climate Change%') AS
climate_change_count,
COUNT(*) AS total_speeches
FROM
"Document" doc
JOIN "rawAuthor" rau ON doc."rawAuthorId" = rau.id
JOIN "Parliamentarian" par ON rau."parliamentarianId" = par.id
JOIN "Service" svc ON par.id = svc."parliamentarianId"
AND doc."date" BETWEEN svc."startDate" AND svc."endDate"
JOIN "Party" prt ON prt.id = svc."partyId"
WHERE
prt.name IN ('Australian Greens', 'Australian Labor Party', 'Independent',
'Liberal Party of Australia', 'The Nationals')
AND EXTRACT(YEAR FROM doc."date") > 1990
GROUP BY year, party
ORDER BY year, party;