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

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

[@LearningwithRohan]

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

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

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

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

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

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

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

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

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

[@ramakumarguntamadugu1299]

very good explanation with out using cte 👌👌

[@neelamkushwaha1703]

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

[@ankitbansal6]

My pleasure🙏

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

[@weshallbe]

As usual, clean solution and great explanation!

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

[@Abhilash_Jose]

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

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

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

[@codespacelk]

Thanks Ankit, really worth watching 😆

[@ankitbansal6]

My pleasure 😊

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

[@dgvj]

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

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

[@gagansingh3481]

Great awesome explanation Ankit 👏

[@shreyashchoudhary6827]

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

[@ankitbansal6]

Welcome

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

[@aayushibirla2590]

such an amazing solution

[@SumanGhosh-vn3tx]

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

[@ankitbansal6]

Sure.

[@tunguyenanh3071]

thank you for your solution ! appreciate that !

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

[@swamivivekananda-cyclonicm8781]

Amazing.

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

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

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

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

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

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

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

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

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

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

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

[@innominatesoloist1597]

awesome

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

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

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

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

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

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

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

[@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":[]}

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

[@shrinjaysoni99]

select request_at, total_attempted_tripp-total_success_tripp as cancelled,total_attempted_tripp, (total_attempted_tripp -total_success_tripp)*1.0/total_attempted_tripp as cancelation_percent from ( select request_at,sum(success_flag) as total_success_tripp ,count(success_flag) as total_attempted_tripp from( select request_at , case when status="completed" then 1 else 0 end as success_flag from trips t inner join users u on t.client_id=u.users_id where u.banned="No" ) a group by request_at) b

[@shrinjaysoni99]

this works

[@TusharKumar-u4p]

with cu as ( SELECT * FROM Trips LEFT JOIN Users ON Trips.client_id = Users.users_id ) SELECT cu.request_at AS Day, ROUND ( SUM(CASE WHEN cu.status != 'completed' THEN 1 ELSE 0 END) / COUNT(*) , 2) AS "Cancellation Rate" FROM cu LEFT JOIN Users ON cu.driver_id = Users.users_id WHERE cu.banned = 'NO' AND Users.banned = 'NO' AND cu.request_at >= '2013-10-01' AND cu.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