Skip to main content

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_idhacker_idname
    1101Contest A
    2102Contest B

  • Colleges:

    college_idcontest_id
    11
    22

  • Challenges:

    challenge_idcollege_id
    11
    22

  • View_Stats:

    challenge_idtotal_viewstotal_unique_views
    110050
    2200100

  • Submission_Stats:

    challenge_idtotal_submissionstotal_accepted_submissions
    1105
    22010

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_idhacker_idnametotal_submissionstotal_accepted_submissionstotal_viewstotal_unique_views
1101Contest A10510050
2102Contest B2010200100

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.

Labels:

Comments

Post a Comment

Popular posts from this blog

Entity Relationship (ER) Diagram Model with DBMS Example

Reference :   Entity Relationship (ER) Diagram Model with DBMS Example What is ER Diagram? ER Diagram  stands for Entity Relationship Diagram, also known as ERD is a diagram that displays the relationship of entity sets stored in a database. In other words, ER diagrams help to explain the logical structure of databases. ER diagrams are created based on three basic concepts: entities, attributes and relationships. ER Diagrams contain different symbols that use rectangles to represent entities, ovals to define attributes and diamond shapes to represent relationships. At first look, an ER diagram looks very similar to the flowchart. However, ER Diagram includes many specialized symbols, and its meanings make this model unique. The purpose of ER Diagram is to represent the entity framework infrastructure. Entity Relationship Diagram Example Table of Content: What is ER Diagram? What is ER Model? History of ER models Why use ER Diagrams? Facts about ER Diagram Model ER Diagram...
Post a Comment
Read more

SQL Joins and advanced joins and Subqueries

  Refernce :  Expert Guide to Advanced SQL Joins: What You Need to Know It's helpful to visualize how these different SQL joins work. Here's a breakdown in a table-like format, along with explanations: SQL Join Types Overview Join Type Description Key Characteristics Use Cases INNER JOIN Returns rows where there is a match in both tables. - Shows only matching records. - Excludes unmatched rows from both tables. - Retrieving related data that exists in both tables. - Finding records with corresponding entries in another table. LEFT OUTER JOIN (LEFT JOIN) Returns all rows from the left table, and the matched rows from the right table. - Includes all records from the left table. - Fills in NULL values for columns from the right table where there's no match. - Retrieving all records from one table and their related data from another, even if some records don't have matches. - Finding records in one table that don't have corresponding entries in another. RIGHT OUTER JO...
Post a Comment
Read more

GIT BASH

  Bash Shell: Git Bash uses the Bash (Bourne Again SHell) command-line interpreter. This means you can use many of the same commands you'd find in a Linux or macOS terminal. Git Integration: Git Bash is tightly integrated with Git, making it easy to execute Git commands Essential Commands: Navigation: pwd : Prints the current working directory. ls : Lists files and directories in the current directory. cd <directory> : Changes the current directory. cd .. : Moves to the parent directory. File Management: mkdir <directory> : Creates a new directory. touch <file> : Creates a new file. rm <file> : Removes a file. rmdir <directory> : Removes an empty directory. Git Commands: git init : Initializes a new Git repository. git clone <repository URL> : Clones an existing Git repository. git status : Displays the status of your working directory. git add <file> : Adds a file to the staging area. git commit -m "commit message" : Commits chan...
Post a Comment
Read more