LeetCode Trips and Users题SQL代码单测试用例失败求排查

我在准备数据科学面试时，遇到了LeetCode上的Trips and Users问题。我的代码通过了绝大多数测试用例，但有一个测试用例未通过，希望帮忙分析原因。

我的代码 #

# 未封禁用户的每日总订单数
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 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

测试用例数据 #

Trips表 #

Users表 #

输出对比 #

• 我的代码输出：

• 预期输出：

问题原因 #

你的代码只过滤了未被封禁的乘客，但题目要求：只有当乘客和司机都未被封禁时，该订单才需要被计入统计。

在这个测试用例中，司机ID为10的用户处于封禁状态（banned = 'Yes'），所以这个订单应该被排除在统计之外，导致当日的总订单数（total_rides）为0，最终没有符合条件的日期数据，因此预期输出是空表。

修正后的代码 #

with valid_trips as (
    SELECT 
        t.request_at as day,
        CASE WHEN t.status LIKE 'cancelled%' THEN 1 ELSE 0 END is_cancelled
    FROM trips t
    JOIN users client ON t.client_id = client.users_id AND client.banned = 'No' AND client.role = 'client'
    JOIN users driver ON t.driver_id = driver.users_id AND driver.banned = 'No' AND driver.role = 'driver'
    WHERE t.request_at BETWEEN '2013-10-01' AND '2013-10-03'
),

daily_stats as (
    SELECT 
        day,
        COUNT(*) as total_rides,
        SUM(is_cancelled) as cancelled_rides
    FROM valid_trips
    GROUP BY day
)

SELECT 
    day,
    ROUND(cancelled_rides / total_rides, 2) as "cancellation rate"
FROM daily_stats

内容的提问来源于stack exchange，提问作者Musa