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.

Click Here for the previous blog-post in the series.

Recommended SQL Courses:

1. Udemy: SQL - MySQL for Data Analytics and Business Intelligence
2. LinkedIn: Master SQL for Data Science
3. edX: Databases: Advanced Topics in SQL
4. edureka: SQL Essentials Training & Certification
5. Coursera: Learn SQL Basics for Data Science
6. Eduonix: SQL Queries

---

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_score
FROM (
    SELECT S.hacker_id, S.challenge_id, max(S.score) as max_score
    FROM Submissions S GROUP BY S.hacker_id, S.challenge_id
    ) M
JOIN Hackers H ON H.hacker_id = M.hacker_id
GROUP BY H.hacker_id, H.name
HAVING total_score > 0
ORDER 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 760
84200 Susan 710
76615 Ryan 700
82382 Sara 640
79034 Marilyn 580
78552 Harry 570
74064 Helen 540
78688 Sean 540
83832 Jason 540
72796 Jose 510
76216 Carlos 510
90304 Lillian 500

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

Click here to see solutions for all Machine Learning Coursera Assignments.

&

Click here to see more codes for Raspberry Pi 3 and similar Family.

&

Click here to see more codes for NodeMCU ESP8266 and similar Family.

&

Click here to see more codes for Arduino Mega (ATMega 2560) and similar Family.

Feel free to ask doubts in the comment section. I will try my best to answer it.

If you find this helpful by any mean like, comment and share the post.

This is the simplest way to encourage me to keep doing such work.

Thanks & Regards,

-Akshay P Daga