You are viewing a single comment's thread. Return to all comments →
Here is General answer.
Submission_Stats and View_Stats are score tables,
Submission_Stats
View_Stats
Due to the relationship is
contest 1---* college 1---*challenges
So we can useUNION ALL combine Submission_Stats and View_Stats tables, then use a new column to represent type of which table.
UNION ALL
Then do OUTER JOIN with condition aggregate function get the answer.
OUTER JOIN
SELECT cts.Contest_Id, cts.hacker_Id, cts.Name, SUM(CASE WHEN t1.types = 'ss' THEN val1 ELSE 0 END), SUM(CASE WHEN t1.types = 'ss' THEN val2 ELSE 0 END), SUM(CASE WHEN t1.types = 'vs' THEN val1 ELSE 0 END), SUM(CASE WHEN t1.types = 'vs' THEN val2 ELSE 0 END) FROM Contests cts JOIN Colleges c ON c.Contest_Id = cts.Contest_Id JOIN Challenges clg ON clg.College_id = c.College_id LEFT JOIN ( SELECT Challenge_id , total_Submissions val1, total_accepted_Submissions val2, 'ss' types FROM Submission_Stats ss UNION ALL SELECT Challenge_id, total_views, total_unique_views, 'vs' FROM View_Stats vs ) t1 ON t1.Challenge_id = clg.Challenge_id GROUP BY cts.Contest_Id, cts.hacker_Id, cts.Name ORDER BY cts.Contest_Id, cts.hacker_Id
Seems like cookies are disabled on this browser, please enable them to open this website
Interviews
You are viewing a single comment's thread. Return to all comments →
Here is General answer.
Submission_Stats
andView_Stats
are score tables,Due to the relationship is
contest 1---* college 1---*challenges
So we can use
UNION ALL
combineSubmission_Stats
andView_Stats
tables, then use a new column to represent type of which table.Then do
OUTER JOIN
with condition aggregate function get the answer.