In this video we solve Leetcode 262- Trips and Users using SQL. These types of SQL interview questions/problems are often used by FAANG (Facebook/Meta, Amazon, Apple, Netflix and Google) and similar tech companies. This tutorial/walkthrough will serve as a step-by-step solution to the problem.
To solve this, I demonstrate a step-by-step walkthrough of creating a subset of data using a Common Table Expression (CTE), specifically by joining the same table twice to pull clients and then drivers, and then perform my computations to reach the end result.
Here is the URL to the LeetCode problem itself: https://leetcode.com/problems/trips-and-users/
Please give the video a THUMBS UP if you found it helpful. Your support to the channel is appreciated.
Here is the code I wrote:
with cte_daily_cancellations as (
Select
t.request_at
, sum(case when t.status in ('cancelled_by_driver', 'cancelled_by_client') then 1 else 0 end) ct_cancelled
, count(*) ct_total_requests
from
Trips t
inner join Users u
on t.client_id = u.users_id and u.role = 'client' -- Users/Clients
inner join Users d
on t.driver_id = d.users_id and d.role = 'driver' -- Driver
where u.banned = 'No' and d.banned = 'No'
and (t.request_at between '2013-10-01' and '2013-10-03')
group by t.request_at
)
select
request_at as Day
, round(ct_cancelled / ct_total_requests, 2) as 'Cancellation Rate'
from cte_daily_cancellations
#sql #sql101 #leetcode
