Finding the marginal seats after an election

WITH t AS (
  SELECT * FROM read_parquet('open/elections_federal/aec-2004-2025/house_tpp_division.parquet')
), nat AS (
  SELECT election_year,
         100.0 * SUM(alp_votes) / SUM(total_votes) AS alp_tpp,
         SUM(swing_pct * total_votes) / SUM(total_votes) AS published_vw
  FROM t GROUP BY 1
), anchored AS (
  SELECT election_year,
         alp_tpp - LAG(alp_tpp) OVER (ORDER BY election_year) AS national_swing,
         published_vw
  FROM nat
), factor AS (
  SELECT election_year,
         CASE WHEN national_swing IS NULL THEN NULL
              WHEN SIGN(national_swing) = SIGN(published_vw) THEN 1 ELSE -1 END AS sign_factor
  FROM anchored
), swing_2025 AS (
  SELECT t.division_name,
         CASE WHEN ABS(t.swing_pct) = ABS(t.alp_pct) THEN NULL
              ELSE t.swing_pct * f.sign_factor END AS swing_to_labor
  FROM t JOIN factor f USING (election_year)
  WHERE t.election_year = 2025
)
SELECT d.division_name,
       ROUND(d.margin_pct, 2) AS margin_pct,
       ROUND(s.swing_to_labor, 2) AS swing_to_labor_since_2022,
       ROUND(d.area_change_pct_since_2022, 1) AS boundary_change_pct,
       ROUND(d.median_hh_income_weekly_avg) AS seat_average_of_sa2_median_household_income_weekly,
       ROUND(d.median_age_avg, 1) AS seat_average_of_sa2_median_age,
       ROUND(d.bushfire_cell_share_pct, 1) AS bushfire_prone_land_share_pct,
       d.is_classic,
       d.changed_party_since_2022
FROM read_parquet('open/division_profile/2026-09/division_profile.parquet') d
JOIN swing_2025 s USING (division_name)
WHERE d.state = 'NSW'
  AND d.member_party_ab = 'ALP'
  AND d.margin_pct < 6
ORDER BY d.margin_pct

python3 - <<'EOF'
import zn
con=zn.lake_con()
T=zn.read('elections_federal',file='house_tpp_division')
DP=zn.read('division_profile')
con.execute(f'''CREATE OR REPLACE TEMP TABLE swing_2025 AS
WITH t AS (SELECT * FROM {T}), nat AS (
 SELECT election_year, 100.0*SUM(alp_votes)/SUM(total_votes) AS alp_tpp,
 SUM(swing_pct*total_votes)/SUM(total_votes) AS published_vw FROM t GROUP BY 1),
a AS (SELECT election_year, alp_tpp-LAG(alp_tpp) OVER (ORDER BY election_year) AS national_swing, published_vw FROM nat),
f AS (SELECT election_year, CASE WHEN national_swing IS NULL THEN NULL WHEN SIGN(national_swing)=SIGN(published_vw) THEN 1 ELSE -1 END AS sign_factor FROM a)
SELECT t.division_name, CASE WHEN ABS(t.swing_pct)=ABS(t.alp_pct) THEN NULL ELSE t.swing_pct*f.sign_factor END AS swing_to_labor
FROM t JOIN f USING(election_year) WHERE t.election_year=2025''')
seat_sql=f'''SELECT d.division_name AS "Seat", ROUND(d.margin_pct,2) AS "Margin (percentage points)", ROUND(s.swing_to_labor,2) AS "Swing to Labor since 2022 (percentage points)", ROUND(d.area_change_pct_since_2022,1) AS "Boundary change since 2022 (%)", ROUND(d.median_hh_income_weekly_avg) AS "Seat average of SA2 median household income (A$/week)", ROUND(d.median_age_avg,1) AS "Seat average of SA2 median age (years)", ROUND(d.bushfire_cell_share_pct,1) AS "Bushfire-prone land share (%)" FROM {DP} d JOIN swing_2025 s USING(division_name) WHERE d.state='NSW' AND d.member_party_ab='ALP' AND d.margin_pct<6 ORDER BY d.margin_pct'''
zn.write_out(con,'nsw_labor_under6_seats',seat_sql)
# chart data
zn.write_out(con,'nsw_labor_under6_scatter',f'''SELECT d.division_name AS "Seat", d.margin_pct AS margin_pct, s.swing_to_labor AS swing_to_labor FROM {DP} d JOIN swing_2025 s USING(division_name) WHERE d.state='NSW' AND d.member_party_ab='ALP' AND d.margin_pct<6''')
# tightest is Banks, booths
TPP=zn.read('elections_federal',file='house_tpp_polling_place'); PP=zn.read('elections_federal',file='polling_places')
zn.write_out(con,'banks_booths',f'''SELECT t.polling_place AS "Booth", p.premises_suburb AS "Suburb", p.lat, p.lng, ROUND(t.alp_pct,1) AS labor_tpp_share_pct, t.total_votes AS ordinary_formal_votes FROM {TPP} t JOIN {PP} p USING(election_year,division_id,polling_place_id) WHERE t.election_year=2025 AND t.division_name='Banks' AND p.lat IS NOT NULL ORDER BY t.alp_pct DESC''')
print(con.sql(f'''SELECT COUNT(*) AS seats, MIN(margin_pct) AS tightest_margin, MAX(margin_pct) AS widest_margin FROM {DP} WHERE state='NSW' AND member_party_ab='ALP' AND margin_pct<6'''))
print(con.sql(f'''SELECT COUNT(*) AS total_booths, COUNT(*) FILTER(WHERE p.lat IS NOT NULL) AS mappable FROM {TPP} t LEFT JOIN {PP} p USING(election_year,division_id,polling_place_id) WHERE t.election_year=2025 AND t.division_name='Banks' '''))
EOF