PROSQL 45: Complex SQL Questions Solution

DAY 2: PROSQL45 CHALLENGE (17 NOVEMBER, 2024)

A big thanks to Ankit Bansal the year-end challenge! I’m taking it on by solving 2 complex SQL questions every day from a playlist of 79 problems.

PROBLEM 3: Scenario based question:

Challenge Inspiration: Watch the Solution Video

?You have a table names entries that records information about user visits. The table contains the following columns:

• name: The name of the user.
• address: The address of the user.
• email: The email of the user.
• floor: The floor the user visited.
• resources: The resources used by the user during the visit.

Write a SQL query to find the following information for each user: the total number of visits, the floor the user visited the most, and a comma-separated list of distinct resources used by the user across all visits.

Step by Step Solution Approach:

• I first calculated the total number of visits per user using a COUNT(1) in the total_visited CTE.
• I identified the most visited floor(s) for each user with the RANK() function in the most_visited CTE, ensuring ties are handled appropriately. I apply the RANK() function to rank the floors by the highest visit count for each user. The PARTITION BY name ensures that the ranking happens separately for each user.
• Since there are duplicates in the resources, I needed to extract distinct resources used by each user through the resources CTE. These distinct resources are then aggregated into a comma-separated list in the used_resources CTE using STRING_AGG().
• Finally, the results are combined with ?INNER JOINs to merge the total visit counts, the most visited floor, and the list of resources, while filtering to include only the most visited floor(s).

SQL Code:

WITH total_visited AS (
    SELECT name, COUNT(1) AS total_visited
    FROM entries
    GROUP BY name
),
most_visited AS (
    SELECT name,
           floor,
           COUNT(1) AS floor_visits,
           RANK() OVER (PARTITION BY name ORDER BY COUNT(1) DESC) AS rn
    FROM entries
    GROUP BY name, floor
),
resources AS (
    SELECT DISTINCT name, resources
    FROM entries
),
used_resources AS (
    SELECT name, STRING_AGG(resources, ', ') AS resources
    FROM resources
    GROUP BY name
)
SELECT mv.name,
       tv.total_visited,
       mv.floor AS most_visited_floor,
       ur.resources AS resources
FROM total_visited tv
INNER JOIN most_visited mv ON tv.name = mv.name
INNER JOIN used_resources ur ON tv.name = ur.name
WHERE mv.rn = 1;        

OUTPUT:

PROBLEM 4: SQL Question Asked in a FAANG Interview:

Challenge Inspiration: Watch the Challenge Video

Problem: Write a query to find the date for the Nth occurrence of Sunday in the future from a given date.

?

Step by Step Solution Approach:

• Calculate the Day of the Week: I first used DATEPART to calculate the day of the week for the given date. In SQL Server, DATEPART(WEEKDAY, @input_date) returns a value from 1 to 7, where 1 = Sunday, 2 = Monday, and so on.
• Days to the Nth Sunday: I calculated the number of days to the Nth Sunday using the formula:

1 + 7 * @N - DATEPART(WEEKDAY, @input_date)

This expression computes how many days to add to the input date to reach the Nth Sunday.

• Add the Days: Finally, I added the calculated number of days to the input date using DATEADD to get the final date.

SQL Code:

DECLARE @input_date DATE
DECLARE @N INT
SET @N = 2
SET @input_date = '2024-11-15'
SELECT DATEADD(DAY, 1 + 7*@N - DATEPART(WEEKDAY,  @input_date), @input_date) AS nth_sunday;        

Output:

Let’s level up our SQL skills!

Join me on this journey as I tackle more challenges each day. Let’s improve our SQL skills together before the year ends!

Check out the full solutions on GitHub here!

#SQL #Database #SQLServer #MySQL #dataanalytics #SQLQuery #DataAnalysis #SQL #DataScience #DataAnalytics #RelationalDatabases #BusinessIntelligence #InterviewPreparation #sqlpractice #careerdevelopment #interviewquestions