We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
  1. Prepare
  2. SQL
  3. Advanced Join
  4. Interviews
  5. Discussions

Interviews

  • dog830228
     + 0 comments

    Here is General answer.

    Submission_Stats and View_Stats are score tables,

    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.

    Then do OUTER JOIN with condition aggregate function get the answer.

    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