Interviews — HackerRank Advanced SQL Question ( cool )
Samantha interviews many candidates from different colleges using coding challenges and contests. Write a query to print the contest_id, hacker_id, name, and the sums of total_submissions, total_accepted_submissions, total_views, and total_unique_views for each contest sorted by contest_id. Exclude the contest from the result if all four sums are .
Note: A specific contest can be used to screen candidates at more than one college, but each college only holds screening contest.
The following tables hold interview data:
- Contests: The contest_id is the id of the contest, hacker_id is the id of the hacker who created the contest, and name is the name of the hacker.
- Colleges: The college_id is the id of the college, and contest_id is the id of the contest that Samantha used to screen the candidates.
- Challenges: The challenge_id is the id of the challenge that belongs to one of the contests whose contest_id Samantha forgot, and college_id is the id of the college where the challenge was given to candidates.
- View_Stats: The challenge_id is the id of the challenge, total_views is the number of times the challenge was viewed by candidates, and total_unique_views is the number of times the challenge was viewed by unique candidates.
- Submission_Stats: The challenge_id is the id of the challenge, total_submissions is the number of submissions for the challenge, and total_accepted_submission is the number of submissions that achieved full scores.
66406 17973 Rose 111 39 156 56
66556 79153 Angela 0 0 11 10
94828 80275 Frank 150 38 41 15
- Very slow join solution
Sum(total_submissions) AS tot_subs,
Sum(total_accepted_submissions) AS tot_act_subs,
Sum(total_views) AS tot_vw,
Sum(total_unique_views) AS tot_uni_vw
FROM view_stats vs
JOIN (submission_stats, challenges, colleges, contests)
vs.challenge_id = submission_stats.challenge_id
AND vs.challenge_id = challenges.challenge_id
AND challenges.college_id = colleges.college_id
AND colleges.contest_id = contests.contest_id)
GROUP BY contests.contest_id,
HAVING (tot_subs + tot_act_subs +tot_vw + tot_uni_vw) != 0
ORDER BY contests.contest_id
Enter your query here.
Please append a semicolon ";" at the end of the query and enter your query in a single line to avoid error.
*/WITH SUM_View_Stats AS (
, sum(total_views) as total_views
, sum(total_unique_views) as total_unique_views
GROUP BY challenge_id
,SUM_Submission_Stats AS (
, sum(total_submissions) as total_submissions
, sum(total_accepted_submissions) as total_accepted_submissions
GROUP BY challenge_id
FROM Contests con
INNER JOIN Colleges col
ON con.contest_id = col.contest_id
INNER JOIN Challenges cha
ON cha.college_id = col.college_id
LEFT JOIN SUM_View_Stats vs
ON vs.challenge_id = cha.challenge_id
LEFT JOIN SUM_Submission_Stats ss
ON ss.challenge_id = cha.challenge_id
GROUP BY con.contest_id,con.hacker_id,con.name
+sum(total_unique_views)) <> 0
ORDER BY con.contest_ID;