-- ???(mjbo) I had to do this because I couldn't write a nested aggregation
-- `FILTER (WHERE CountNum < MAX(CountNum) - 1)` which seems a little silly.
-- Am I holding this wrong?
WITH max_rounds_per_division AS (
SELECT DivisionId, MAX(CountNum) :: INT as MaxCountNum
FROM HouseDopByPP2022
-- TODO(mjbo) Fix Parquet file as CSV headers have polluted the combined table
WHERE CountNum != 'CountNum'
GROUP BY (DivisionId)
)
SELECT
HouseDopByPP2022.DivisionId, ANY_VALUE(DivisionNm) as DivisionNm,
PPId, ANY_VALUE(PPNm) as PPNm,
CandidateId, ANY_VALUE(Surname) as Surname, ANY_VALUE(PartyAb) as PartyAb,
SUM(CalculationValue::INT) FILTER (WHERE CountNum < MaxCountNum - 1) :: INT as votes
FROM HouseDopByPP2022, max_rounds_per_division
WHERE
HouseDopByPP2022.DivisionId = max_rounds_per_division.DivisionId
AND (CalculationType = 'Preference Count'
OR CalculationType = 'Transfer Count'
)
GROUP BY (HouseDopByPP2022.DivisionId, PPId, CandidateId)