Materialized view for debate league ladder rankings

ladders.sql

create materialized view ladder as (
with
	match_points as (
		select 
			case ... end as aff_points,
			case ... end as neg_points,
			aff_id,
			neg_id
		),

	team_ranks as (
		select 
			team_id, avg(rank) as avg_rank
		from 
			debate_score
		where
		 	rank is not null and official = true
		group by team_id
		),

	ladder_figures as (
		select 
			t.id as team_id, avg_rank, sum(
								case
								 when aff_id = t.id then aff_points
								 when neg_id = t.id then neg_points
								end
								) as points
		from teams t
		left join team_ranks r on t.id = r.team_id
		join match_points p on t.id in (p.neg_id, p.aff_id)
		),

	tied_teams as (
		select array_agg(team_id) as ids
			 from ladder_figures
			 group by (avg_rank, points)
			 having count(distinct team_id) > 1)
		),

	h2h as (
		select
			unnest(t.ids) as team_id, 
			sum(
				(unnest(t.ids) = d.aff_id)::int * case when outcome like 'A%' then 1 else 0 end
			  + (unnest(t.ids) = d.neg_id)::int * case when outcome like 'N%' then 1 else 0 end
			) as wins
		from
			debate_debates d
		join 
			tied_teams t
		on (d.aff_id = ANY(t.ids) and d.neg_id = ANY(t.ids))
		group by unnest(t.ids)
		)

	select 
		row_number(), l.id, l.avg_rank, l.points, h.wins
	from
		ladder_figures l
	left join
		h2h h
	on h.team_id = l.team_id
	order by (l.points, l.avg_rank, h.wins)
	)