upgrade-20210924.sql

SET ROLE 'mo_osmo';

CREATE VIEW region_descendants AS
	WITH RECURSIVE descendant_regions(place_id, descendant) AS (
		SELECT place_id, place_id FROM places WHERE level BETWEEN 1 AND 2
		UNION SELECT r.place_id, p.place_id
		      FROM descendant_regions r, places p
		      WHERE p.parent = r.descendant
	) SELECT place_id AS region, descendant FROM descendant_regions;

CREATE VIEW region_stats AS
	SELECT c.round_id, rd.region, p.state, count(*)
	FROM participations p
	JOIN contests c USING(contest_id)
	JOIN region_descendants rd ON rd.descendant = c.place_id
	GROUP BY c.round_id, rd.region, p.state;