Solving a Complex SQL Challenge on HackerRank: “15 Days of Learning SQL”

Jun 10, 2024

4 min read

Tackling SQL challenges on platforms like HackerRank can be quite demanding, especially when the problem requires advanced techniques like Common Table Expressions (CTEs). In this article, I will walk you through a challenging SQL problem step-by-step and explain the solution in a beginner-friendly and detailed manner. (To see the full code, visit my Github)

The Challenge

Julia conducted a 15 days of learning SQL contest. The start date of the contest was March 01, 2016 and the end date was March 15, 2016. Write a query to print total number of unique hackers who made at least 1 submission each day (starting on the first day of the contest), and find the hacker_id and name of the hacker who made maximum number of submissions each day. If more than one such hacker has a maximum number of submissions, print the lowest hacker_id. The query should print this information for each day of the contest, sorted by the date.

Hackers Table:

Submissions Table:

Sample Output:

2016-03-01 4 20703 Angela
2016-03-02 2 79722 Michael
2016-03-03 2 20703 Angela
2016-03-04 2 20703 Angela
2016-03-05 1 36396 Frank
2016-03-06 1 20703 Angela

The Solution

Part 1: Finding the Total Number of Unique Hackers Who Made At Least One Submission Each Day

To solve this part, we need to create a list of all hackers who made at least one submission every day from the start of the contest and then count the number of unique hackers for each date in this list.

Here’s how we can approach it:

Step 1: Identify the First Submission Date

(
        SELECT TOP 1 submission_date
        FROM Submissions
        GROUP BY submission_date
        ORDER BY submission_date
    ) TOP_ONE

Step 2: Fetch All the Entries That Match the First Submission Date

Here we create a base for our CTE (common table expression). We will keep adding rows to this CTE in step 3.

WITH CTE AS (
    SELECT Submissions.submission_date, hacker_id
    FROM Submissions, (
        SELECT TOP 1 submission_date
        FROM Submissions
        GROUP BY submission_date
        ORDER BY submission_date
    ) TOP_ONE
    WHERE Submissions.submission_date = TOP_ONE.submission_date

Step 3: Create a Sequence of Dates and Hackers Present Since Day 1

To extend our CTE base and include hackers who have made submissions every day since day 1, we need to check if the hacker_id from the next day exists in the current day. Essentially, we join the CTE base table with the Submissions table on this condition. The DATEADD(DAY, +1, CTE.submission_date) function will increment the date by one day repeatedly until no more matching records are found.

   UNION ALL
   
    SELECT Submissions.submission_date, Submissions.hacker_id
    FROM CTE
    JOIN Submissions ON DATEADD(DAY, +1, CTE.submission_date) = Submissions.submission_date
        AND CTE.hacker_id = Submissions.hacker_id
)

Step 4: Count Unique Hackers for Each Day

Now that we have our list, we group the results by submission_date and count the distinct hacker_ids for each date:

UNIQUE_HACKERS_PER_DAY AS (
    SELECT submission_date, COUNT(DISTINCT hacker_id) AS unique_hackers_count
    FROM CTE
    GROUP BY submission_date
)

Part 2: Finding the Hacker with the Maximum Number of Submissions Each Day

In this part, we aim to identify the hacker with the highest number of submissions for each day. We will rank the hackers based on their number of submissions for each day and then fetch the top-ranked hacker for each day.

Step 1: Rank Hackers by Number of Submissions

First, we group the Submissions table by submission_date and hacker_id. Counting the rows in each group will give us the number of submissions each hacker made on each day. We then order these groups in descending order based on the count of submissions (and based on hacker_ids in case count number is the same for more than one hacker), and assign a row number to maintain this order of ranks.

MAX_SUBMISSIONS AS (
    SELECT ROW_NUMBER() OVER (ORDER BY submission_date, COUNT(*) DESC, hacker_id) AS RN,
           submission_date, hacker_id
    FROM Submissions
    GROUP BY submission_date, hacker_id
),

Step 2: Identify the Row Numbers of Top-Ranked Hackers for Each Date

To find the top-ranked hacker for each date, we group the table from the last step by submission_date and select the minimum row number (RN) within each group. This will give us the row number corresponding to the hacker with the highest number of submissions for each date.

(
        SELECT MIN(RN) AS min_rn
        FROM MAX_SUBMISSIONS
        GROUP BY submission_date
    ) MAX_ENTRY

Step 3: Identify the Hacker with the Highest Number of Submissions

To find the top hacker_id, we need to join the results from the previous step with the MAX_SUBMISSIONS table on the matching row number. This will give us the hacker who has the highest number of submissions for each date.

MAX_SUBS_PER_DAY AS (
    SELECT submission_date, hacker_id
    FROM (
        SELECT MIN(RN) AS min_rn
        FROM MAX_SUBMISSIONS
        GROUP BY submission_date
    ) MAX_ENTRY
    JOIN MAX_SUBMISSIONS ON MAX_ENTRY.min_rn = MAX_SUBMISSIONS.RN
)

Part 3: Joining the Results

The problem requires the name of the hacker as well. To achieve this, we join our three tables to create the final result:

SELECT UNIQUE_HACKERS_PER_DAY.submission_date, unique_hackers_count, Hackers.hacker_id, Hackers.name
FROM UNIQUE_HACKERS_PER_DAY
JOIN MAX_SUBS_PER_DAY ON UNIQUE_HACKERS_PER_DAY.submission_date = MAX_SUBS_PER_DAY.submission_date
JOIN Hackers ON MAX_SUBS_PER_DAY.hacker_id = Hackers.hacker_id;