Pirate X Pirate X - 3 months ago 7
SQL Question

Calculation going wrong due to JOIN issue

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


My attempt -

WITH src
AS (SELECT Count(status) AS Denom,
request_at
FROM trips
WHERE status = 'completed'
GROUP BY request_at),
src2
AS (SELECT Count(status) AS Num,
request_at
FROM trips
WHERE status <> 'completed'
GROUP BY request_at)
SELECT Cast(Count(num) AS FLOAT)/Cast(Count(Denom) AS FLOAT) AS cancel_rate,
trips.request_at
FROM src,
src2,
trips
GROUP BY trips.request_at;


I am trying to find the cancellation rate per day but it is clearing wrong (MY OUTPUT)-

+-------------+------------+
| cancel_rate | request_at |
+-------------+------------+
| 24 | 2013-10-01 |
| 18 | 2013-10-02 |
| 18 | 2013-10-03 |
+-------------+------------+


The cancellation rate for 2013-10-01 should be 0.5 and not 24. Similarly for other dates it should be different.

I know the problem lies with this part but I do not know what is the correct way or how to approach it

SELECT Cast(Count(num) AS FLOAT)/Cast(Count(Denom) AS FLOAT) AS cancel_rate,
trips.request_at
FROM src,
src2,
trips


Is there any way to put in more than 1 select statement in
With NAME as ()
clause ? So that I won't use any JOIN or multiple tables.

Answer

Use conditional aggregation:

SELECT SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) as denom,
       SUM(CASE WHEN status <> 'completed' THEN 1 ELSE 0 END) as num,
       AVG(CASE WHEN status <> 'completed' THEN 1.0 ELSE 0 END) as cancel_rate
       ) 
FROM trips 
GROUP BY request_at;

Note that calculation for the cancel_rate. This is simpler to do using AVG() rather than dividing the two values. The use of 1.0is because SQL Server does integer arithmetic, so 1 / 2 is 0 rather than 0.5.