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

  • connorjennison
     + 0 comments

    Hello,

    I ended up getting rid of the last CTE I had made and instead putting it all in the final select statement similar to yours, but at first I had the following and for some rows I got the wrong output (lower than expected), althought most were correct. Can somebody help me understanad why?

    Thank you

    WITH A AS (
SELECT challenge_id, SUM(total_submissions) AS total_challenge_submissions, SUM(total_accepted_submissions) AS total_challenge_accepted_submissions
FROM Submission_Stats
GROUP BY challenge_id),

B AS (
SELECT challenge_id, SUM(total_views) AS total_challenge_views, SUM(total_unique_views) AS total_challenge_unique_views
FROM View_Stats
GROUP BY challenge_id), 

/*In final solution, this CTE was deleted and logic moved down to final SELECT statement*/
C AS (
SELECT 
    hacker.hacker_id AS hacker_id, 
    hacker.name AS name, 
    college.contest_id AS contest_id, 
    college.college_id AS college_id,  
    challenge.challenge_id AS challenge_id
FROM Contests AS hacker
JOIN Colleges college ON hacker.contest_id = college.contest_id
JOIN Challenges AS challenge ON college.college_id = challenge.college_id)

SELECT
    C.contest_id, 
    C.hacker_id, 
    C.name, 
    SUM(A.total_challenge_submissions), 
    SUM(A.total_challenge_accepted_submissions), 
    SUM(B.total_challenge_views), 
    SUM(B.total_challenge_unique_views)
FROM C
LEFT JOIN B ON C.challenge_id = B.challenge_id
LEFT JOIN A ON A.challenge_id = B.challenge_id
GROUP BY C.contest_id, C.hacker_id, C.name
HAVING 
    SUM(A.total_challenge_submissions)  
    + SUM(A.total_challenge_accepted_submissions) 
    + SUM(B.total_challenge_views) 
    + SUM(B.total_challenge_unique_views) <> 0
ORDER BY C.contest_id;