SQL hackerrank interview question

 

Youtube : Complex SQL Query Breakdown Step By Step Detailed Explanation

answer: SELECT name

FROM Employee

WHERE salary > 2000 AND months < 10

ORDER BY employee_id ;

Answer: SELECT

    con.contest_id,

    con.hacker_id, 

    con.name, 

    SUM(total_submissions) AS total_submissions, 

    SUM(total_accepted_submissions) AS total_accepted_submissions, 

    SUM(total_views) AS total_views, 

    SUM(total_unique_views) AS total_unique_views

FROM

    contests con 

JOIN

    colleges col ON con.contest_id = col.contest_id 

JOIN

    challenges cha ON col.college_id = cha.college_id 

LEFT JOIN

    (SELECT challenge_id, 

            SUM(total_views) AS total_views, 

            SUM(total_unique_views) AS total_unique_views

     FROM view_stats 

     GROUP BY challenge_id) vs 

    ON cha.challenge_id = vs.challenge_id 

LEFT JOIN

    (SELECT challenge_id, 

            SUM(total_submissions) AS total_submissions, 

            SUM(total_accepted_submissions) AS total_accepted_submissions 

     FROM submission_stats 

     GROUP BY challenge_id) ss 

    ON cha.challenge_id = ss.challenge_id

GROUP BY

    con.contest_id, con.hacker_id, con.name

HAVING

    SUM(total_submissions) != 0 OR 

    SUM(total_accepted_submissions) != 0 OR

    SUM(total_views) != 0 OR

    SUM(total_unique_views) != 0

ORDER BY

    contest_id;

Query Explanation:

1. Base Tables:

• Contests: Contains contest details (contest_id, hacker_id, name).

• Colleges: Links contests to colleges (college_id, contest_id).

• Challenges: Represents coding challenges within colleges (challenge_id, college_id).

• View_Stats: Contains view statistics for challenges (challenge_id, total_views, total_unique_views).

• Submission_Stats: Contains submission statistics for challenges (challenge_id, total_submissions, total_accepted_submissions).

---

2. Subqueries:

• vs Subquery:

  sql

  Copy

  (select challenge_id, sum(total_views) as total_views, sum(total_unique_views) as total_unique_views
   from view_stats 
   group by challenge_id)

  • Aggregates total_views and total_unique_views for each challenge.

  • Groups by challenge_id to ensure each challenge has a single row of aggregated stats.

• ss Subquery:

  sql

  Copy

  (select challenge_id, sum(total_submissions) as total_submissions, sum(total_accepted_submissions) as total_accepted_submissions 
   from submission_stats 
   group by challenge_id)

  • Aggregates total_submissions and total_accepted_submissions for each challenge.

  • Groups by challenge_id to ensure each challenge has a single row of aggregated stats.

---

3. Main Query:

• Joins:

  • Contests is joined with Colleges to link contests to colleges.

  • Colleges is joined with Challenges to link colleges to challenges.

  • Challenges is left-joined with the vs subquery to include view stats for each challenge.

  • Challenges is left-joined with the ss subquery to include submission stats for each challenge.

• Aggregation:

  • The SUM function calculates the total values for:

    • total_submissions

    • total_accepted_submissions

    • total_views

    • total_unique_views

  • These sums are calculated for each contest (con.contest_id).

• Grouping:

  • The results are grouped by con.contest_id, con.hacker_id, and con.name to ensure each contest is represented as a single row.

• Filtering:

  • The HAVING clause ensures that only contests with at least one non-zero sum are included:

    sql

    Copy

    having sum(total_submissions)!=0 or 
           sum(total_accepted_submissions)!=0 or
           sum(total_views)!=0 or
           sum(total_unique_views)!=0

• Ordering:

  • The results are ordered by contest_id to ensure the output is sorted.

---

Query Breakdown:

sql

Copy

SELECT
    con.contest_id,
    con.hacker_id, 
    con.name, 
    SUM(total_submissions) AS total_submissions, 
    SUM(total_accepted_submissions) AS total_accepted_submissions, 
    SUM(total_views) AS total_views, 
    SUM(total_unique_views) AS total_unique_views
FROM
    contests con 
JOIN
    colleges col ON con.contest_id = col.contest_id 
JOIN
    challenges cha ON col.college_id = cha.college_id 
LEFT JOIN
    (SELECT challenge_id, 
            SUM(total_views) AS total_views, 
            SUM(total_unique_views) AS total_unique_views
     FROM view_stats 
     GROUP BY challenge_id) vs 
    ON cha.challenge_id = vs.challenge_id 
LEFT JOIN
    (SELECT challenge_id, 
            SUM(total_submissions) AS total_submissions, 
            SUM(total_accepted_submissions) AS total_accepted_submissions 
     FROM submission_stats 
     GROUP BY challenge_id) ss 
    ON cha.challenge_id = ss.challenge_id
GROUP BY
    con.contest_id, con.hacker_id, con.name
HAVING
    SUM(total_submissions) != 0 OR 
    SUM(total_accepted_submissions) != 0 OR
    SUM(total_views) != 0 OR
    SUM(total_unique_views) != 0
ORDER BY
    contest_id;

---

Example Walkthrough:

Input Data:

• Contests:

  contest_id	hacker_id	name
  1	101	Contest A
  2	102	Contest B

• Colleges:

  college_id	contest_id
  1	1
  2	2

• Challenges:

  challenge_id	college_id
  1	1
  2	2

• View_Stats:

  challenge_id	total_views	total_unique_views
  1	100	50
  2	200	100

• Submission_Stats:

  challenge_id	total_submissions	total_accepted_submissions
  1	10	5
  2	20	10

Query Execution:

1. Joins:

   • Contests is joined with Colleges and Challenges to link contests to challenges.

   • Challenges is left-joined with vs and ss to include view and submission stats.

2. Aggregation:

   • For contest_id = 1:

     • total_submissions = 10

     • total_accepted_submissions = 5

     • total_views = 100

     • total_unique_views = 50

   • For contest_id = 2:

     • total_submissions = 20

     • total_accepted_submissions = 10

     • total_views = 200

     • total_unique_views = 100

3. Filtering:

   • Both contests have non-zero sums, so they are included.

4. Ordering:

   • Results are sorted by contest_id.

Output:

contest_id	hacker_id	name	total_submissions	total_accepted_submissions	total_views	total_unique_views
1	101	Contest A	10	5	100	50
2	102	Contest B	20	10	200	100

---

Key Points:

• The query uses subqueries to pre-aggregate stats for each challenge.

• LEFT JOIN ensures that challenges without stats are still included (with NULL values, which are treated as 0 in aggregation).

• The HAVING clause filters out contests with all-zero sums.

• The result is sorted by contest_id.