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