Leetcode Hard Problem | Complex SQL 7 | Trips and Users

[@ashutoshkumar5784]

Thanks Ankit for this stuff ...worth watching

[@ankitbansal6]

Glad you liked it 😊

[@nilanjanpaul2723]

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

[@LearningwithRohan]

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

[@prajwalns7070]

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

[@rockythehybriddog3112]

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, (cancelled*1.0)/countofusers*100 as cancellationpercentage from cte1

[@prashantchhaparwal8227]

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

[@ayaskanta100]

this better and using sum is right actually i was wondering why count

[@ayaskanta100]

better use count(1)

[@vasistasairam5483]

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

[@ronakjhanwar2115]

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

[@RohitKumar-zm3nw]

sir video solution galat hai pehle tareek pe total 6 trips hua hai 3 nahi or 2 cancelled hai 1 nahi. Please help

[@DilipKumar-of3jk]

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 trips.CLIENT_ID=client_users.users_id
and trips.DRIVER_ID=driver_users.users_id);

[@dharmeshdutt4129]

here is my way of doing :
WITH DATA AS (SELECT T.REQUEST_AT,
(CASE WHEN STATUS = 'completed' THEN 1 ELSE 0 END) COMPLETED
FROM TRIPS T, USERS U
WHERE T.CLIENT_ID = U.USERS_ID AND U.BANNED ='No')
SELECT REQUEST_AT REQUEST_DATE,
ROUND((COUNT(1)-SUM(COMPLETED))/COUNT(1)*100, 2)||'%' CANCLETION_RATE
FROM DATA GROUP BY REQUEST_AT ORDER BY REQUEST_AT;

[@abhayakumarbehera2343]

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..

[@riyaz8072]

Thanks Ankit..
I only learnt basics bz of which i'm not able to clear the interviews..
Please keep posting similar videos..

[@ankitbansal6]

Sure

[@ramakumarguntamadugu1299]

very good explanation with out using cte 👌👌

[@rashmidutta7151]

select count(case when status like 'cancelled%' then id end)/count(distinct id),request_at
from trips
where driver_id in (select users_id from usersFor where banned='No') AND
client_id in (select users_id from usersFor where banned='No') group by request_at

[@2412_Sujoy_Das]

Nice solution Rashmi!!!

[@neelamkushwaha1703]

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

[@ankitbansal6]

My pleasure🙏

[@weshallbe]

As usual, clean solution and great explanation!

[@apurvasaraf5828]

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

[@aaravkumarsingh4018]

##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";

[@codespacelk]

Thanks Ankit, really worth watching 😆

[@ankitbansal6]

My pleasure 😊

[@maverick6932]

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)

[@dwaipayansaha4443]

My Solution:-
select T.request_at,round(sum(T.cancel_ind)/count(T.status_1),2) cancel_rate from
(with t1 as(
select *,case when u.banned='Yes' then 1 else 0 end banned_ind from users u),
t2 as(
select *, case when t.status_1='cancelled_by_driver' then 1 when t.status_1='cancelled_by_client' then 1 else 0 end cancel_ind from trips t)
select * from t2 left join t1 on t2.client_id=t1.users_id) T
where T.banned_ind=0
group by T.request_at;

[@SudhirKumar-rl4wt]

with client as (
select * from users where role='client' and banned='No'
),
driver as (
select * from users where role='driver' and banned='No'
),
temp as (
select request_at,status
from Trips t inner join client c on t.client_id = c.users_id
inner join driver d on t.driver_id = d.users_id
) select request_at "Day" ,
round(cast(sum( case when status in ('cancelled_by_driver','cancelled_by_client')
then 1
else 0
end ) as decimal(5,2))/cast (count(*) as decimal(5,2)),2)
"Cancellation Rate"
from temp where request_at between '2013-10-01' and '2013-10-03'
group by request_at

[@gauravmalik3911]

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

[@yadavikasAnalyst]

Hi sir I solved this using SUBQUERY..........MYSQL
WITH CTE AS
(
select request_at,status,
CASE WHEN status like "cancelled%" THEN 1
ELSE 0
END AS flag
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")
)
SELECT request_at,ROUND(sum(flag)*100/count(flag),2) AS cancellation_rates
FROM cte
GROUP BY request_at;

[@vikhyatjalota2213]

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

[@gagansingh3481]

Great awesome explanation Ankit 👏

[@dgvj]

awesome video. Thank you so much, looking forward for more such amazing content.

[@tanmaykumar3250]

My solution :
with cte as
(SELECT * from trips
where client_id in (select users_id from users where banned = 'No')
or driver_id in (select users_id from users where banned = 'No'))
SELECT 100*count(*)/(select count(*) from cte) from cte where status "completed";

[@shreyashchoudhary6827]

thanks for the scripts ,it got easier to understand and solve

[@ankitbansal6]

Welcome

[@arpiteshsrivastava9838]

Tried this way | MySql
select request_at as 'Day',
round(count(case when status != 'completed' then status end)/count(*),2)
'Cancellation Rate'
from Trips t 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')
and request_at between '2013-10-01' and '2013-10-03'
group by request_at;

[@ujjwalvarshney3188]

select request_at ,(sum(case when status ='completed' then 0 else 1 end ):: NUMERIC(38,4)/count(* )) *100 as cancellation_rate
from Trips
where client_id not in ( select distinct users_id from users where banned = 'Yes' and role = 'client')
or driver_id not in (select distinct users_id from users where banned = 'Yes' and role = 'driver')
group by 1 order by 1

[@PrashantSharma-sw2jr]

Select request_at, round(sum(case when status like 'cancelled%' then 1 else 0 end)*100/count(*),2) as Cancellation_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 request_at
order by 1 asc

[@apurvasaraf5828]

select request_at,sum(case when status!='completed' then 1 else 0 end)as cm,
count(*) , sum(case when status!='completed' then 1 else 0 end)*100/count(*) as p
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 request_at

[@dipanjan93]

Bit confused, why can't we do a union like join trips with users when role is client then union all the same when the user is driver. Finally, calculate the cancellation rate. Something like below -
With temp as
(Select a.*, b.banned from #trips a join #users b on a.client_id = b.users_id
where b.role = 'client' and b.banned = 'No'
union all
Select a.*, b.banned from #trips a join #users b on a.driver_id = b.users_id
where b.role = 'driver' and b.banned= 'No'),
cancel_rate as
(Select request_at,
count(1) as total_trips ,
sum(case when status 'completed' and banned = 'No' then 1 else 0 end) as cancelled_trips
from temp
group by request_at)
Select * from cancel_rate;

[@aayushibirla2590]

such an amazing solution

[@abhinabjha7988]

with filter_banned_users as
(
select * 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')
), get_count as
(
select request_at, count(id) as total, sum(case when status ='cancelled_by_client' or status='cancelled_by_driver' then 1 else 0 end ) as cancelled from filter_banned_users group by request_at
)select request_at, round(cancelled::numeric/total::numeric, 2) from get_count;

[@tunguyenanh3071]

thank you for your solution ! appreciate that !

[@gothams1195]

select distinct request_at,
(sum(case when status!='completed' then 1 else 0 end) over (partition by request_at)/
count(1) over (partition by request_at)) as crate
,
count(1) over (partition by request_at) as total
from (select * 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'))a;

[@subhashreekarmakar2822]

with cte as (
select *,
case when status like '%cancelled%' then 1 else 0 end as cancel_status
from trips a
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, (sum(cancel_status)*1.0/count(id))*100 ,sum(cancel_status) as cancel ,count(id) as total from cte a
group by request_at

[@TusharKumar-u4p]

We can also do it without join . Can anyone tell me which approach is considered better with join or without join ?

[@SumanGhosh-vn3tx]

If possible please start the Basic SQL Tutorials so that beginners can understand

[@ankitbansal6]

Sure.

[@cuongtranmanh2287]

with a as (SELECT DISTINCT * from Trips where client_id not in (select users_id from Users u where banned='Yes')),
b as (SELECT request_at r, count(*) total, sum(case when status like 'cancel%' then 1 else 0 end) as cancel from a group by r)
select *, cancel*1.0/total from b;

[@Abhi-x9f8t]

Ankit I just did almost same . Can you tell me if it has any problem. The answer is correct but i want to know if it it efficient enough , if not please tell my why
with temp as (
select t.*,
case when status = "completed" then 1 else 0 end as completed,
case when status = "cancelled_by_client" or status ="cancelled_by_driver" then 1 else 0 end as cancelled
from trips t
inner join users u1 on t.client_id = u1.users_id
inner join users u2 on t.driver_id = u2.users_id
where u1.banned = "no" and u2.banned ="no")
select request_at, sum(completed) total_completed, sum(cancelled) total_cancelled, round(sum(cancelled)/(sum(completed)+sum(cancelled))*100,2)
rate from temp
group by request_at

[@anirvansen5024]

MYSQL Solution
with base as (
select t.*,u.banned as user_banned,u1.banned as driver_banned from Trips t join Users u
on t.client_id = u.users_id
join Users u1 on t.driver_id = u1.users_id
where t.request_at between '2013-10-01' and '2013-10-03'
order by t.request_at),
base_result_set as (
select * from base where user_banned = 'No' and driver_banned = 'No'
)
select request_at,
round((sum(case when status = 'cancelled_by_client' or status = 'cancelled_by_driver' then 1 else 0 end) / count(1)) *100,2) as cancellation_rate
from base_result_set group by request_at order by request_at;

[@ankitbansal6]

Thank you

[@shivamsharma334]

Hello Sir,
I have one question.
What's the difference between
count(case when column_name = 'something' then 1 else null end)
and
sum(case when column_name = 'something' then 1 else null end)
I believe the answers would be the same but is there any difference in how the SQL process it ?

[@shahrukhhussain9650]

In case of sum query , It'll give null value

[@Aarohi_Zara]

select request_at
, count(case when status in ('cancelled_by_client', 'cancelled_by_driver') then 1 else NULL end) as CancelledTrip
, count(0) as TotalTrip
, cast(1.0 * count(case when status in ('cancelled_by_client', 'cancelled_by_driver') then 1 else NULL end)/count(0) * 100 as decimal(5, 2)) as CancelledPercentage
from Trips t
inner join Users c
on t.client_id = c.users_id and c.banned = 'No'
inner join Users d
on t.client_id = d.users_id and d.banned = 'No'
group by request_at

[@florincopaci6821]

Thank you for an other useful video!
Maybe in the future you will come with other problems from leet, especially those with friends likes, page recommendations, etc. Please.
For me they seem a bit difficult.
Thank you!

[@ankitbansal6]

Sure. If you can share links of problems I will check them out.

[@florincopaci6821]

@@ankitbansal6 The last one was 1892 but i will check for others.Thank you so much!

[@dgvj]

@@ankitbansal6 thank you for the videos. If you can make videos on the problems 1949 - 1951 - 1972 -1125 - 1709 that would be really helpful. I am facing difficulties solving these questions.

[@hairavyadav6579]

Hello Sir,
My approach
with cte as (select t.status,t.request_at from trips as t join userss as u on t.client_id = u.users_id
join userss as u1 on t.driver_id=u1.users_id where u.banned="No" and u1.banned="No")
select request_at,round(avg(case when status in("cancelled_by_client", "cancelled_by_driver") then 1 else 0 end)*100,2) as per
from cte group by request_at;

[@AmitSingh-f7g3w]

Final Query from my side with help of video
with Client as
(select request_at,count(case when(status='cancelled_by_driver' or status='cancelled_by_client') then 1 else null end) as cancelled_trips, count(1) as total_no_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 a.*,round(cancelled_trips/total_no_trips * 100,2) as Cancelled_Percentage
from client a

[@NaveenKumar-lz6xi]

with cte as(
select * from Trips
inner join
Users
on Trips.client_id = Users.users_id or Trips.driver_id = Users.users_id
where banned != 'Yes'),
cte2 as(
select id,status,request_at, max(case when client_id = users_id then client_id else NULL end) as client_id2,
max(case when driver_id = users_id then driver_id else NULL end) as driver_id2,
case when status = 'completed' then 1 else null end as com_status,
case when status != 'completed' then 1 else null end as cancelled_stat
from cte
group by id,status,request_at
having max(case when client_id = users_id then client_id else null end) is not null
)
select request_at, SUM(cancelled_stat) as cancelled_trip,COUNT(1) as total_trips, round(coalesce(SUM(cancelled_stat)*1.0, COUNT(1))/COUNT(1),2,2)* 100 as canceled_percentage from cte2
group by request_at;

[@arthurmorgan9010]

Sir how is my solution to the problem:
with cte as
(
select * 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'))
)
,cteone as
(
select count(id) as request_count,request_at as date from cte
where status = 'cancelled_by_client' or status='cancelled_by_driver'
group by request_at
)
,ctetwo as
(
select count(id) as total_requests,request_at as dates from cte
group by request_at
)
,ctethree as
(
select co.total_requests,co.dates,ISNULL(request_count,0) as s from ctetwo co
left join cteone cone
on co.dates = cone.date
)
select dates as Day,round((1.0*s/total_requests),2) 'Cancellation Rate' from ctethree

[@deepanshugaur6454]

I think instead of count we should use sum for cancelled trip count.
Here you have only one cancelled trip on each day, so luckily query worked.

[@ankitbansal6]

It will be same because : In "case when" I have put else condition as null. When we do count , nulls are not counted.

[@deepanshugaur6454]

@@ankitbansal6 But, it will affect in percentage calculation as Count will always be 1 no matter even if the number of wins are more than 1.
So, i think it would be better to use sum instead of count.

[@ankitbansal6]

@@deepanshugaur6454 count will count number of not null values.

[@deepanshugaur6454]

Ok. Got your point. I must say your videos are quite informative and useful 👍🙂

[@sikansamantara9504]

Hi @Ankit Below is my try
with CTE as(
select request_at,status,b.users_id as client_id, c.users_id as driver_id 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'
)
select request_at,round(1.0*sum(case when status='cancelled_by_driver' or status='cancelled_by_client' then 1 else 0 end)/count(*)*100 ,2)from CTE
group by request_at

[@arjitakashyap8169]

select request_at
,count_Cancelled_drive = sum(case when status 'completed' then 1 else 0 end )
,Count_Booked_drive = count(1)
,cancellation_rate = convert(decimal(9,2),1.0*sum(case when status 'completed' then 1 else 0 end )/count(1))
from trips t
inner join users u
on t.client_id = u.users_id
inner join users u2
on t.driver_id = u2.users_id
where u.banned = 'no' and u2.banned = 'no'

[@pmohantymohanty7]

MYSQL USING SUBQUERIES, CASE
select *,
case when cancelletion_count > 0 then round((cancelletion_count/total_requests)*100,2) else 0 end as cancelletion_rate_percent
from
(
select request_at, count(id) as total_requests,
count( CASE when status in ('cancelled_by_client', 'cancelled_by_driver') then 1 else null end) as cancelletion_count
from
(
select t.*,u.banned as client_ban_status,u1.banned as driver_ban_status from Trips t
inner join Users u on t.client_id = u.users_id
inner join Users u1 on t.driver_id = u1.users_id
where request_at between '2013-10-01' and '2013-10-03'
and u.banned = 'No' and u1.banned = 'No'
) as alias_table1
group by request_at
) as alias_table2

[@mr.chicomalo4003]

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');
SELECT * FROM Trips;
SELECT * FROM Users;
-- WRITE A SQL QUERY TO FIND THE CANCELLATION RATE OF REQUESTS WITH UNBANNED users_id
-- (BOTH CLIENT AND DRIVER MUST NOT BE BANNED) EACH DAY BETWEEN "2013-10-01" AND "2013-10-03"
-- ROUND CANCELLATION RATE TO TWO DECIMAL POINTS
-- THE CANCELLATION RATE IS COMPUTED BY DIVIDING THE NUMBER OF CANCELLED (BY CLIENT OR DRIVER)
-- REQUESTS WITH UNBANNED USERS BY THE TOTAL NUMBER OF REQUESTS WITH UNBANNED USERS ON THAT DAY
WITH CTE AS (
SELECT request_at,COUNT(CASE WHEN status in ( "cancelled_by_driver","cancelled_by_client") THEN 1 ELSE null END ) AS CANCELLED,
COUNT(*) AS TOTAL_RIDES
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 )
SELECT request_at,ROUND((CANCELLED/TOTAL_RIDES),2) AS CANCELLATION_RATE FROM CTE ;

[@VARISHROCKS]

Thankyou for this amazing video , ! At 07:10 can you please explain the COUNT(1) concept what does 1 signify in it ?

[@ankitbansal6]

COUNT(1) is basically just counting a constant value 1 column for each row. it's the same as COUNT(0) or COUNT(42) . Any non- NULL value will suffice.

[@sachindubey4315]

1 signify the position number of column .
In group he can also doike this
Group by 1,2
Istead of mentioned entire column name

[@lakshaykhanna2462]

No it is not that@@sachindubey4315

[@ashwingupta4765]

with cte as (
select request_at,
count(case when status in ('cancelled_by_client', 'cancelled_by_driver') then 1 else null end) as cancelled_trip_count,
count(*) as total_count
from Trips as t
inner join Users1 as u on t.client_id = u.users_id
inner join users1 as d on t.driver_id = d.users_id
where u.banned = 'No' and d.banned = 'No'
group by request_at)
Select request_at,
(cancelled_trip_count*1.0/total_count)*100.0
from cte

[@anishchhabra6085]

My solution (in MySQL) without looking into the video :
select request_at,
round(sum(case when status like 'cancelled%' then 1 else 0 end)/count(*),2) as cancellation_rate
from trips
where client_id in
(select distinct users_id
from users
where banned = "No")
and driver_id in
(select distinct users_id
from users
where banned = "No")
and request_at between '2013-10-01' and '2013-10-03'
group by request_at
order by request_at;
Please let me know about the sub-queries that I am using, is it better to use the sub query or join as you did in your solution.

[@siddheshkalgaonkar2752]

Below solution works fine in postgres sql:
with status_flag as
(
select id,client_id,driver_id,city_id,request_at,status,case when status='completed' then 0
when status='cancelled_by_driver' then 1 when status ='cancelled_by_client' then 2 else -1 end as
status_flag from trips
),
filter_banned_records as
(
select * from status_flag where id not in (select id from status_flag where client_id in (select users_id from users where banned='Yes') or driver_id in (select users_id from users where banned='Yes'))
)
,
total_trips_flag as
(
select *,count(*) over(partition by request_at order by request_at) as total_trips from filter_banned_records
)
,
cancelled_trips_flag as
(
(select request_at,total_trips,count(*) over(partition by request_at) as cancelled_trips_flag from total_trips_flag where status_flag !=0)
union all
(select request_at,0 as total_trips,0 as cancelled_trips_flag from total_trips_flag)
)
,
block_to_handle_0_records_per_request_at as
(
select request_at, sum(total_trips) as total_trips,sum(cancelled_trips_flag) as cancelled_trips_flag from cancelled_trips_flag
group by request_at order by request_at
)
,
cancellation_rate as
(
select request_at,case when cancelled_trips_flag !='0' then round((cancelled_trips_flag)/(total_trips)::DECIMAL,2)
else 0.00 end as cancellation_rate from block_to_handle_0_records_per_request_at
)
select request_at,cancellation_rate from cancellation_rate;

[@ankitbansal6]

Thanks for posting 👍

[@nikhitamaruvada9385]

My solution -
with cte as
(
select count(t.request_at) as cancelled_rides ,t.request_at
from Tripss t
inner join userss u
on t.client_id = u.users_id
inner join userss d
on t.driver_id = d.users_id
where u.banned = 'No' and d.banned = 'No' and t.status = 'cancelled_by_client' or t.status = 'cancelled_by_driver'
group by t.request_at
),
cte2 AS
(
select count(request_at) as total_requests,request_at
from tripss
group by request_at
)
select (ifnull(round(((1.0*c.cancelled_rides)/c2.total_requests),2),0))*100 as cancellation_rate , c2.request_at
from cte2 c2
left join cte c
on c.request_at = c2.request_at
group by c.request_at

[@exam_prep_cat5605]

with unbanned_users_drivers as (select * from Users where banned ='No')
select request_at
, round(100.0*sum(case when status='completed' then 1 else 0 end)/count(*),2) as comp_perc
, round(100.0*sum(case when status'completed' then 1 else 0 end)/count(*),2) as ncomp_perc
from Trips
where client_id in (select distinct users_id from unbanned_users_drivers where role ='client' )
and driver_id in (select distinct users_id from unbanned_users_drivers where role ='driver' )
and date(request_at) >= date('2013-10-01') and date(request_at)

[@ankitbansal6]

Thanks

[@kartikeyasingh2798]

with cte as(
select T.client_id,T.driver_id,T.status,T.request_at from Trips T inner join users U on T.client_id=U.users_id
inner join users D on T.driver_id=D.users_id where U.banned="No" and D.banned="No"
)
select
request_at,
SUM(case when Z.status="cancelled_by_client" or Z.status="cancelled_by_driver" then 1 else 0 end )
as cancelledtrips,
count(1) as totaltrips,
SUM(case when Z.status="cancelled_by_client" or Z.status="cancelled_by_driver" then 1 else 0 end )/count(1)
as TC
from cte Z group by request_at;

[@ravideshmukh2873]

WITH nobanned as
(SELECT users_id, banned FROM Users
WHERE banned = 'Yes'),
CTE as (SELECT client_id, driver_id, status, request_at
FROM Trips
WHERE client_id NOT IN (SELECT users_id FROM nobanned) AND
driver_id NOT IN (SELECT users_id FROM nobanned))
SELECT request_at, count(1) as total_trips, count(case when status in ('cancelled_by_client', 'cancelled_by_driver') then 1 else null end) as cancelled_count,
1.0*count(case when status in ('cancelled_by_client', 'cancelled_by_driver') then 1 else null end) / count(1) * 100 as cancelled_percent
FROM CTE
GROUP BY request_at

[@swamivivekananda-cyclonicm8781]

Amazing.

[@somenathdey6571]

Hi Ankit..I tried the same solution in leetcode coding area..but it didn't pass all the test cases..can you please help me?..It shown that 9/12 test cases passed..it was the same question and I written ur solution only..thanks in advance 🙂

[@ankitbansal6]

Do you know which cases not passing?

[@somenathdey6571]

@@ankitbansal6 Yes, one of the case was like..only one one row was there..cancelled by client..no banned user..rest 2 cases they were not showing I guess..out of 12 , 9 passed

[@somenathdey6571]

@@ankitbansal6 I would request you..if you run this code over there once if you get time

[@maverick6932]

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)

[@rajunaik8803]

with cte1 as(
select t.*,u.banned,case when t.status='completed' then 0 else 1 end as cancel_flag
from Trips t,users u where t.client_id=u.users_id and u.banned='No'
)
select cte1.request_at,sum(cte1.cancel_flag) total_cancelled_rides,count(cte1.cancel_flag) total_rides,
sum(cte1.cancel_flag)*1.0/count(cte1.cancel_flag) * 100 as cancel_ratio
from cte1 as cte1
group by request_at

[@naveenkumar-tg2lr]

with cte as (
select distinct request_at
,count(1) Total_rides
,sum(case when status in ('cancelled_by_driver','cancelled_by_client') then 1 else 0 end ) cancelled_by_drier_or_client
from Trips t
join Users u on t.client_id = u.users_id
join Users c on t.client_id = c.users_id
where u.banned = 'No' and c.banned = 'No'
group by request_at
)
select request_at
,Total_rides
,cancelled_by_drier_or_client
,(1.0*cancelled_by_drier_or_client/Total_rides) * 100 Cancelled_Percent
from cte

[@ankitbansal6]

Thanks for posting 👏

[@srinivascheemala1287]

My solution:
with cte as (
select t.client_id,t.driver_id,t.status,t.request_at,u.banned as client_banned,u.role as client_role
from trips t
inner join users u
on t.client_id=u.users_id
where u.banned='No'
)
select request_at,
count(case when status like 'cancelled%' then 1 else NULL end) as cancellation_count,count(*) as total_requests,
1.0*count(case when status like 'cancelled%' then 1 else NULL end)/count(*) *100 as cancellation_rate
from (
select c.*,u.banned as driver_banned,u.role as driver_role from
cte c
inner join users u
on c.driver_id=u.users_id
order by request_at)
group by request_at
;

[@innominatesoloist1597]

awesome

[@Alexpudow]

select distinct request_at
,round(cast(count(case when status in ('cancelled_by_driver','cancelled_by_client') and (u.banned like 'No' and u2.banned like 'No')
then id end) over(partition by request_at) as float)
/
count(case when u.banned like 'No' and u2.banned like 'No' then id end) over(partition by request_at)*100,2) result
from Trips_t t
left join Users_t u
on t.client_id=u.users_id and u.role like 'client'
left join Users_t u2
on t.driver_id=u2.users_id and u2.role like 'driver'
order by 1

[@pavitrashailaja850]

🙏🏻🙏🏻

[@ankitbansal6]

😊

[@mrshubhamthakur007]

I think in question we have to find cancellation rate for unbanned user at particular date instead of finding cancellation rate w.r.t date

[@ankitbansal6]

For each date we need to find right .

[@LastCall-z3i]

{"headers": {"Trips": ["id", "client_id", "driver_id", "city_id", "status", "request_at"], "Users": ["users_id", "banned", "role"]}, "rows": {"Trips": [["1", "1", "10", "1", "cancelled_by_client", "2013-10-04"]], "Users": [["1", "No", "client"], ["10", "No", "driver"]]}}
For the above test case it fails. Can anyone check and help me.
Expected output : {"headers":["Day","Cancellation Rate"],"values":[]}

[@siddheshkalgaonkar2752]

Hey Ankit,
If I give you this input
{"headers": {"Trips": ["id", "client_id", "driver_id", "city_id", "status", "request_at"], "Users": ["users_id", "banned", "role"]}, "rows": {"Trips": [["1", "1", "10", "1", "cancelled_by_client", "2013-10-04"]], "Users": [["1", "No", "client"], ["10", "No", "driver"]]}}
what would be the output according to your query? With my query, it is giving me a single record like below
{"headers": ["Day", "Cancellation Rate"], "values": [["2013-10-04", 1.00]]}
This is correct according to me since there was one request made that is canceled but not on the banned list (since we ignore record only if it is 'Yes' for banned) so it should be 1/1 i.e 1.00. So, wanted to check with you if there is any understanding gap on my end.

[@ankitbansal6]

Yes absolutely right.

[@siddheshkalgaonkar2752]

But according to leetcode, it is returning blank

[@ankitbansal6]

@@siddheshkalgaonkar2752 can you share link

[@siddheshkalgaonkar2752]

@@ankitbansal6 Did you get a chance to check it?

[@ankitbansal6]

@@siddheshkalgaonkar2752 can you share the link where u see different result.

[@nihal2725]

challnge solution:
select request_at as 'Day',round(cancelled_trips*1.0/total_trips,2) as 'Cancellation Rate' from
(select request_at,sum(case when status in('cancelled_by_client','cancelled_by_driver') then 1 else 0 end) as cancelled_trips,
count(1) as total_trips
from Trips t
join Users u1 on t.client_id=u1.users_id
join Users u2 on t.driver_id=u2.users_id
where u1.banned='No' and u2.banned='No'
and request_at between '2013-10-01' and '2013-10-03'
group by request_at)x

[@shahakunal1]

Hi Ankit ,
This is one more implementation
SELECT request_at ,
COALESCE(Round(cancelled_requests*1.0/(completed_requests+ cancelled_requests),2),0)
FROM (
SELECT request_at ,
Sum(cancelled_requests) AS cancelled_requests,
Sum(completed_requests) AS completed_requests
FROM (
SELECT request_at ,
Sum(
CASE
WHEN status = 'cancelled' THEN total_number_of_requests
END) AS cancelled_requests ,
Sum(
CASE
WHEN status = 'completed' THEN total_number_of_requests
END) AS completed_requests
FROM (
SELECT request_at,
CASE
WHEN status='cancelled_by_client'
OR status = 'cancelled_by_driver' THEN 'cancelled'
ELSE status
END AS status,
Count(DISTINCT users_id) AS total_number_of_requests
FROM (
SELECT *
FROM users u
JOIN trips t
ON t.client_id = u.users_id
AND banned 'Yes') AS a
GROUP BY
CASE
WHEN status='cancelled_by_client'
OR status = 'cancelled_by_driver' THEN 'cancelled'
ELSE status
END ,
request_at
ORDER BY request_at) AS b
GROUP BY request_at,
status )
GROUP BY request_at) AS c ;

[@addhyasumitra90]

select request_at, sum(case when status!='Completed' then 1 else 0 end) as cancelled_trips,
COUNT(status) as total_trips,
ROUND(100*(1.0*sum(case when status!='Completed' then 1 else 0 end)/COUNT(status)),2) as cancelled_perc
from Trips as t join Users as c ON t.client_ID=c.users_id
join Users as d ON t.driver_ID=d.users_id
where c.banned='No' and d.banned='No'
group by request_at
;

[@rahulmehla2014]

my approach:
with cte as(
select t.*,u.banned as user_ban,u2.banned as driver_ban from trips t
inner join
users u
on t.client_id = u.users_id
inner join users u2 on t.driver_id = u2.users_id
where u.banned = "no" and u2.banned = "no")
select request_at,
round(sum(case when status in ("cancelled_by_client","cancelled_by_driver") then 1 else 0 end)*1.0/count(*) *100,2) as cancellation_rate
from cte group by request_at

[@MojilShah]

with no_of_cancellations as (select t.request_at as request_at, count(t.id) as no_of_rides,
sum(case when t.status = 'completed' then 0 else 1 end) as cancellations
from trips t
join users u on t.client_id=u.users_id
where u.banned = 'No'
group by t.request_at)
select request_at, cast(((cancellations*1.00)/no_of_rides)*100 as decimal(18,2)) as cancellation_rate
from no_of_cancellations;

[@vaibhavverma1340]

Here what is decimal(18,2) mean ?

[@vandanaK-mh9zo]

select request_at, count(case when status in ('cancelled_by_client', 'cancelled_by_driver' ) then 1 else null end ) as canclled_trips,
count(1) as total_trips,
count(case when status in ('cancelled_by_client', 'cancelled_by_driver' ) then 1 else null end ) / count(1) * 100 as cancellation_rate
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 ;

[@rajkumarrajan8059]

select
t.request_at,
count (Case when t.status = 'Completed' then 1 else null end) completed_trips,
Count(t.status)-count (Case when t.status = 'Completed' then 1 else null end) as canceled_trips,
Count(t.status) as total_trips,
round(1.0*(Count(t.status)-count(Case when t.status = 'Completed' then 1 else null end))/Count(t.status)*100,2) as canceled_trip_percent
from trips t
left join users u1 on u1.users_id=t.client_id
left join users u2 on u2.users_id=t.driver_id
where u1.banned like 'No' and u2.banned like 'No'
group by t.request_at

[@Dhanushts-g7x]

with cte1 as (select id,users_id,status,request_at from users join trips
on trips.client_id=users.users_id where banned="no"),
cte2 as (select id,driver_id from users join trips
on trips.client_id=users.users_id where banned="no"),
cte3 as (select request_at,max(cancelled) cancelled from
(select request_at,count(*) cancelled from cte1 join cte2 using (id) where status!="completed"
group by request_at
union
select distinct request_at,0 from trips) a group by request_at order by request_at),
cte4 as
(select request_at,count(*) acc from cte1 group by request_at)
select cancelled/acc from cte3 join cte4 using (request_at)
(CONSIDER MINE FOR BETTER UNDERSTANDING BECAUSE CTE INCREASES THE QUERY READABILITY)

[@GautamKumar-ci4rz]

In oracle
:- WITH client_info AS (
SELECT
t.client_id,
u.banned,
t.driver_id,
t.city_id,
t.status,
t.request_at
FROM
trips t,
users u
WHERE
t.client_id = u.users_id
) SELECT
c1.request_at,
COUNT(c1.client_id) total_rides,
SUM(
CASE
WHEN c1.status IN(
'cancelled_by_client','cancelled_by_driver'
) THEN 1
ELSE 0
END
) / COUNT(c1.client_id) * 100
|| '%' cancellation_ratio
FROM
client_info c1,
users u1
WHERE
c1.driver_id = u1.users_id
AND c1.banned = 'No'
AND u1.banned = 'No'
GROUP BY
c1.request_at;

[@abhishekpurohit3442]

select request_at,
(count(distinct case when status like '%cancelled%' then id end)*1.0)/(count(distinct id)*1.0) as Cancel_rate
from
(
select id,driver_id,client_id,status,request_at,b.banned as client_banned, c.banned as driver_banned
from trips a left join users b on a.client_id=b.users_id
left join users c on a.driver_id=c.users_id
where UPPER(b.banned)='NO' and UPPER(c.banned)='NO'
) as A
where request_at between '2013-10-01' and '2013-10-03'
group by request_at;

[@ankitbansal6]

Thanks for posting 👏

[@LearnYouAndMe]

Here is my solution
select t.request_at,
sum(1) as TotalTrips,
sum(iif(t.status ='completed',0,1)) as CancelledCount,
Round(sum(iif(t.status ='completed',0,1))*100.00/sum(1),2) as CancellationRate
from trips as t
left join Users as uC on uC.users_id=t.client_id
left join Users as uD on uD.users_id=t.driver_id
where uC.banned='No' and uD.banned='No' and t.request_at between '2013-10-01' and '2013-10-03'
group by t.request_at

[@rishabhjain2756]

with t1 as (select t.request_at,
t.status,
case
when t.status like '%cancell%' THEN 1
else 0 END as cancel_status
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' )
-- select * from t1
select request_at as Day, round((sum(cancel_status)*1.00/count(cancel_status)),2) as 'Cancellation Rate'
from t1
where request_at = '2013-10-01'
group by request_at

[@monasanthosh9208]

MYSQL Solution
Select Request_At,round(Cancelled_trip/Requested_trip*100,2) as Cancelled_Rate from
(Select Request_At, Count(Status) as requested_Trip,
Count(Case When Status Like "%Cancelled_By%" then 1 end) as Cancelled_Trip from
(Select N.Client_id,N.Status,N.Request_At from
(Select T.Client_id,T.Driver_id,T.Status,T.Request_At,U.Banned from Trips t
Left Join Users U on T.Client_id=U.Users_Id where Banned = "No")N
Left Join Users U on N.Driver_id=U.Users_id where U.Banned="No")N group by request_At)N;

[@amrutaborse2163]

select id,client_id,driver_id,city_id,status,request_at,round(cnt2/cnt*100,2) cnt3 from
(select id,client_id,driver_id,city_id,request_at,
status,count(*) over(partition by request_at order by request_at) cnt,
count(*) over(partition by request_at,status order by request_at) cnt2
from trips12 a,users12 b,users12 c
where client_id = b.users_id(+)
and driver_id = c.users_id(+)
and b.banned ='No'
and c.banned ='No'
order by request_at,id)
where status like '%cancelled%';

[@hustlerguy6091]

My solution:
select request_at, round(100.0*count(case when status'completed' then id else null end)/count(*),2) cancellation_rate
from trips
JOIN users u1 ON trips.client_id=u1.users_id AND u1.banned='No'
JOIN users u2 ON trips.driver_id=u2.users_id AND u2.banned='No'
where request_at >='2013-10-01' AND request_at

[@anirvansen2941]

my mysql solution
with base as (select t.*,u.banned as user_banned,u1.banned as driver_banned from
Trips t join Users u on t.client_id = u.users_id and u.banned = 'No'
join users u1 on t.driver_id = u1.users_id and u1.banned = 'No')
select request_at,
concat(round(sum(case when status like 'cancelled%' then 1 else 0 end) / count(1) * 100,2), '%') as cancellation_rate
from base
group by request_at
order by request_at

[@TusharKumar-u4p]

SELECT request_at AS Day,
ROUND( SUM( CASE WHEN status LIKE '%cancelled%' THEN 1 ELSE 0 END) / COUNT(*), 2) AS "Cancellation Rate"
FROM Trips
WHERE request_at >= '2013-10-01' AND request_at

[@ChaitanyaKariya-x4q]

with yess as(
select * from Trips T1 left join users t2 on
t1.client_id = t2.users_id ) ,
yess2 as(
select * from Trips T1 left join users t2 on
t1.driver_id = t2.users_id),
okay as (
select * from (
select y1.id as id , y1.client_id as clientid,y1.driver_id as driverid , y1.city_id as cityid,y1.status as status ,y1.request_at as requestat, y1.banned as client_banned, y2.banned as driver_banned,y1.role as role, y2.role as D_role
from yess y1 full outer join yess2 y2 on
y1.id = y2.id)
where client_banned = 'No' and driver_banned = 'No')
select *,round((100.0*cancelled_trip)/nullif(total_rides,0),2) as netride from (
select requestat ,total_rides , sum (cancelled_trip) as cancelled_trip from (
select status,requestat , count(*) over(partition by requestat) as total_rides,
case when status = 'completed' then 0 else 1 end as cancelled_trip
from okay) y
group by requestat ,total_rides
)z

[@sameerpandey2140]

WITH cte as(
SELECT
a.*,
b.banned as client_status,
c.banned as driver_status
FROM
trips a
LEFT JOIN users b
ON a.client_id = b.users_id
LEFT JOIN users c
ON a.driver_id = c.users_id
)
SELECT
request_at,
cast(greatest(0,SUM(CASE WHEN status like '%cancelled%' THEN 1 END))/count(status) as decimal(10,2))*100 cancellation_rate
FROM cte
WHERE
client_status = 'No'
OR driver_status = 'No'
GROUP BY 1

[@PraveenSinghRathore-df3td]

with unbanned_users as (select t.*,u.banned as ban_status from trips t join
users u on t.client_id = u.users_id
join users d on t.driver_id = d.users_id
where u.banned 'Yes' and d.banned 'Yes'),
cancellation_rate as (select request_at, sum(case when status like '%cancelled%' then 1 else 0 end) as cancel_flg,
count(*) as total_rides from unbanned_users
group by request_at order by request_at)
select request_at, (cancel_flg/total_rides) * 100 as cancel_rate from cancellation_rate;

[@HimanshuSingh-cf7wr]

with k as (select request_at,status,banned,client_id from Trips left join Users on Trips.driver_id=Users.users_id ),
t as (select request_at,case when k.banned='Yes' or users.banned='Yes' then 'Yes' else 'No' end as banned,
case when k.status='cancelled_by_client' or k.status='cancelled_by_driver' then 'cancelled' else 'completed' end as status
from k
left join Users on k.client_id=Users.users_id ),
n as (
select request_at,case when status='cancelled' then 1 else 0 end as banned_order from t where banned'Yes')
select request_at,(sum(banned_order)*100/count(banned_order)) as percentage_decline from n group by request_at

[@tahakhalid324]

Hi ankit here is my solution: WITH trips_users AS (
SELECT
t.id,
t.client_id,
u1.banned AS client_banned,
t.driver_id,
u2.banned AS driver_banned,
t.city_id,
t.status,
t.request_at
FROM Trips t
JOIN users u1 ON t.client_id = u1.users_id
JOIN users u2 ON t.driver_id = u2.users_id
), overalL_rides_by_unbanned_users AS (
SELECT
COUNT(status) AS ride_made,
request_at
FROM trips_users
WHERE client_banned = 'No'
AND driver_banned = 'No'
AND request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY request_at
), cancelled_rides_by_unbanned_users AS (
SELECT
COUNT(status) AS ride_made,
request_at
FROM trips_users
WHERE client_banned = 'No'
AND driver_banned = 'No'
AND (status = 'cancelled_by_driver' OR status = 'cancelled_by_client')
AND request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY request_at
)
SELECT
ROUND(COALESCE((crbuu.ride_made / orbuu.ride_made) * 100.0, 0), 2) AS cancelled_rides_percentage,
orbuu.request_at
FROM cancelled_rides_by_unbanned_users crbuu
RIGHT JOIN overalL_rides_by_unbanned_users orbuu
ON crbuu.request_at = orbuu.request_at;

[@arjunanand1541]

long but broken down solution:
WITH t1 AS (
SELECT
t.*,
u.banned AS banned_client,
u1.banned AS banned_driver
FROM
trips t
INNER JOIN
Userr u ON t.client_id = u.users_id
INNER JOIN
Userr u1 ON t.driver_id = u1.users_id
),
t2 AS (
SELECT
t1.*,
CASE
WHEN t1.banned_client = 'Yes' OR t1.banned_driver = 'Yes' THEN 0
ELSE 1
END AS req_cnt
FROM
t1
),
t3 AS (
SELECT
request_at,
COUNT(*) AS cancel_cnt
FROM
t1
WHERE
banned_client = 'No'
AND banned_driver = 'No'
AND status 'completed'
GROUP BY
request_at
)
SELECT
a.request_at,
COALESCE(CAST(a.cancel_cnt AS DECIMAL) / NULLIF(a.total_req, 0), 0) AS cancel_rate
FROM (
SELECT
t2.request_at,
SUM(t2.req_cnt) AS total_req,
COALESCE(t3.cancel_cnt, 0) AS cancel_cnt
FROM
t2
LEFT JOIN
t3 ON t2.request_at = t3.request_at
GROUP BY
t2.request_at
) a;

[@ashishkumarjha2587]

#DAY_3
Hi guys could you please help me little bit why I am getting different result could you please help me debug
WITH client_table AS (SELECT
u.users_id
, t.city_id, t.request_at, t.status
FROM Users u
JOIN Trips t
ON t.client_id = u.users_id AND u.role = "client" AND request_at BETWEEN "2013-10-01" AND "2013-10-03" AND banned = "No"
),
driver_table AS ( SELECT
u.users_id
, t.city_id, t.request_at, t.status
FROM Users u
JOIN Trips t
ON t.driver_id = u.users_id AND u.role = "driver" AND request_at BETWEEN "2013-10-01" AND "2013-10-03" AND banned = "No"
),
unbanned_user AS(
SELECT
*
FROM client_table
UNION
SELECT
*
FROM driver_table
)
SELECT
request_at
,SUM(IF(status LIKE "cancelled%" ,1,0)) as sum_cancle
,COUNT(1) as total_count
,ROUND(SUM(IF(status LIKE "cancelled%" ,1,0)) / COUNT(1) ,2) AS ratio
FROM unbanned_user
GROUP BY request_at

[@bhanurathore8939]

SELECT
request_at,
COUNT(*) AS total_trips, SUM(CASE WHEN status != 'completed' THEN 1 ELSE 0 END) AS cancelled_trips,
ROUND((1.00 * SUM(CASE WHEN status != 'completed' THEN 1 ELSE 0 END)/COUNT(*)) * 100,2) AS cancel_per
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