美团大数据面试SQL-计算用户首单是即时单的比例

数据仓库晨曦

发布于 2024-07-25 12:44:02

800

发布于 2024-07-25 12:44:02

文章被收录于专栏：数据仓库技术

一、题目

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

t_user_order

+-----------+----------+----------------------+--------------+
| order_id  | user_id  |      order_time      | desire_date  |
+-----------+----------+----------------------+--------------+
| 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   |
+-----------+----------+----------------------+--------------+

二、分析

题目中给出了即时单和首单的定义，需要先找到每个用户的首单，然后进行判断是否是即时单；
计算完成之后，需要统计出共有多少首单，其中即时单的比例。

维度	评分
题目难度	⭐️⭐️⭐️
题目清晰度	⭐️⭐️⭐️⭐️⭐️
业务常见度	⭐️⭐️⭐️⭐️⭐️

三、SQL

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

使用row_number函数，找到每个用户的首单，然后根据订单时间和预期送达时间判断是否是即时单

执行SQL

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

查询结果

+-----------+----------+----------------------+--------------+-------------+
| order_id  | user_id  |      order_time      | desire_date  | is_instant  |
+-----------+----------+----------------------+--------------+-------------+
| 1001      | 001      | 2024-07-01 12:01:23  | 2024-07-01   | 1           |
| 1003      | 002      | 2024-07-01 13:03:23  | 2024-07-02   | 0           |
| 1005      | 003      | 2024-07-01 15:03:23  | 2024-07-01   | 1           |
+-----------+----------+----------------------+--------------+-------------+

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

执行SQL

select 
    count(case when is_instant = 1 then order_id end) 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

查询结果

+--------------+------------+
| instant_cnt  | total_cnt  |
+--------------+------------+
| 2            | 3          |
+--------------+------------+

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

查询结果

+--------------+
| instant_per  |
+--------------+
| 0.67         |
+--------------+

四、建表语句和数据插入

--建表语句
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')

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2024-07-24，如有侵权请联系 cloudcommunity@tencent.com 删除

大数据