HackerRank SQL

Interviews

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 56
66556 79153 Angela 0 0 11 10
94828 80275 Frank 150 38 41 15

Solution(MySQL):

select ct.contest_id, ct.hacker_id, ct.name, sum(total_submissions), sum(total_accepted_submissions),
sum(total_views), sum(total_unique_views)
from Contests ct
join Colleges cl
on ct.contest_id = cl.contest_id
join Challenges ch
on cl.college_id = ch.college_id
left join
(
select challenge_id, sum(total_views) as total_views, sum(total_unique_views) as total_unique_views
from View_Stats
group by 1
) vs
on ch.challenge_id = vs.challenge_id
left join
(
select challenge_id, sum(total_submissions) as total_submissions, sum(total_accepted_submissions) as total_accepted_submissions
from Submission_Stats
group by 1
) ss
on ch.challenge_id = ss.challenge_id
group by 1, 2, 3
having sum(total_submissions) != 0
or sum(total_accepted_submissions) != 0
or sum(total_views) != 0
or sum(total_unique_views) != 0
order by 1
;

Link

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Isabelle

Isabelle

In love with telling stories with data