WITH party_time AS (
SELECT
EXTRACT(YEAR FROM sd.date)::int AS year,
sd.house,
py.name AS party,
SUM(LENGTH(d.text)) AS total_chars
FROM "Document" d
JOIN "SittingDay" sd ON sd.id = d."sittingDayId"
JOIN "rawAuthor" ra ON ra.id = d."rawAuthorId"
JOIN "Parliamentarian" p ON p.id = ra."parliamentarianId"
JOIN "Service" sv
ON sv."parliamentarianId" = p.id
AND sd.date >= sv."startDate"
AND (sd.date <= sv."endDate" OR sv."endDate" IS NULL)
JOIN "Party" py ON py.id = sv."partyId"
WHERE d.type = 'speech'
AND d.text IS NOT NULL
AND d.text <> ''
GROUP BY 1, 2, 3
),
shares AS (
SELECT
year,
house,
total_chars::float
/ SUM(total_chars) OVER (PARTITION BY year, house) AS share
FROM party_time
)
SELECT
year,
house,
SUM(share^2) AS hhi
FROM shares
GROUP BY year, house
ORDER BY year, house