LeetCode 262: Trips and Users [SQL]

Solution and walkthrough of leetcode database problem 262: Trips and Users. I'm using MySQL but this solution should work in any SQL dialect such as PostgreSQL SQL Server, etc.

[FLYAEROBOY]

Hands down you're amazing when it comes to explaining and solving these queries - You explain just about enough - no blabber - straight and concise. Please keep continuing the good work. Subscribed, thank you!

[zhihaoxu]

Thanks! Your approach is more intuitive than using inner join twice

[anirbansarkar]

Awesome explanation. You divided this complex problem into such simple solutions and added them one after another, which just cleared the concepts in best way possible. Thanks a lot @Frederik Müller

[legion_]

Thank you.. Great Explanation and easy to understand!!!

[dmitrychudinovskikh]

Very elegant! I only managed to solve it with 4 CTEs. But I love CTEs

[haowang]

Wow! Very easy to understand! Huge thanks!!!!

[saurabhanand]

Hi there can someone help- I'm getting wrong values for cancellation rate :
with tt as
(select status, request_at
from Trips a
Join
Users b
ON
a.client_id = b.users_id OR a.driver_id = b.users_id
WHERE banned = 'NO'
AND request_at BETWEEN "2013-10-01" AND "2013-10-03")

select request_at as "Day",
ROUND(SUM(IF(status != "completed", 1, 0))/count(status), 2) as "Cancellation Rate"
from tt
group by Day

[vinayp]

Hello Frederik

I have tried the solution and it's accepting the answer btu throwing an error in submission as my calculation is not rounding to 1.00. can you please tell me where I am missing the logic

(select request_at,
count(case when status in ('cancelled_by_driver', 'cancelled_by_client') then 1 else Null end) as no_of_cancelled, count(1) as no_of_trips,
round(1.0*count(case when status in ('cancelled_by_driver', 'cancelled_by_client') then 1 else Null end)/count(1), 2) as percent
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 c.banned='No' and d.banned='No'
group by request_at)

[evanpetrarca]

My solution with CTEs is much more involved and only passes 9 of 12 test cases.
I did try your solution but can't seem to get my values to become a float like yours.
I tried casting and multiplying by 1.0 but no luck. 😮‍💨

[ankitatapadia]

For the banned users check, if I wrote the code in the below mentioned way and it is accepted on LeetCode. But my question is will it make any difference on any other test case?
where client_id IN (select users_id from users where banned ="No")
and driver_id IN (select users_id from users where banned ="No")

[sabisingh]

Why is it a sum and not count in your query? Aren’t we counting number of cancelled rides ??

[adilkevin]

Could you please continue to make more videos on sql leetcode

[akhandbharat]

you have a great approach to complex problems. That's what makes it easier to understand. Do you think we could have used CASE statements too?

[legion_]

Could you pls make a video on leetcode 1270. All People Report to the Given Manager. Thank you

[karthikbs]

I solved this question on my own but took two hours and the code was very lengthy. May be I need some optimising and better thinking skills.

[nikhilasati]

Not sure what's wrong in the following:

With CTE1 AS (
Select Users.users_id, Trips.request_at,
case when Trips.status like ('%cancelled%') then 1 else 0 end as status_flag
FROM Users
JOIN Trips
ON Users.Users_id = Trips.client_id
where Users.banned in ('No') and Users.role = ('client')
)

, CTE2 AS (
Select Users.users_id, Trips.request_at,
case when Trips.status like ('%cancelled%') then 1 else 0 end as status_flag
FROM Users
JOIN Trips
ON Users.Users_id = Trips.driver_id
where Users.banned in ('No') and Users.role = ('client')
)

, CTE3 AS (
Select * from CTE1
Union all
Select * from CTE2
)

, CTE4 AS (
select request_at, count(*) as cnt, sum(status_flag) as sm
from CTE3
group by request_at
)

select request_at as Day, round((sm/cnt), 2) as "Cancellation Rate"
from CTE4
where request_at between '2013-10-01' and '2013-10-03'