-CREATE OR REPLACE VIEW user_solved_problems AS (SELECT
- us.id,
- COALESCE(array_agg(ps.problem) FILTER (WHERE ps.problem IS NOT NULL), ARRAY[]::text[]) AS solved
- FROM users us
- LEFT JOIN (SELECT * FROM problem_status WHERE solved = TRUE) ps ON ps.owner=id
- GROUP BY us.id);
-
-CREATE OR REPLACE VIEW user_attempted_problems AS (SELECT
- us.id,
- COALESCE(array_agg(ps.problem) FILTER (WHERE ps.problem IS NOT NULL), ARRAY[]::text[]) AS attempted
- FROM users us
- LEFT JOIN (SELECT * FROM problem_status WHERE solved = FALSE) ps ON ps.owner=id
- GROUP BY us.id);
-
-CREATE OR REPLACE VIEW user_contests AS (SELECT
- us.id,
- COALESCE(array_agg(cs.contest) FILTER (WHERE cs.contest IS NOT NULL), ARRAY[]::text[]) AS contests
- FROM users us
- LEFT JOIN contest_status cs ON cs.owner=id
- GROUP BY us.id);
-