美团大数据面试SQL-用户购买频次分布

⚠️ 待修正

一、题目背景

这道题来自美团的数据分析岗面试。美团是本地生活服务（外卖、到店、酒旅），数据分析师需要从海量业务数据中挖掘洞见。

业务场景：用户消费行为相关需求在日常工作中频繁出现，这类型的SQL题是面试高频考点。

一、题目

现有一张用户订单表 t8_user_orders，记录了用户每次下单的记录。请统计用户的购买频次分布：将用户按历史累计下单次数分段，统计各段的用户数。

频次区间	标签
1次	新用户
2-3次	普通用户
4-6次	活跃用户
7次以上	忠实用户

用户订单表 t8_user_orders：

+----------+----------+---------------------+
| order_id | user_id  |     order_time      |
+----------+----------+---------------------+
| O001     | u01      | 2023-03-01 10:00:00 |
| O002     | u02      | 2023-03-01 10:30:00 |
| O003     | u01      | 2023-03-01 11:00:00 |
| O004     | u03      | 2023-03-01 11:30:00 |
| O005     | u01      | 2023-03-02 10:00:00 |
| O006     | u02      | 2023-03-02 10:30:00 |
| O007     | u01      | 2023-03-03 09:00:00 |
| O008     | u04      | 2023-03-03 10:00:00 |
| O009     | u01      | 2023-03-04 11:00:00 |
| O010     | u01      | 2023-03-05 12:00:00 |
| O011     | u05      | 2023-03-05 12:30:00 |
| O012     | u01      | 2023-03-06 12:00:00 |
+----------+----------+---------------------+

三、思路分析

本题考察两层聚合：先按用户统计下单次数，再按频次分段统计用户数。

解题步骤：

1. 按 user_id 分组统计每个用户的下单次数；
2. 使用 CASE WHEN 将下单次数映射到频次区间；
3. 按频次区间分组统计用户数；

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

四、逐步推导

1. 统计每个用户的下单次数

执行SQL

select user_id,
       count(1) as order_cnt
from t8_user_orders
group by user_id

执行结果

+----------+-----------+
| user_id  | order_cnt |
+----------+-----------+
| u01      | 7         |
| u02      | 2         |
| u03      | 1         |
| u04      | 1         |
| u05      | 1         |
+----------+-----------+

2. 按频次区间分组统计用户数

执行SQL

select freq_range,
       count(1) as user_cnt
from (
    select user_id,
           order_cnt,
           case
               when order_cnt = 1 then '1次（新用户）'
               when order_cnt between 2 and 3 then '2-3次（普通用户）'
               when order_cnt between 4 and 6 then '4-6次（活跃用户）'
               else '7次以上（忠实用户）'
           end as freq_range
    from (
        select user_id,
               count(1) as order_cnt
        from t8_user_orders
        group by user_id
    ) t1
) t2
group by freq_range
order by field(freq_range, '1次（新用户）', '2-3次（普通用户）', '4-6次（活跃用户）', '7次以上（忠实用户）')

执行结果

+-----------------------+----------+
| freq_range            | user_cnt |
+-----------------------+----------+
| 1次（新用户）          | 3        |
| 2-3次（普通用户）       | 1        |
| 7次以上（忠实用户）     | 1        |
+-----------------------+----------+

新用户占比最高（3/5=60%），忠实用户仅1人（u01），说明用户留存有待提升。

五、常见坑点

坑1：CASE WHEN条件的顺序 — 从上到下匹配，先命中先生效。写区间时从低到高或从高到低，避免条件重叠。

坑2：占比计算的分母 — SUM() OVER() 不带 PARTITION BY 才是全局总量，加了PARTITION BY分母变成组内总量。

六、举一反三

1. 动态分段：阈值存在配置表中用JOIN取值，灵活调整分段策略

2. 累计分布CDF：SUM(cnt) OVER (ORDER BY bucket) 算累计占比，画帕累托图

3. 按时间维度对比：按天/周/月分区，观察分布的时序变化

七、知识点总结

考点	说明
CASE WHEN 条件聚合	灵活实现分段统计、条件计数、标签化处理
GROUP BY + 聚合函数	分组聚合是数据分析的基础，配合HAVING筛选分组结果

八、建表语句和数据插入

点击展开 DDL & DML

-- 建表语句
CREATE TABLE t8_user_orders (
    order_id   string COMMENT '订单ID',
    user_id    string COMMENT '用户ID',
    order_time string COMMENT '下单时间'
) COMMENT '用户订单表';

-- 数据插入
INSERT INTO t8_user_orders VALUES
('O001', 'u01', '2023-03-01 10:00:00'),
('O002', 'u02', '2023-03-01 10:30:00'),
('O003', 'u01', '2023-03-01 11:00:00'),
('O004', 'u03', '2023-03-01 11:30:00'),
('O005', 'u01', '2023-03-02 10:00:00'),
('O006', 'u02', '2023-03-02 10:30:00'),
('O007', 'u01', '2023-03-03 09:00:00'),
('O008', 'u04', '2023-03-03 10:00:00'),
('O009', 'u01', '2023-03-04 11:00:00'),
('O010', 'u01', '2023-03-05 12:00:00'),
('O011', 'u05', '2023-03-05 12:30:00'),
('O012', 'u01', '2023-03-06 12:00:00');

📱关注公众号

「数据仓库技术」文章同步更新，不错过每一篇干货

💬加群交流

备注「数据仓库技术」加入社群，每日一道大厂SQL真题