HackerRank: [SQL Advanced Join] (4/5) INTERVIEWS | inner & left join, having, sum in SQL

byAkshay Daga (APDaga) -
0

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.

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:

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:

  • Contests: The contest_id is the id of the contest, hacker_id is the id of the hacker who created the contest, and name is the name of the hacker.

  • Colleges: The college_id is the id of the college, and contest_id is the id of the contest that Samantha used to screen the candidates.

  • Challenges: The challenge_id is the id of the challenge that belongs to one of the contests whose contest_id Samantha forgot, and college_id is the id of the college where the challenge was given to candidates.

  • View_Stats: The challenge_id is the id of the challenge, total_views is the number of times the challenge was viewed by candidates, and total_unique_views is the number of times the challenge was viewed by unique candidates.

  • Submission_Stats: The challenge_id is the id of the challenge, total_submissions is the number of submissions for the challenge, and total_accepted_submission is the number of submissions that achieved full scores.



Sample Input:
  • Contests Table:

  • Colleges Table:

  • Challenges Table:

  • View_Stats Table:

  • Submission_Stats Table:

Sample Output:

66406 17973 Rose 111 39 156 56
66556 79153 Angela 0 0 11 10
94828 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 C
JOIN Colleges Cl ON C.contest_id = Cl.contest_id
JOIN 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) SS
ON Ch.challenge_id=SS.challenge_id
LEFT 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) VV
ON Ch.challenge_id=VV.challenge_id
GROUP BY C.contest_id, C.hacker_id, C.name
HAVING !(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 C
JOIN Colleges Cl ON C.contest_id = Cl.contest_id
JOIN 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) SS
ON Ch.challenge_id=SS.challenge_id
LEFT 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) VV
ON Ch.challenge_id=VV.challenge_id
GROUP BY C.contest_id, C.hacker_id, C.name
HAVING SUM(SS.total_submissions)+
SUM(SS.total_accepted_submissions)+
SUM(VV.total_views)+
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.


Expected Output:

845 579 Rose 1987 580 1635 566
858 1053 Angela 703 160 1002 384
883 1055 Frank 1121 319 1217 338
1793 2655 Patrick 1337 360 1216 412
2374 2765 Lisa 2733 815 3368 904
2963 2845 Kimberly 4306 1221 3603 1184
3584 2873 Bonnie 2492 652 3019 954
4044 3067 Michael 1323 449 1722 528
4249 3116 Todd 1452 376 1767 463
4269 3256 Joe 1018 372 1766 530
4483 3386 Earl 1911 572 1644 477
4541 3608 Robert 1886 516 1694 504



--------------------------------------------------------------------------------
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:
Post a Comment (0)
Previous Post Next Post