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

Recommended SQL Courses:

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 5666556 79153 Angela 0 0 11 1094828 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 CJOIN Colleges Cl ON C.contest_id = Cl.contest_idJOIN 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) SSON Ch.challenge_id=SS.challenge_idLEFT 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) VVON Ch.challenge_id=VV.challenge_idGROUP BY C.contest_id, C.hacker_id, C.nameHAVING !(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 CJOIN Colleges Cl ON C.contest_id = Cl.contest_idJOIN 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) SSON Ch.challenge_id=SS.challenge_idLEFT 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) VVON Ch.challenge_id=VV.challenge_idGROUP BY C.contest_id, C.hacker_id, C.nameHAVING SUM(SS.total_submissions)+SUM(SS.total_accepted_submissions)+SUM(VV.total_views)+SUM(VV.total_unique_views)>0ORDER 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 566858 1053 Angela 703 160 1002 384883 1055 Frank 1121 319 1217 3381793 2655 Patrick 1337 360 1216 4122374 2765 Lisa 2733 815 3368 9042963 2845 Kimberly 4306 1221 3603 11843584 2873 Bonnie 2492 652 3019 9544044 3067 Michael 1323 449 1722 5284249 3116 Todd 1452 376 1767 4634269 3256 Joe 1018 372 1766 5304483 3386 Earl 1911 572 1644 4774541 3608 Robert 1886 516 1694 504`

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