# 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.

Input Format

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.

Sample Input

Contests Table:

Colleges Table:

Challenges Table:

View_Stats Table:

Submission_Stats Table:

Sample Output

`66406 17973 Rose 111 39 156 5666556 79153 Angela 0 0 11 1094828 80275 Frank 150 38 41 15`

Explanation

Solution

1. Very slow join solution
`SELECT   contests.contest_id,          hacker_id,          NAME,          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) ON       (                   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,          hacker_id,          NAME HAVING   (tot_subs + tot_act_subs +tot_vw + tot_uni_vw) != 0 ORDER BY contests.contest_id`

3.

`/*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 (SELECT challenge_id    , sum(total_views) as total_views     , sum(total_unique_views) as total_unique_views FROM View_Stats GROUP BY challenge_id),SUM_Submission_Stats AS (SELECT challenge_id    , sum(total_submissions) as total_submissions     , sum(total_accepted_submissions) as total_accepted_submissions  FROM Submission_Stats GROUP BY challenge_id)SELECT    con.contest_id        , con.hacker_id        , con.name        , SUM(total_submissions)        , sum(total_accepted_submissions)        , sum(total_views)        , sum(total_unique_views)FROM Contests conINNER JOIN Colleges col    ON con.contest_id = col.contest_idINNER JOIN Challenges cha    ON cha.college_id = col.college_idLEFT JOIN SUM_View_Stats vs    ON vs.challenge_id = cha.challenge_idLEFT JOIN SUM_Submission_Stats ss    ON ss.challenge_id = cha.challenge_idGROUP BY con.contest_id,con.hacker_id,con.nameHAVING (SUM(total_submissions)        +sum(total_accepted_submissions)        +sum(total_views)        +sum(total_unique_views)) <> 0ORDER BY con.contest_ID;`

--

--

## More from Interview help, 1:1 mocks, strategies, AI, ML, DS

Mentor and prepare candidates for interviews. I write about solutions to known and upcoming problems. I educate and talk about mental health too.

## Get the Medium app

Mentor and prepare candidates for interviews. I write about solutions to known and upcoming problems. I educate and talk about mental health too.