Узгодженість голосування фракцій

Custom SQL query returning 9 rows (hide)

WITH faction_votes AS (SELECT PD_NPP, Faction, DPGolos, COUNT(*) as vote_count FROM roll_call_voting_kmr GROUP BY PD_NPP, Faction, DPGolos), faction_totals AS (SELECT PD_NPP, Faction, SUM(vote_count) as total FROM faction_votes GROUP BY PD_NPP, Faction), faction_max AS (SELECT fv.PD_NPP, fv.Faction, MAX(fv.vote_count) as max_votes FROM faction_votes fv GROUP BY fv.PD_NPP, fv.Faction) SELECT ft.Faction, COUNT(DISTINCT ft.PD_NPP) as total_votings, ROUND(AVG(100.0 * fm.max_votes / ft.total), 2) as avg_alignment_percent FROM faction_totals ft JOIN faction_max fm ON ft.PD_NPP = fm.PD_NPP AND ft.Faction = fm.Faction GROUP BY ft.Faction ORDER BY avg_alignment_percent DESC

Edit SQL