Risso
Newbie level 5
I am having difficulty passing all of the test cases when preparing for a data science interview. My code is only successful in some of the tests.
Can someone help explain why my code doesn't work for this question.
Here is the code:
Can someone help explain why my code doesn't work for this question.
Here is the code:
Code:
# total rides by unbanned users by day
with total_rides as (SELECT t.request_at as day,
count(1) as total_rides
from trips t join
users u on t.client_id = u.users_id and u.banned = 'No' and request_at between '2013-10-01' and '2013-10-03'
group by 1),
# cancelled rides by unbanned users by day
cancelled_rides as (
SELECT t.request_at as day,
count(1) as cancelled_rides
from trips t join
users u on t.client_id = u.users_id
where t.status like 'cancelled%'and u.banned = 'No' and request_at between '2013-10-01' and '2013-10-03'
group by 1
),
combined as(select tr.day, tr.total_rides as total_rides, cr.cancelled_rides as cancelled_rides from cancelled_rides cr
right join total_rides tr on cr.day = tr.day)
select day, case when cancelled_rides is NULL then 0
else round(cancelled_rides/total_rides,2) end as "cancellation rate"
from combined