"Leetcode" Trips and Users

The Trips table holds all taxi trips. Each trips has a unique Id, while client_id and driver_id is both foreign keys to the users_id at the Users table. The Status is an ENUM of type of (' Completed ', ' cancelled_by_driver ', ' cancelled_by_client ').

+----+-----------+-----------+---------+--------------------+----------+| Id | client_id | driver_id |        city_id | Status | request_at|+----+-----------+-----------+---------+--------------------+----------+|     1 |    1 |    10 |     1 | Completed |2013-10-01| |     2 |    2 |    11 | 1 | cancelled_by_driver|2013-10-01| |     3 |    3 |    12 |     6 | Completed |2013-10-01| |     4 |    4 |    13 | 6 | cancelled_by_client|2013-10-01| |     5 |    1 |    10 |     1 | Completed |2013-10-02| |     6 |    2 |    11 |     6 | Completed |2013-10-02| |     7 |    3 |    12 |     6 | Completed |2013-10-02| |     8 |    2 |    12 |     12 | Completed |2013-10-03| |     9 |    3 |    10 |     12 | Completed |2013-10-03| |     10 |    4 |    13 | 12 | cancelled_by_driver|2013-10-03|+----+-----------+-----------+---------+--------------------+----------+ 

The Users table holds all users. Each user has a unique users_id, and Role is an ENUM type of (' Client ', ' driver ', ' partner ').

+----------+--------+--------+| users_id | Banned |  Role  |+----------+--------+--------+|    1     |   No   | client | |    2     |   Yes  | client | |    3     |   No   | client | |    4     |   No   | client | |    Ten    |   No   | driver | |    One    |   No   | driver | |    |   No   | driver | |    |   No   | driver |+----------+--------+--------+

Write a SQL query to find the cancellation rate of requests made by unbanned clients between Oct 1, and oct 3,. For the above tables, your SQL query should return the following rows with the cancellation rate being rounded to decimal places.

+------------+-------------------+|     Day    | Cancellation rate |+------------+-------------------+| 2013-10-01 |       0.33        | | 2013-10-02 |       0.00        | | 2013-10-03 |       0.50        |+------------+-------------------+

Problem solving ideas: The subject belongs to the hard level, but I personally think it is not very difficult.

The title means that the three days from 2013-10-01 to 2013-10-03, the rate of cancellation of orders per day for non-banned users, including passenger cancellations and driver cancellations, is required.

1. Take out all the records that meet the criteria, this time add a sort by date, for subsequent statistics use

Select *  fromTrips A, Users bwherea.client_id=b.users_id andB.role= 'Client'  andb.banned= 'No'  andRequest_atbetween '2013-10-01'  and '2013-10-03' Order  byRequest_at;

2. Loop through all eligible records, introduce intermediate traversal @day (the date of the current record), @lastday (the date of the previous record), @snum (number of records completed), @cnum (number of records canceled), @totalnum (total number of records). If the @day and @lastday are equal, indicating the date on the same day, according to the order status respectively to @snum or @cnum plus 1, while giving @totalnum plus 1;
If not equal, indicates that the date is transformed, if the order status succeeds let @snum = 1, @cnum = 0, if the failure lets @snum = 0, @cnum = 1, while giving @totalnum = 1. This is why the first step is sorted by date, the @snum in the last record of the same date after the traversal is completed, @cnum, @totalnum is the number of states that the date corresponds to.

SelectRequest_at,@lastday:=@day, Case  when @day = "'  Then @day:=Request_at when @day !=Request_at Then(@day:=REQUEST_AT)Else @day:=Request_atEnd,@snum:= if(@lastday =Request_at,if(Status='completed',@snum+1,@snum),if(Status='completed',@snum:=1,@sum:=0)) asSuccess,@cnum:= if(@lastday =Request_at,if(Status!='completed',@cnum+1,@cnum),if(Status!='completed',@cnum:=1,@csum:=0)) asfail,@totalnum:= if(@lastday =Request_at,@totalnum+1,@totalnum:=1) as Total from(Selectrequest_at,status,client_id fromTripsOrder  byREQUEST_AT) A, Users B, (Select @snum:=0,@cnum:=0,@day:="',@lastday:="',@totalnum:=0) cwherea.client_id=b.users_id andB.role= 'Client'  andb.banned= 'No' Order  byRequest_at,totaldesc;

3. @cnum divided by @totalnum to find out the quotient can

SelectRequest_at as  Day,round(fail/Total2) as 'Cancellation Rate'  from(SelectRequest_at,@lastday:=@day, Case  when @day = "'  Then @day:=Request_at when @day !=Request_at Then(@day:=REQUEST_AT)Else @day:=Request_atEnd,@snum:= if(@lastday =Request_at,if(Status='completed',@snum+1,@snum),if(Status='completed',@snum:=1,@sum:=0)) asSuccess,@cnum:= if(@lastday =Request_at,if(Status!='completed',@cnum+1,@cnum),if(Status!='completed',@cnum:=1,@csum:=0)) asfail,@totalnum:= if(@lastday =Request_at,@totalnum+1,@totalnum:=1) as Total from(Selectrequest_at,status,client_id fromTripsOrder  byREQUEST_AT) A, Users B, (Select @snum:=0,@cnum:=0,@day:="',@lastday:="',@totalnum:=0) cwherea.client_id=b.users_id andB.role= 'Client'  andb.banned= 'No'  andRequest_atbetween '2013-10-01'  and '2013-10-03' Order  byRequest_at, Totaldesc) dGroup  byRequest_at;

"Leetcode" Trips and Users