# HackerRank: [SQL Advanced Join] (4/5) INTERVIEWS | inner & left join, having, sum in SQL

I started studying SQL from a very famous site - HackerRank. Here I will try to provide multiple approaches & solutions to the same problem. It will help you learn and understand SQL in a better way.

Please make use of my blog posts for learning purpose only and feel free to ask your questions in the comment box below in case of any doubt.

SQL Problem Statement:

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

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:

Sample Input:

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-1: USING INNER, LEFT JOIN & HAVING (MySQL Query):

```SELECT C.contest_id, C.hacker_id, C.name, SUM(SS.total_submissions), SUM(SS.total_accepted_submissions), SUM(VV.total_views), SUM(VV.total_unique_views)
FROM Contests C
JOIN Colleges Cl ON C.contest_id = Cl.contest_id
JOIN Challenges Ch ON Cl.college_id = Ch.college_id
LEFT JOIN (SELECT S.challenge_id, SUM(S.total_submissions) as total_submissions, SUM(S.total_accepted_submissions) as total_accepted_submissions FROM Submission_Stats S GROUP BY S.challenge_id) SS
ON Ch.challenge_id=SS.challenge_id
LEFT JOIN (SELECT V.challenge_id, SUM(V.total_views) as total_views, SUM(V.total_unique_views) as total_unique_views FROM View_Stats V GROUP BY V.challenge_id) VV
ON Ch.challenge_id=VV.challenge_id
GROUP BY C.contest_id, C.hacker_id, C.name
HAVING !(SUM(SS.total_submissions)=0 AND SUM(SS.total_accepted_submissions)=0 AND SUM(VV.total_views)=0 AND SUM(VV.total_unique_views)=0)
ORDER BY C.contest_id;```

NOTE:
• The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions.

• JOIN and INNER JOIN are the same in SQL. It returns the records that have matching values in both tables.

• LEFT JOIN returns all the records from LEFT Table and records with matching values from Right table.

### Solution-2: USING INNER, LEFT JOIN & HAVING (MySQL Query):

```SELECT C.contest_id, C.hacker_id, C.name, SUM(SS.total_submissions), SUM(SS.total_accepted_submissions), SUM(VV.total_views), SUM(VV.total_unique_views)
FROM Contests C
JOIN Colleges Cl ON C.contest_id = Cl.contest_id
JOIN Challenges Ch ON Cl.college_id = Ch.college_id
LEFT JOIN (SELECT S.challenge_id, SUM(S.total_submissions) as total_submissions, SUM(S.total_accepted_submissions) as total_accepted_submissions FROM Submission_Stats S GROUP BY S.challenge_id) SS
ON Ch.challenge_id=SS.challenge_id
LEFT JOIN (SELECT V.challenge_id, SUM(V.total_views) as total_views, SUM(V.total_unique_views) as total_unique_views FROM View_Stats V GROUP BY V.challenge_id) VV
ON Ch.challenge_id=VV.challenge_id
GROUP BY C.contest_id, C.hacker_id, C.name
HAVING SUM(SS.total_submissions)+
SUM(SS.total_accepted_submissions)+
SUM(VV.total_views)+
SUM(VV.total_unique_views)>0
ORDER BY C.contest_id;```

NOTE:
• The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions.

• JOIN and INNER JOIN are the same in SQL. It returns the records that have matching values in both tables.

• LEFT JOIN returns all the records from LEFT Table and records with matching values from Right table.

### Expected Output:

```845 579 Rose 1987 580 1635 566
858 1053 Angela 703 160 1002 384
883 1055 Frank 1121 319 1217 338
1793 2655 Patrick 1337 360 1216 412
2374 2765 Lisa 2733 815 3368 904
2963 2845 Kimberly 4306 1221 3603 1184
3584 2873 Bonnie 2492 652 3019 954
4044 3067 Michael 1323 449 1722 528
4249 3116 Todd 1452 376 1767 463
4269 3256 Joe 1018 372 1766 530
4483 3386 Earl 1911 572 1644 477
4541 3608 Robert 1886 516 1694 504
```

--------------------------------------------------------------------------------