携程大数据面试SQL-差旅用户出差频次分析

⚠️ 待修正

一、题目背景

这道题来自携程的数据分析岗面试。携程是在线旅游平台，数据分析师需要从海量业务数据中挖掘洞见。

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

一、题目

携程企业差旅部门需要分析2025年上半年（1月-6月）差旅用户的出差频次分布，以便制定针对性的企业客户服务策略。给定 business_travel 表，记录了每次差旅订单的详细信息。

business_travel 差旅订单表：

order_id	user_id	company	departure_date	return_date	destination	amount
BT001	U001	华为	2025-01-10	2025-01-12	深圳	3200
BT002	U001	华为	2025-02-15	2025-02-18	北京	2800
BT003	U002	阿里巴巴	2025-01-05	2025-01-07	上海	1500
BT004	U003	腾讯	2025-03-01	2025-03-03	北京	2100
BT005	U001	华为	2025-03-20	2025-03-22	广州	1800
BT006	U001	华为	2025-04-10	2025-04-13	成都	3500
BT007	U002	阿里巴巴	2025-04-15	2025-04-17	深圳	2200
BT008	U001	华为	2025-05-08	2025-05-10	上海	2600
BT009	U003	腾讯	2025-05-20	2025-05-22	杭州	1900
BT010	U002	阿里巴巴	2025-06-01	2025-06-03	南京	1600

要求：

统计每个用户的出差总次数、总消费金额、平均单次消费金额
按出差频次分级：高频（≥5次）、中频（3-4次）、低频（1-2次）
统计每个频次等级的用户数量和占比

三、思路分析

本题考察分层聚合统计的能力，核心在于先做用户级聚合，再在用户聚合结果之上做二次分组统计。考察 COUNT、SUM、AVG、CASE WHEN 条件分组，以及子查询/CTE的嵌套使用。

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

解题思路：

第一步：按 user_id 聚合，统计每个用户的出差次数、总金额、平均金额
第二步：在第一步结果基础上，使用 CASE WHEN 按出差次数分级
第三步：按频次等级聚合，计算各等级的用户数及占比
注意：占比计算的分母为总用户数，可使用窗口函数 SUM() OVER() 或子查询

四、逐步推导

步骤1：统计每个用户的出差指标并分级

SELECT
    user_id,
    company,
    travel_count,
    total_amount,
    ROUND(total_amount / travel_count, 2) AS avg_amount,
    CASE
        WHEN travel_count >= 5 THEN '高频（≥5次）'
        WHEN travel_count >= 3 THEN '中频（3-4次）'
        ELSE '低频（1-2次）'
    END AS frequency_level
FROM (
    SELECT
        user_id,
        MAX(company) AS company,
        COUNT(*) AS travel_count,
        SUM(amount) AS total_amount
    FROM business_travel
    WHERE departure_date >= '2025-01-01'
      AND departure_date < '2025-07-01'
    GROUP BY user_id
) t
ORDER BY travel_count DESC;

执行结果：

user_id	company	travel_count	total_amount	avg_amount	frequency_level
U001	华为	5	15300	3060.00	高频（≥5次）
U002	阿里巴巴	3	5300	1766.67	中频（3-4次）
U003	腾讯	2	4000	2000.00	低频（1-2次）

步骤2：统计各频次等级用户数及占比

WITH user_stats AS (
    SELECT
        user_id,
        COUNT(*) AS travel_count,
        SUM(amount) AS total_amount
    FROM business_travel
    WHERE departure_date >= '2025-01-01'
      AND departure_date < '2025-07-01'
    GROUP BY user_id
),
user_level AS (
    SELECT
        user_id,
        travel_count,
        total_amount,
        CASE
            WHEN travel_count >= 5 THEN '高频（≥5次）'
            WHEN travel_count >= 3 THEN '中频（3-4次）'
            ELSE '低频（1-2次）'
        END AS frequency_level
    FROM user_stats
)
SELECT
    frequency_level,
    COUNT(*) AS user_cnt,
    SUM(travel_count) AS total_travel_cnt,
    ROUND(AVG(travel_count), 1) AS avg_travel_cnt,
    CONCAT(ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1), '%') AS user_pct
FROM user_level
GROUP BY frequency_level
ORDER BY MIN(travel_count) DESC;

执行结果：

frequency_level	user_cnt	total_travel_cnt	avg_travel_cnt	user_pct
高频（≥5次）	1	5	5.0	33.3%
中频（3-4次）	1	3	3.0	33.3%
低频（1-2次）	1	2	2.0	33.3%

五、常见坑点

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

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

六、举一反三

增加时间维度对比：按天/周/月分组，观察指标的趋势和季节性波动
增加分组维度：按品类/地区/渠道拆解，发现差异化的业务洞察
设置预警阈值：在WHERE中加阈值判断，自动筛选异常数据

七、知识点总结

考点	说明
SUM() OVER 累积求和	窗口聚合实现滚动累计，配合ORDER BY实现时间轴展开
CASE WHEN 条件聚合	灵活实现分段统计、条件计数、标签化处理
GROUP BY + 聚合函数	分组聚合是数据分析的基础，配合HAVING筛选分组结果
日期函数	DATEDIFF / DATE_ADD / unix_timestamp处理日期运算

八、建表语句和数据插入

点击展开 DDL & DML

CREATE TABLE business_travel (
    order_id VARCHAR(10) PRIMARY KEY,
    user_id VARCHAR(10),
    company VARCHAR(50),
    departure_date DATE,
    return_date DATE,
    destination VARCHAR(20),
    amount DECIMAL(10,2)
);

INSERT INTO business_travel VALUES
('BT001', 'U001', '华为', '2025-01-10', '2025-01-12', '深圳', 3200),
('BT002', 'U001', '华为', '2025-02-15', '2025-02-18', '北京', 2800),
('BT003', 'U002', '阿里巴巴', '2025-01-05', '2025-01-07', '上海', 1500),
('BT004', 'U003', '腾讯', '2025-03-01', '2025-03-03', '北京', 2100),
('BT005', 'U001', '华为', '2025-03-20', '2025-03-22', '广州', 1800),
('BT006', 'U001', '华为', '2025-04-10', '2025-04-13', '成都', 3500),
('BT007', 'U002', '阿里巴巴', '2025-04-15', '2025-04-17', '深圳', 2200),
('BT008', 'U001', '华为', '2025-05-08', '2025-05-10', '上海', 2600),
('BT009', 'U003', '腾讯', '2025-05-20', '2025-05-22', '杭州', 1900),
('BT010', 'U002', '阿里巴巴', '2025-06-01', '2025-06-03', '南京', 1600);

📱关注公众号

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

💬加群交流

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

一、题目背景​

一、题目​

三、思路分析​

四、逐步推导​

五、常见坑点​

六、举一反三​

七、知识点总结​

八、建表语句和数据插入​

你可能还想看