LeetCode 262. Trips and Users SQL Solution

Problem

LeetCode SQL Problem

262. Trips and Users

Trips table

id	client_id	driver_id	city_id	status	request_at
1	1	10	1	completed	2013-10-01T00:00:00Z
2	2	11	1	cancelled_by_driver	2013-10-01T00:00:00Z
3	3	12	6	completed	2013-10-01T00:00:00Z
4	4	13	6	cancelled_by_client	2013-10-01T00:00:00Z
5	1	10	1	completed	2013-10-02T00:00:00Z
6	2	11	6	completed	2013-10-02T00:00:00Z
7	3	12	6	completed	2013-10-02T00:00:00Z
8	2	12	12	completed	2013-10-03T00:00:00Z
9	3	10	12	completed	2013-10-03T00:00:00Z
10	4	13	12	cancelled_by_driver	2013-10-03T00:00:00Z

Users table

users_id	banned	role
1	No	client
2	Yes	client
3	No	client
4	No	client
10	No	driver
11	No	driver
12	No	driver
13	No	driver

Solution - Group By Approach

• Use INNER JOIN to filter out trips involving a banned client or driver
• Use WHERE clause to filter out trips outside the desired date range
• Group request count by date and status
• Use CASE statement with SUM() function to aggregate daily cancelled requests
• Calculate cancellation rate for each day and round it to 2 decimal points

MySQL

WITH trips_analysis
AS (
	SELECT t.request_at,
		t.STATUS,
		count(t.id) AS requests
	FROM Trips t
	INNER JOIN Users AS client ON t.client_id = client.users_id
		AND client.banned = 'No'
	INNER JOIN Users AS driver ON t.driver_id = driver.users_id
		AND driver.banned = 'No'
	WHERE t.request_at BETWEEN '2013-10-01'
			AND '2013-10-03'
	GROUP BY t.request_at,
		t.STATUS
	)
SELECT request_at AS Day,
	round(sum(CASE
				WHEN STATUS LIKE 'cancelled%'
					THEN requests
				ELSE 0
				END) / sum(requests), 2) AS "Cancellation Rate"
FROM trips_analysis
GROUP BY request_at;

TSQL

WITH trips_analysis
AS (
	SELECT t.request_at
		,t.STATUS
		,count(t.id) AS requests
	FROM Trips t
	INNER JOIN Users AS client ON t.client_id = client.users_id
		AND client.banned = 'No'
	INNER JOIN Users AS driver ON t.driver_id = driver.users_id
		AND driver.banned = 'No'
	WHERE t.request_at BETWEEN '2013-10-01'
			AND '2013-10-03'
	GROUP BY t.request_at
		,t.STATUS
	)
SELECT request_at AS Day
	,cast((
			sum(CASE
					WHEN STATUS LIKE 'cancelled%'
						THEN requests
					ELSE 0
					END) / cast(sum(requests) AS FLOAT)
			) AS DECIMAL(10, 2)) AS "Cancellation Rate"
FROM trips_analysis
GROUP BY request_at;

This SQL query is using a Common Table Expression (CTE) to create a temporary table called trips_analysis that selects data from the Trips table and joins it with the Users table twice to get information about clients and drivers. The WHERE clause filters the results to only include data for a specific date range, and the GROUP BY clause aggregates the data by the request date and status of the trip.

The second part of the SQL query uses the trips_analysis CTE to calculate the cancellation rate for each day within the date range. The ROUND function is used to round the result to two decimal places, and the CASE statement within the SUM function is used to count the number of cancelled requests and divide it by the total number of requests for each day. Finally, the results are grouped by the request date to show the cancellation rate for each day.

Query Output

Day	Cancellation Rate
2013-10-01 00:00:00	0.33
2013-10-02 00:00:00	0.00
2013-10-03 00:00:00	0.50