PROSQL 45: Complex SQL Questions Solution

DAY 4: PROSQL45 CHALLENGE (19 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 7:

This problem is sourced from Leetcode.com : https://leetcode.com/problems/trips-and-users/

Problem: Calculate Daily Cancellation Rate for Unbanned Users

You are given two tables: Trips and Users. A trip can be canceled by either the client or the driver. The goal is to compute the cancellation rate for each day between 2013-10-01 and 2013-10-03, considering only trips involving unbanned clients and drivers.

The cancellation rate is defined as the ratio of canceled trips (either by the client or driver) to the total number of trips, rounded to two decimal places.

Requirements:

• A trip is canceled if the status is either 'cancelled_by_driver' or 'cancelled_by_client'.
• Only count trips where both the client and the driver are not banned.

Output:

Return the cancellation rate for each day within the specified date range.

Video Solution by Ankit: https://www.youtube.com/watch?v=EjzhMv0E_FE&list=PLBTZqjSKn0IeKBQDjLmzisazhqQy4iGkb&index=8

Step-by-Step Solution Approach:

• We need to ?calculate the daily cancellation rate for taxi trips between 2013-10-01 and 2013-10-03, considering only trips where both the client and the driver are unbanned.
• First I join the Trips table with the Users table twice, once for the client_id and once for the driver_id, ensuring both users are not banned.
• The cancellation rate is computed by counting trips where the status is not 'completed' and dividing it by the total number of trips for each day.
• The results are grouped by the request date and sorted in chronological order, with the cancellation rate rounded to two decimal places.

SELECT request_at AS Day,
ROUND(SUM(CASE WHEN status <> 'completed' THEN 1 ELSE 0 END)* 1.0/ COUNT(*),2) AS 'Cancellation Rate'
FROM Trips t
INNER JOIN Users c
ON t.client_id = c.users_id
INNER JOIN Users d
ON t.driver_id = d.users_id
WHERE t.request_at BETWEEN '2013-10-01' AND '2013-10-03'
AND c.banned <> 'Yes' AND d.banned <> 'Yes'
GROUP BY request_at
ORDER BY request_at;        

PROBLEM 8:

This problem is sourced from Leetcode.com: https://leetcode.ca/all/1194.html

Problem: Find the Winner in Each Group

You are given two tables: Players and Matches. In each match, two players from the same group compete. The winner in each group is the player with the highest total score across all matches in that group. In case of a tie, the player with the lower player_id wins.

Write an SQL query to find the winner of each group.

?Video Solution: https://www.youtube.com/watch?v=SfzbR69LquU&list=PLBTZqjSKn0IeKBQDjLmzisazhqQy4iGkb&index=6

Step-by-Step Solution Approach:?

• I first calculate the total score for each player by combining the scores from both first_player and second_player in the Matches table using a UNION ALL. This creates a temporary table scores containing each player's score across all matches.
• Then, a final_score CTE is created where it calculates the total score for each player within their respective group_id. It uses the SUM() function to calculate the total score and the RANK() window function to rank players in each group based on their total score, breaking ties by player_id.
• Finally, the final query selects the group_id and the player_id of the winner, which is the player with rank 1 in each group, and returns the result as the winner for each group.

WITH scores AS (
SELECT first_player AS player_id
	   , first_score AS score
FROM matches
UNION ALL
SELECT second_player AS player_id
	   , second_score AS score
FROM matches), final_score AS (
SELECT s.player_id, p.group_id, SUM(score) AS total_score,
rank() over(partition by p.group_id order by SUM(score) desc,s.player_id) as rn
FROM scores s
INNER JOIN players p
ON p.player_id = s.player_id
GROUP BY s.player_id, p.group_id)
SELECT group_id , player_id as winner 
FROM final_score
where rn = 1;        

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