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: Users table: Output: Here's my code: And the output is: 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. Answer The

Postgresql sum not working as expected when it is clear

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.

Answer

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:

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

Advertisement

Answer