Hacker Rank SQL

Interviews Solution

Hello Friends in this article i am gone to share Hacker Rank SQL Solutions with you | Interviews Solution


Also Visit:ย  Symmetric Pairs Solution


 

Problem

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

Explanation

The contestย 66406ย is used in the collegeย 11219. In this collegeย 11219, challengesย 18765ย andย 47127ย are asked, so from theย viewย andย submissionย stats:

  • Sum of total submissionsย = 27 + 56 + 28 = 111
  • Sum of total accepted submissionsย = 10 + 18 + 11 = 39ย 
  • Sum of total viewsย = 43 + 72 + 26 + 15 = 156
  • Sum of total unique viewsย = 10 + 13 + 19 + 14 = 56ย 

Similarly, we can find the sums for contestsย 66556ย andย 94828.

Solution โ€“ Interviews

MySQL Code
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ย conย 
joinย collegesย colย onย con.contest_idย =ย col.contest_idย 
joinย challengesย chaย onย ย col.college_idย =ย cha.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ย challenge_id)ย vsย onย cha.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ย challenge_id)ย ssย onย cha.challenge_idย =ย ss.challenge_id
ย ย ย ย groupย byย con.contest_id,ย con.hacker_id,ย con.name
ย ย ย ย ย ย ย ย havingย sum(total_submissions)!=0ย orย 
ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย sum(total_accepted_submissions)!=0ย or
ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย sum(total_views)!=0ย or
ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย ย sum(total_unique_views)!=0

ย ย ย ย ย ย ย ย ย ย ย ย orderย byย contest_id;

 

 

Disclaimer: The above Problems are generated by Hacker Rank but the Solutions are Provided by NYANDER.COM. All Hacker Rank SQL Solutions Shared only for Educational and Learning Purpose.