Leetcode Hard Problem | Complex SQL 7 | Trips and Users

In this video we will discuss a trips and users SQL problem taken from leetcode hard problem section. Below is the ready script for creating table and inserting data.

Create table Trips (id int, client_id int, driver_id int, city_id int, status varchar(50), request_at varchar(50));
Create table Users (users_id int, banned varchar(50), role varchar(50));
Truncate table Trips;
insert into Trips (id, client_id, driver_id, city_id, status, request_at) values ('1', '1', '10', '1', 'completed', '2013-10-01');
insert into Trips (id, client_id, driver_id, city_id, status, request_at) values ('2', '2', '11', '1', 'cancelled_by_driver', '2013-10-01');
insert into Trips (id, client_id, driver_id, city_id, status, request_at) values ('3', '3', '12', '6', 'completed', '2013-10-01');
insert into Trips (id, client_id, driver_id, city_id, status, request_at) values ('4', '4', '13', '6', 'cancelled_by_client', '2013-10-01');
insert into Trips (id, client_id, driver_id, city_id, status, request_at) values ('5', '1', '10', '1', 'completed', '2013-10-02');
insert into Trips (id, client_id, driver_id, city_id, status, request_at) values ('6', '2', '11', '6', 'completed', '2013-10-02');
insert into Trips (id, client_id, driver_id, city_id, status, request_at) values ('7', '3', '12', '6', 'completed', '2013-10-02');
insert into Trips (id, client_id, driver_id, city_id, status, request_at) values ('8', '2', '12', '12', 'completed', '2013-10-03');
insert into Trips (id, client_id, driver_id, city_id, status, request_at) values ('9', '3', '10', '12', 'completed', '2013-10-03');
insert into Trips (id, client_id, driver_id, city_id, status, request_at) values ('10', '4', '13', '12', 'cancelled_by_driver', '2013-10-03');
Truncate table Users;
insert into Users (users_id, banned, role) values ('1', 'No', 'client');
insert into Users (users_id, banned, role) values ('2', 'Yes', 'client');
insert into Users (users_id, banned, role) values ('3', 'No', 'client');
insert into Users (users_id, banned, role) values ('4', 'No', 'client');
insert into Users (users_id, banned, role) values ('10', 'No', 'driver');
insert into Users (users_id, banned, role) values ('11', 'No', 'driver');
insert into Users (users_id, banned, role) values ('12', 'No', 'driver');
insert into Users (users_id, banned, role) values ('13', 'No', 'driver');

[ashutoshkumar]

Thanks Ankit for this stuff ...worth watching

[LearningwithRohan]

Instead of multiplying with 1.0, best practice is to use casting.. like cast(col1 as numeric)

[nilanjanpaul]

Thank you so much Ankit for such a simple explanation of complex queries.

My solution -

with cte as(select t.request_at, t.status, count(t.status) as cnt
from trips t
inner join users c
on t.client_id = c.users_id
inner join users d
on d.users_id = t.driver_id
where c.banned = 'No' and d.banned = 'No'
group by t.request_at, t.status)


, cte2 as(select request_at, sum(cnt) as total,
sum(case when status = 'completed' then cnt end) as complete_cnt,
isnull( sum(case when status in ( 'cancelled_by_client', 'cancelled_by_driver') then cnt end), 0) as cancelled_cnt

from cte
group by request_at)

select request_at, round((1.0* cancelled_cnt/total), 2)* 100
from cte2

[abhayakumarbehera]

Just browsing and saw this video. I just saw you are jining same table 2 times. Instead of joining 2 times we can use CTE. here is the Answer...
With Act_user as
(
select * from users where banned='No'
) select request_at, count(1) total_trip,
sum(case when status in ('cancelled_by_driver', 'cancelled_by_client') then 1 else 0 end) total_canceled,
round(1.0*sum(case when status in ('cancelled_by_driver', 'cancelled_by_client') then 1 else 0 end)/count(1)*100, 2) Cancel_Trip_percentage
from trips t
inner join Act_user au
on (t.client_id= au.users_id )
inner join Act_user au2
on (t.client_id= au2.users_id)
group by request_at;

But I appreciate your effort and dedication on these video. Kudos..

[prajwalns]

Hi Ankit,

Below is my solution, could have written in more simple format. still learning. Thank you so much for the video and sharing your thought process, very helpful

with users1 as (select *
from trips t
join users u
on t.client_id = u.users_id and banned = 'No'
order by 1),

drivers as (
select *
from trips t
join users u
on t.driver_id = u.users_id and banned = 'No'
order by 1)

select d.request_at
, round(cast(sum(case when d.status = 'cancelled_by_driver' or u.status = 'cancelled_by_client' then 1 else 0 end) as numeric)/cast(sum(case when d.status = 'completed' or d.status = 'cancelled_by_driver' or u.status = 'cancelled_by_client' then 1 else 0 end) as numeric), 2) as Cancellation_Rate
from drivers d
join users1 u
on d.id = u.id
group by 1

[tejatalkz]

Thanks Ankit for your wonderful explanation. Based on the question I think we need to include one more filter condition for checking only between 2013-10-01 and 2013-10-03

[rockyyyieee]

Thanks for scenario based questions. It helps me gaining more knowledge on sql
My solution:

with cte as
(
select users_id from users where banned='No'
),
cte1 as
(
select count(users_id)as countofusers, request_at, sum(case when status in ('cancelled_by_driver', 'cancelled_by_client') then 1 else 0 end) as cancelled from cte c join trips t on c.users_id=t.client_id group by request_at
)
select request_at, as cancellationpercentage from cte1

[prashantchhaparwal]

This solution makes more sense to me as we should use role = 'client' and driver while joining on client_id and driver_id respectively otherwise it would pose problem in case if client_id and driver_id is same.

select request_at, sum(case when status like '%cancelled%' then 1 else 0 end)*1.0/count(*)

from trips t
inner join users u1 on t.client_id = u1.users_id and u1.banned = 'No' and u1.role = 'client'
inner join users u2 on t.driver_id = u2.users_id and u2.banned = 'No' and u2.role = 'driver'
group by 1

[vasistasairam]

Thanks, Ankit, your videos are helping me fight the fear of SQL :)

Here's my solution:

SELECT request_at,
COUNT(id) AS total_trips,
ROUND(1.0* COUNT(CASE WHEN status in ('cancelled_by_client', 'cancelled_by_driver') THEN 1 ELSE NULL END) / COUNT(*), 2)
FROM (
SELECT *
FROM Trips
WHERE client_id IN (SELECT users_id FROM Trip_Users WHERE banned = 'No' AND role='client')
AND driver_id IN (SELECT users_id FROM Trip_Users WHERE banned = 'No' AND role='driver')
) cte
GROUP BY request_at

[DilipKumar-ofjk]

Thanks Ankit for the video :)

Oracle SQL solution:

select REQUEST_AT, round(canceled_requests/total_requests, 2) as cancelation_rate from (
select distinct REQUEST_AT,
count(*) over (partition by REQUEST_AT) as total_requests,
sum(decode(STATUS, 'completed', 0, 1)) over (partition by REQUEST_AT) as canceled_requests
from
Trips,
(select * from Users where ROLE='client' and banned ='No') client_users,
(select * from Users where ROLE='driver'and banned ='No') driver_users
where
and

[saurabhmodi]

My approach: Thank you very much for the question :))

with ban_status_table as(
select trips.*, users1.banned as client_ban_status, users2.banned as driver_ban_status
from Trips trips
left join
Users users1
on trips.client_id = users1.users_id
left join
Users users2
on trips.driver_id = users2.users_id
)
, cancellation_table as (
select request_at,
sum(case when or status='cancelled_by_driver') and client_ban_status='No' and driver_ban_status='No') then 1 else 0 end) as number_canceled_by_unbanned,
sum(case when (client_ban_status='No' and driver_ban_status='No') then 1 else 0 end) as total_requests_by_unbanned
from ban_status_table
group by request_at
)
select ct.*, round((number_canceled_by_unbanned*1.00/total_requests_by_unbanned)*100, 2) as cancellation_rate
from
cancellation_table ct;

[maverick]

Solution:

select a.request_at as Day,
round(count(case when a.status like "%cancelled%" then 1 else null end) / count(1), 2) as "Cancellation Rate"
from Trips a join Users b on a.client_id = b.users_id
join Users c on a.driver_id = c.users_id
where b.banned = 'No' and c.banned = 'No' and day(a.request_at)<4 group by a.request_at

[rohitsingh-rlbo]

with cte as(
select * from users_1
where banned= 'Yes')



Select count(*) as total_trips,
sum(case when status= "completed" then 1 else 0 end) as complete,
sum(Case when status = "cancelled_by_%" then 1 else 0 end) as cancelled
from trips
where client_id not in (select users_id from cte)
and driver_id not in (select users_id from cte)

[aaravkumarsingh]

##According to Leetcode Question
with cte as(
select request_at,
count(case when status in('cancelled_by_driver', 'cancelled_by_client') then 1 else null end) as cancelled_trip_cnt, count(*) as total_trips
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)

select request_at as Day, round(1.0*(cancelled_trip_cnt/total_trips), 2) as "cancellation Rate"
from cte
where request_at between "2013-10-01" and "2013-10-03";

[apurvasaraf]

my solution
select request_at, sum(if(status!='completed', 1,0))*100/count(*) as cancellation_rate
from trips
where client_id not in(select users_id from Users where banned='Yes')
or driver_id not in(select users_id from Users where banned='Yes')
and request_at between '2013-10-01' and '2013-10-03'
group by request_at

[Sagarkumar-yeet]

with cte as (
select *,
case when status='completed' then 0 else 1 end as cancellation_flag
from Trips
),

cte2 as (
select * from cte where
client_id not in (select users_id from Users where banned='Yes') and
driver_id not in (select users_id from Users where banned='Yes')
)

select request_at, FORMAT(((sum(cancellation_flag)*1.0)/count(cancellation_flag)*100), 'N2') as percentage_cancellation_rate
from cte2 group by request_at

[vikhyatjalota]

Alternate Mysql Solution :
select request_at as date, round(count(case when status like 'cancelled%' then 1 else null end) *100 / (count(*)), 2) as rate
from trips
where client_id not in (select users_id from users where banned = 'Yes')
and driver_id not in (select users_id from users where banned = 'Yes')
group by 1

[gauravmalik]

select request_at, sum(case when status like "%cancelled%" then 1 else 0 end), count(*)
from trips left join users on trips.client_id = users.users_id
where banned = "No" and request_at between str_to_date("2013-10-01", "%Y-%m-%d") and str_to_date("2013-10-03", "%Y-%m-%d")
group by 1

[sriparnamodak]

select request_at, round(
sum(case when status = 'cancelled_by_driver' or status = 'cancelled_by_client' then 1 else 0 end )/count(1), 2) *100
from trips where client_id in (select users_id from users where BANNED = 'No')
and driver_id in (select users_id from users where BANNED = 'No')
group by request_at

[ronakjhanwar]

Thnaks ankit for the amazing videos, here is my solution.


with cte as (
SELECT *,
case when status='completed' then 1 else 0 end as flag
FROM trips
WHERE client_id NOT IN (SELECT users_id FROM users WHERE banned = 'yes')
AND driver_id NOT IN (SELECT users_id FROM users WHERE banned = 'yes')
)
select cte.request_at, round((count(*)-sum(flag))/count(*), 2) as rate from cte group by request_at