I'm close to finishing a LeetCode hard problem. The idea is find the cancellation rate (percent) for a date range given that neither the driver or the client has been banned from a ride sharing service.
The problem I'm running into is that on October 2nd, there were no cancellations; using an inner join, this date is omitted. Using a right join, the value is returned as NULL. I need this NULL value to be mapped to 0.
The problem: https://leetcode.com/problems/trips-and-users/
My code:
SELECT output2.day AS "Day", ROUND(output1.failed/output2.success,2) AS "Cancellation Rate"
FROM
((SELECT t1.request_at AS Day, COUNT(*) AS failed
FROM Trips T1
WHERE T1.Status != 'completed'
AND (T1.request_at BETWEEN '2013-10-01' AND '2013-10-03')
AND T1.client_id IN
(SELECT users_id
FROM users
WHERE role = 'client'
AND banned = 'No')
AND t1.driver_id IN
(SELECT users_id
FROM users U2
WHERE role = 'driver'
AND banned = 'No')
GROUP BY t1.request_at
) AS output1
RIGHT JOIN
(SELECT t2.request_at AS Day, COUNT(*) AS success
FROM Trips T2
WHERE (T2.request_at BETWEEN '2013-10-01' AND '2013-10-03')
AND T2.client_id IN
(SELECT users_id
FROM users
WHERE role = 'client'
AND banned = 'No')
AND t2.driver_id IN
(SELECT users_id
FROM users
WHERE role = 'driver'
AND banned = 'No')
GROUP BY t2.request_at
) AS output2
ON output1.Day = output2.Day)
GROUP BY output2.day
My Output:
{"headers": ["Day", "Cancellation Rate"], "values": [["2013-10-01", 0.33], ["2013-10-02", null], ["2013-10-03", 0.50]]}
Desired Output:
{"headers": ["Day", "Cancellation Rate"], "values": [["2013-10-01", 0.33], ["2013-10-02", 0.00], ["2013-10-03", 0.50]]}
I've seen various community recommendations, such as using the functions ISNULL or IFNULL. But both returned an error. Any ideas?
You could just use COALESCE() in the most outer query to turn NULL values to 0s:
SELECT
output2.day AS "Day",
COALESCE(ROUND(output1.failed/output2.success,2), 0) AS "Cancellation Rate"
FROM ...
Note, however, that you query could be largely simplified by using joins and conditional aggregation. Something like this should be close to what you want:
select
t.request_at as day,
avg(t.status != 'completed') as cancellation_rate
from trips t
inner join users uc
on uc.users_id = t.client_id and uc.role = 'client' and uc.banned = 'no'
inner join users ud
on ud.users_id = t.client_id and ud.role = 'driver' and ud.banned = 'no'
where t.request_at between '2013-10-01' and '2013-10-03'
group by t.request_at