HackerRank: [SQL Basic Join] (7/8) CHALLENGES | inner join, having& Sub-Query in SQL

byAkshay Daga (APDaga) -
0

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.

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:

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

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



Sample Input-0:

Hackers Table:

Hackers data

Challenges Table:

Challenges data



Sample Output-0:

21283 Angela 6
88255 Patrick 5
96196 Lisa 1



Sample Input-1:

Hackers Table:
Hackers

Challenges Table:
Challenges

Sample Output-1:
12299 Rose 6
34856 Angela 6
79345 Frank 4
80491 Patrick 3
81041 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_challenges
FROM Hackers H
JOIN Challenges C ON H.hacker_id = C.hacker_id
GROUP BY H.hacker_id, H.name
HAVING 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_challenges
FROM Hackers H
JOIN Challenges C ON H.hacker_id = C.hacker_id
GROUP BY H.hacker_id, H.name
HAVING 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;

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:

5120 Julia 50
18425 Anna 50
20023 Brian 50
33625 Jason 50
41805 Benjamin 50
52462 Nicholas 50
64036 Craig 50
69471 Michelle 50
77173 Mildred 50
94278 Dennis 50
96009 Russell 50
96716 Emily 50
72866 Eugene 42



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

Tags:
إرسال تعليق (0)
أحدث أقدم