262.行程和用户

链接：262.行程和用户
难度：Hard
标签：数据库
简介：编写解决方案找出 "2013-10-01" 至 "2013-10-03" 期间非禁止用户（乘客和司机都必须未被禁止）的取消率。非禁止用户即 banned 为 No 的用户，禁止用户即 banned 为 Yes 的用户。其中取消率 Cancellation Rate 需要四舍五入保留 两位小数 。

题解 1 - sql

• 编辑时间：2024-10-16
• 执行用时：767ms
• 编程语言：sql
• 解法介绍：先用子查询查找到每个时间段的符合条件的取消次数，再在外部查询中相加求结果

select 
    t1.request_at as 'Day',
    convert((ifnull(sub1.cancelled_by_driver_count, 0) + ifnull(sub2.cancelled_by_client_count, 0)) / count(t1.id), decimal(6, 2)) as 'Cancellation Rate'
from Trips t1
    left join Users u1 on t1.driver_id = u1.users_id
    left join Users u2 on t1.client_id = u2.users_id
     left join (
        select 
            count(*) as cancelled_by_driver_count,
            t2.request_at as request_at
        from 
            Trips t2
            left join Users u3 on t2.driver_id = u3.users_id
            left join Users u4 on t2.client_id = u4.users_id
        where 
            t2.status = 'cancelled_by_driver' and u3.banned = 'No' and u4.banned = 'No'
        group by t2.request_at
    ) sub1 on sub1.request_at = t1.request_at
     left join (
        select 
            count(*) as cancelled_by_client_count,
            t2.request_at as request_at
        from 
            Trips t2
            left join Users u5 on t2.driver_id = u5.users_id
            left join Users u6 on t2.client_id = u6.users_id
        where 
            t2.status = 'cancelled_by_client' and u5.banned = 'No' and u6.banned = 'No'
        group by t2.request_at
    ) sub2 on sub2.request_at = t1.request_at
where 
    u1.banned = 'No' and u2.banned = 'No' and 
    t1.request_at >= '2013-10-01' and t1.request_at <= '2013-10-03'
group by t1.request_at
order by t1.request_at