# HackerRank: [SQL Basic Join] (7/8) CHALLENGES | 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:

Julia asked her students to create some coding challenges. Write a query to print the hacker_id, name, and the total number of challenges created by each student. Sort your results by the total number of challenges in descending order. If more than one student created the same number of challenges, then sort the result by hacker_id. If more than one student created the same number of challenges and the count is less than the maximum number of challenges created, then exclude those students from the result.

Input Format:

The following tables contain challenge data:

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

• Challenges: The challenge_id is the id of the challenge, and hacker_id is the id of the student who created the challenge.

Sample Input-0:

Hackers Table:

Challenges Table:

Sample Output-0:

`21283 Angela 688255 Patrick 596196 Lisa 1`

Sample Input-1:

Hackers Table:

Challenges Table:

Sample Output-1:
`12299 Rose 634856 Angela 679345 Frank 480491 Patrick 381041 Lisa 1`

Explanation:

For Sample Case 0, we can get the following details:

Students 5077 and 62743 both created 4 challenges, but the maximum number of challenges created is 6 so these students are excluded from the result.

For Sample Case 1, we can get the following details:

Students 12299 and 34856 both created 6 challenges. Because 6 is the maximum number of challenges created, these students are included in the result.

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

`SELECT H.hacker_id, H.name, COUNT(C.challenge_id) as no_of_challengesFROM Hackers HJOIN Challenges C ON H.hacker_id = C.hacker_idGROUP BY H.hacker_id, H.nameHAVING no_of_challenges = (SELECT count(challenge_id) AS max_count FROM Challenges GROUP BY hacker_id ORDER BY max_count DESC LIMIT 1)OR no_of_challenges IN (SELECT t.cnt FROM (SELECT count(challenge_id) AS cnt FROM Challenges GROUP BY hacker_id) t GROUP BY t.cnt HAVING COUNT(t.cnt) = 1)ORDER BY no_of_challenges 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.

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

`SELECT H.hacker_id, H.name, COUNT(C.challenge_id) as no_of_challengesFROM Hackers HJOIN Challenges C ON H.hacker_id = C.hacker_idGROUP BY H.hacker_id, H.nameHAVING no_of_challenges = (SELECT count(challenge_id) AS max_count FROM Challenges GROUP BY hacker_id ORDER BY max_count DESC LIMIT 1)OR no_of_challenges NOT IN (SELECT t.cnt FROM (SELECT count(challenge_id) AS cnt FROM Challenges GROUP BY hacker_id) t GROUP BY t.cnt HAVING COUNT(t.cnt) != 1)ORDER BY no_of_challenges DESC, H.hacker_id ASC;`

### Expected Output:

`5120 Julia 5018425 Anna 5020023 Brian 5033625 Jason 5041805 Benjamin 5052462 Nicholas 5064036 Craig 5069471 Michelle 5077173 Mildred 5094278 Dennis 5096009 Russell 5096716 Emily 5072866 Eugene 42`

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