LeetCode 262: Trips and Users [SQL]

[@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!

[@zhihaoxu8119]

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

[@saurabhanand9110]

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

[@vn5051]

by far, the best one!! thanks a lot

[@anirbansarkar6306]

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

[@vinayp749]

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)

[@srikanthyellapu5709]

try
select t.Request_at Day,
ROUND(
(cast(sum(IIF(t.status!='completed',1,0)) as float) / count(*))
,2) as 'Cancellation Rate'
from Trips t
where t.Client_Id in (Select Users_Id from Users where Banned='No')
and t.Driver_Id in (Select Users_Id from Users where Banned='No')
and t.Request_at between '2013-10-01' and '2013-10-03'
group by t.Request_at

[@deepshah2137]

Excellent explanation!!!!!!!

[@haowang6662]

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

[@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. 😮‍💨

[@ankitatapadia4806]

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")

[@legion_29]

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

[@sabisingh9635]

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

[@frederikmuller]

a SUM still works here as the IF statement within the SUM function gives 1 for cancelled rides, that way we’re summing up or counting the cancelled rides. you could also use COUNT if you restructure the IF statement to assign NULL to non-cancelled rides.

[@adilkevin6220]

Could you please continue to make more videos on sql leetcode

[@akhandbharat2463]

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?

[@frederikmuller]

you can always use CASE statements instead of IF, just another way of writing it

[@dmitrychudinovskikh7038]

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

[@frederikmuller]

you do you Dmitry!

[@kunalbudhiraja7761]

You made this problem so easy

[@legion_29]

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

[@frederikmuller]

I'll put it on my list of videos to film!

[@frederikmuller]

it's out now! th-cam.com/video/IFGSqmA2_h8/w-d-xo.html

[@legion_29]

@@frederikmuller thank you very much

[@PATRICKCHUAD]

Very good explanation. Than you,

[@juancruz8392]

Great Explanation thank you!!

[@mohitmanghnani2346]

Awesome Bro

[@frederikmuller]

Awesome comment bro

[@karthikbs8749]

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.

[@frederikmuller]

hm maybe try some more medium or even easy questions as this question is like a combination of multiple questions (calculate a ratio, use NOT IN, JOIN tables…). if you get good at all these elements and recognize what the question is asking for you should be able to solve these hard ones rather quickly!

[@nikhilasati2329]

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'