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.
Link to the problem: leetcode.com/p...

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

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

[@vn5051]

by far, the best one!! thanks a lot

[@legion_29]

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

[@haowang6662]

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

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

[@deepshah2137]

Excellent explanation!!!!!!!

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

[@kunalbudhiraja7761]

You made this problem so easy

[@PATRICKCHUAD]

Very good explanation. Than you,

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

[@juancruz8392]

Great Explanation thank you!!

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

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

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

[@mohitmanghnani2346]

Awesome Bro

[@frederikmuller]

Awesome comment bro

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

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

[@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! kzbin.info/www/bejne/f3eqhKSjdpeSnpo

[@legion_29]

@@frederikmuller thank you very much