SQL面试题练习 —— 计算用户首单是即时单的比例

题目来源：美团。

1 题目

在外卖订单中，有时用户会指定订单的配送时间。现定义：如果用户下单日期与期望配送日期相同则认为是即时单，如果用户下单日期与期望配送时间不同则是预约单。每个用户下单时间最早的一单为用户首单，请计算用户首单中即时单的占比。

t_user_order

2 建表语句

--建表语句
CREATE TABLE t_user_order
(
order_id string COMMENT '订单ID',
user_id string COMMENT '用户ID',
order_time string comment '下单时间',
desire_date string comment '期望送达日期'
) COMMENT '用户订单记录表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' 
;
--插入数据
insert into t_user_order
values
('1001','001','2024-07-01 12:01:23','2024-07-01'),
('1002','001','2024-07-01 12:03:23','2024-07-02'),
('1003','002','2024-07-01 13:03:23','2024-07-02'),
('1004','002','2024-07-01 13:07:23','2024-07-01'),
('1005','003','2024-07-01 15:03:23','2024-07-01')

3 题解

1. 找到用户首单，并判断是否是即时单

select order_id,
       user_id,
       order_time,
       desire_date,
       is_instant
from (select order_id,
             user_id,
             order_time,
             desire_date,
             if(to_date(order_time) = to_date(desire_date), 1, 0)              as is_instant,
             row_number() over (partition by user_id order by order_time asc ) as rn
      from t_user_order) t
where rn = 1

执行结果

2. 统计用户首单总单数和即时单数

select 
    count(case when is_instant = 1 then order_id end) as instant_cnt,
    -- count(if(is_instant = 1, order_id, null)) as instant_cnt,
    count(order_id) as total_cnt
from (select order_id,
             user_id,
             order_time,
             desire_date,
             if(to_date(order_time) = to_date(desire_date), 1, 0)              as is_instant,
             row_number() over (partition by user_id order by order_time asc ) as rn
      from t_user_order) t
where rn = 1

执行结果

3. 计算即时单比例

select
    round(count(case when is_instant = 1 then order_id end)/count(order_id),2) as instant_per
from (select order_id,
             user_id,
             order_time,
             desire_date,
             if(to_date(order_time) = to_date(desire_date), 1, 0)              as is_instant,
             row_number() over (partition by user_id order by order_time asc ) as rn
      from t_user_order) t
where rn = 1

执行结果