# HackerRank: [SQL Basic Join] (8/8) CONTEST LEADERBOARD | inner join, having & Sub-Query 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:

You did such a great job helping Julia with her last coding contest challenge that she wants you to work on this one, too!

The total score of a hacker is the sum of their maximum scores for all of the challenges. Write a query to print the hacker_id, name, and total score of the hackers ordered by the descending score. If more than one hacker achieved the same total score, then sort the result by ascending hacker_id. Exclude all hackers with a total score of 0 from your result.

Input Format:

The following tables contain contest data:

• Hackers: The hacker_id is the id of the hacker, and name is the name of the hacker.

• Submissions: The submission_id is the id of the submission, hacker_id is the id of the hacker who made the submission, challenge_id is the id of the challenge for which the submission belongs to, and score is the score of the submission.

Sample Input:

Hackers Table:

Challenges Table:

Sample Output:

`4071 Rose 191 74842 Lisa 174 84072 Bonnie 100 4806 Angela 89 26071 Frank 85 80305 Kimberly 67 49438 Patrick 43`

Explanation:

Hacker 4071 submitted solutions for challenges 19797 and 49593, so the total score = 95 + max(43,96) = 191.

Hacker 74842 submitted solutions for challenges 19797 and 63132, so the total score = max(98,5) + 76 = 174.

Hacker 84072 submitted solutions for challenges 49593 and 63132, so the total score = 100 + 0 = 100.

The total scores for hackers 4806, 26071, 80305, and 49438 can be similarly calculated.

### Solution: Using INNER JOIN, HAVING & SUB-QUERY (MySQL Query):

`SELECT H.hacker_id, H.name, SUM(M.max_score) AS total_scoreFROM (    SELECT S.hacker_id, S.challenge_id, max(S.score) as max_score    FROM Submissions S GROUP BY S.hacker_id, S.challenge_id    ) MJOIN Hackers H ON H.hacker_id = M.hacker_idGROUP BY H.hacker_id, H.nameHAVING total_score > 0ORDER BY total_score DESC, H.hacker_id ASC;`

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

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

### Expected Output:

`76971 Ashley 76084200 Susan 71076615 Ryan 70082382 Sara 64079034 Marilyn 58078552 Harry 57074064 Helen 54078688 Sean 54083832 Jason 54072796 Jose 51076216 Carlos 51090304 Lillian 500`

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