From cc6974a9fd40c60cc99785615b2b2d1e80ab91c6 Mon Sep 17 00:00:00 2001 From: Marius Gavrilescu Date: Tue, 31 Mar 2015 11:27:10 +0300 Subject: [PATCH] Simplify user_list --- db.sql | 33 ++++++--------------------------- 1 file changed, 6 insertions(+), 27 deletions(-) diff --git a/db.sql b/db.sql index 9343d0b..44e2420 100644 --- a/db.sql +++ b/db.sql @@ -161,36 +161,15 @@ CREATE OR REPLACE VIEW user_data AS (SELECT FROM users ); -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); - CREATE OR REPLACE VIEW user_list AS (SELECT dt.*, - COALESCE(array_length(solved, 1), 0) AS solved, - COALESCE(array_length(attempted, 1), 0) AS attempted, - COALESCE(array_length(contests, 1), 0) AS contests + COALESCE(solved, 0) as solved, + COALESCE(attempted, 0) as attempted, + COALESCE(contests, 0) as contests FROM user_data dt - JOIN user_contests ct USING (id) - JOIN user_solved_problems sp USING (id) - JOIN user_attempted_problems ap USING (id) + LEFT JOIN (SELECT owner as id, COUNT(*) as solved FROM problem_status WHERE solved=TRUE GROUP BY owner) ps USING (id) + LEFT JOIN (SELECT owner as id, COUNT(*) as attempted FROM problem_status WHERE solved=FALSE GROUP BY owner) pa USING (id) + LEFT JOIN (SELECT owner as id, COUNT(*) as contests FROM contest_status GROUP BY owner) ct USING (id) ORDER BY solved DESC, attempted DESC, id); CREATE OR REPLACE VIEW contest_entry AS (SELECT -- 2.39.2