I am solving the following Hard Leetcode SQL Question.

Link to Question: https://leetcode.com/problems/trips-and-users/ (You can directly look at the solution and understand the problem)

Question:

Trips table:

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

Users table:

 ---------- -------- -------- 
| users_id | banned | role   |
 ---------- -------- -------- 
| 1        | No     | client |
| 2        | Yes    | client |
| 3        | No     | client |
| 4        | No     | client |
| 10       | No     | driver |
| 11       | No     | driver |
| 12       | No     | driver |
| 13       | No     | driver |
 ---------- -------- -------- 

Output:

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

Here's my code:

SELECT request_at,
count(*) c,
sum(case when status='cancelled_by_driver' or status='cancelled_by_client' then 1 else 0 end) s,
round(sum(case when status='cancelled_by_client' or status='cancelled_by_client' then 1 else 0 end)/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;

And the output is:

 request_at | c | s | cancellation_rate 
------------ --- --- -------------------
 2013-10-01 | 3 | 1 |              0.00
 2013-10-03 | 2 | 1 |              0.00
 2013-10-02 | 2 | 0 |              0.00

How is the cancellation_rate is 0.00 when it is clear by looking at previous columns(s/c) that it should be 0.33,0.50, 0.00.

CodePudding user response：

The good news is you're only off by a typo.

In your example you are using cancelled_by_client or cancelled_by_client

round(sum(case when status='cancelled_by_client' or status='cancelled_by_client' then 1 else 0 end)/count(*),2) as Cancellation_rate 

rather than:

.. when status='cancelled_by_driver' or status='cancelled_by_client' then ..

which would return: