CODEWITHSUNDEEP
mysqlsql
DJ_Stuffy_K
DJ_Stuffy_K

Reputation: 635

SQL query to find the cancellation rate of requests made between two dates using WITH

I'm trying to understand the right way to divide the count sums from two queries.

I'm teaching myself sql and practising it on line.
Question:

Write a SQL query to find the cancellation rate of requests made between 2017-08-01 and 2017-08-03. The cancellation rate is given by dividing the number of cancelled requests by the total number of rides each day. The result table should have 2 Columns, namely Day that shows each day and Cancellation Rate that provides the cancellation rate of that day.
Table is:
enter image description here What I tried was:

  1. count cancelled ride rates per date
  2. count all ride requests per date
  3. divide both the counts per date
     with  
    cancelled_rides as 
    (select count(*) cancel_count, status, Request_id 
    from TRIPS 
    where status = 'cncld_driver' 
    group by state, Request_id)
     
    all_rides as ( 
    select count(*) day_count, status, Request_id 
    from TRIPS 
    group by state, Request_id) ,
     
    select cancelled_rides.Request_id as DAY, 
    (cancelled_rides.cancel_count/all_rides.day_count) as 'Cancellation Rate' 
    FROM cancelled_rides, all_rides;

Does this look right? Note I purposefully ignored including date ranges as the table has only limited entries.

Upvotes: 0

Views: 2233

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

I do not see that a CTE helps at all for this query. Just use conditional aggregation:

select t.Request_id as day, count(*) as total,
       sum( status = 'cncld_driver' ) as num_cancelled, 
       avg( status = 'cncld_driver' ) as cancellation_rate
from trips t
where request_id >= '2017-08-01' and 
      request_id < '2017-08-04'
group by request_id;

Calling a date "request_id" is rather confusing. You should have a request id that is unique for each row and a separate column with the date/time.

Upvotes: 1

Related Questions