携程大数据面试SQL-酒店+机票打包购买转化率

Q: 酒店+机票打包购买转化率

携程大数据面试SQL-酒店机票打包购买转化率

⚠️ 待修正

一、题目背景

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

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

一、题目

携程经常推出"机+酒"打包套餐产品，需要分析浏览了打包套餐页面的用户中有多少最终完成了购买。给定三张表：user_visit（用户浏览日志）、package_order（打包套餐订单）、hotel_order（单独酒店订单）和 flight_order（单独机票订单）。

user_visit 用户浏览日志表：

visit_id	user_id	page_type	visit_time
V001	U201	package	2025-03-01 10:00:00
V002	U202	package	2025-03-02 14:30:00
V003	U203	package	2025-03-03 09:15:00
V004	U204	package	2025-03-03 11:20:00
V005	U201	hotel	2025-03-04 16:00:00
V006	U205	package	2025-03-04 10:00:00
V007	U202	package	2025-03-05 08:45:00
V008	U203	flight	2025-03-05 12:30:00
V009	U206	package	2025-03-06 09:00:00
V010	U201	package	2025-03-06 15:20:00

package_order 打包套餐订单表：

order_id	user_id	package_name	order_time	amount
PKG001	U201	三亚5天4晚机+酒	2025-03-02 20:00:00	5200
PKG002	U202	丽江3天2晚机+酒	2025-03-06 10:00:00	3800
PKG003	U204	成都4天3晚机+酒	2025-03-10 18:30:00	4100

分散购买记录（用户单独购买机票和酒店，而非打包）：

order_id	user_id	order_type	order_time	amount
FLT001	U203	flight	2025-03-06 10:00:00	1800
HTL001	U203	hotel	2025-03-06 10:05:00	2200
FLT002	U205	flight	2025-03-10 14:00:00	1500
FLT003	U206	flight	2025-03-12 09:00:00	2000

要求：

统计浏览过打包套餐页面的总独立用户数
统计这些用户中最终购买打包套餐的用户数
计算打包购买的转化率（购买打包套餐的用户数 / 浏览打包套餐的用户数）
分析：浏览了打包套餐但未购买打包套餐的用户，他们是否有单独购买机票或酒店？

三、思路分析

本题考察多表关联分析能力，核心是转化漏斗的计算。涉及 COUNT DISTINCT、多表 LEFT JOIN、以及转化率的分母定义。难点在于理清"浏览打包页-购买打包套餐-分散购买"三者之间的关系。

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

解题思路：

先从 user_visit 中取出浏览过 page_type = 'package' 的独立用户集合
用 LEFT JOIN 关联 package_order，标记用户是否购买打包套餐
用 LEFT JOIN 关联分散购买记录（需要 UNION 机票和酒店订单），标记未购买打包套餐的用户是否有分散购买行为
计算各层级的用户数和转化率
注意：一个用户可能多次浏览，需要去重；分散购买表需要先 UNION 再关联

四、逐步推导

步骤1：统计浏览和购买打包套餐的用户数及转化率

WITH visited_users AS (
    SELECT DISTINCT user_id
    FROM user_visit
    WHERE page_type = 'package'
),
package_buyers AS (
    SELECT DISTINCT user_id
    FROM package_order
)
SELECT
    COUNT(DISTINCT v.user_id) AS visited_users,
    COUNT(DISTINCT p.user_id) AS package_buyers,
    CONCAT(
        ROUND(
            COUNT(DISTINCT p.user_id) * 100.0 / COUNT(DISTINCT v.user_id), 2
        ), '%'
    ) AS conversion_rate
FROM visited_users v
LEFT JOIN package_buyers p ON v.user_id = p.user_id;

执行结果：

visited_users	package_buyers	conversion_rate
6	3	50.00%

步骤2：分析未购买打包套餐的用户是否有分散购买行为

WITH visited_users AS (
    SELECT DISTINCT user_id
    FROM user_visit
    WHERE page_type = 'package'
),
package_buyers AS (
    SELECT DISTINCT user_id
    FROM package_order
),
separate_orders AS (
    SELECT DISTINCT user_id, 'flight' AS buy_type FROM hotel_order
    UNION
    SELECT DISTINCT user_id, 'hotel' AS buy_type FROM flight_order
)
SELECT
    v.user_id,
    CASE WHEN p.user_id IS NOT NULL THEN '已购买打包套餐'
         WHEN s.user_id IS NOT NULL THEN '未购打包，但有分散购买'
         ELSE '未购打包，无任何购买'
    END AS user_behavior
FROM visited_users v
LEFT JOIN package_buyers p ON v.user_id = p.user_id
LEFT JOIN separate_orders s ON v.user_id = s.user_id
ORDER BY v.user_id;

执行结果：

user_id	user_behavior
U201	已购买打包套餐
U202	已购买打包套餐
U203	未购打包，但有分散购买
U204	已购买打包套餐
U205	未购打包，但有分散购买
U206	未购打包，但有分散购买

五、常见坑点

坑1：各步骤COUNT DISTINCT口径需一致 — 如果时间窗口不同，转化率可能 >100%，出现诡异数据。

坑2：LEFT JOIN链过长导致性能爆炸 — 3-4层嵌套在大数据量下严重膨胀，可用 UNION ALL + CASE WHEN 替代。

六、举一反三

按骑手/站点/城市维度下钻：定位问题来源，是某个骑手还是某个区域的延迟率偏高
按延迟程度分级：CASE WHEN 分为轻微/中等/严重三级，关注严重延迟的占比
时段分析：按小时统计，识别午晚高峰时段的配送压力变化

七、知识点总结

考点	说明
CASE WHEN 条件聚合	灵活实现分段统计、条件计数、标签化处理
多表JOIN	LEFT JOIN保留主表所有数据，COALESCE处理NULL
COUNT DISTINCT 去重	统计唯一用户/事件数，避免重复记录干扰聚合结果
日期函数	DATEDIFF / DATE_ADD / unix_timestamp处理日期运算

八、建表语句和数据插入

点击展开 DDL & DML

-- 用户浏览日志表
CREATE TABLE user_visit (
    visit_id VARCHAR(10) PRIMARY KEY,
    user_id VARCHAR(10),
    page_type VARCHAR(20),
    visit_time DATETIME
);

INSERT INTO user_visit VALUES
('V001', 'U201', 'package', '2025-03-01 10:00:00'),
('V002', 'U202', 'package', '2025-03-02 14:30:00'),
('V003', 'U203', 'package', '2025-03-03 09:15:00'),
('V004', 'U204', 'package', '2025-03-03 11:20:00'),
('V005', 'U201', 'hotel', '2025-03-04 16:00:00'),
('V006', 'U205', 'package', '2025-03-04 10:00:00'),
('V007', 'U202', 'package', '2025-03-05 08:45:00'),
('V008', 'U203', 'flight', '2025-03-05 12:30:00'),
('V009', 'U206', 'package', '2025-03-06 09:00:00'),
('V010', 'U201', 'package', '2025-03-06 15:20:00');

-- 打包套餐订单表
CREATE TABLE package_order (
    order_id VARCHAR(10) PRIMARY KEY,
    user_id VARCHAR(10),
    package_name VARCHAR(50),
    order_time DATETIME,
    amount DECIMAL(10,2)
);

INSERT INTO package_order VALUES
('PKG001', 'U201', '三亚5天4晚机+酒', '2025-03-02 20:00:00', 5200),
('PKG002', 'U202', '丽江3天2晚机+酒', '2025-03-06 10:00:00', 3800),
('PKG003', 'U204', '成都4天3晚机+酒', '2025-03-10 18:30:00', 4100);

-- 机票订单表
CREATE TABLE flight_order (
    order_id VARCHAR(10) PRIMARY KEY,
    user_id VARCHAR(10),
    order_type VARCHAR(20),
    order_time DATETIME,
    amount DECIMAL(10,2)
);

INSERT INTO flight_order VALUES
('FLT001', 'U203', 'flight', '2025-03-06 10:00:00', 1800),
('FLT002', 'U205', 'flight', '2025-03-10 14:00:00', 1500),
('FLT003', 'U206', 'flight', '2025-03-12 09:00:00', 2000);

-- 酒店订单表
CREATE TABLE hotel_order (
    order_id VARCHAR(10) PRIMARY KEY,
    user_id VARCHAR(10),
    order_type VARCHAR(20),
    order_time DATETIME,
    amount DECIMAL(10,2)
);

INSERT INTO hotel_order VALUES
('HTL001', 'U203', 'hotel', '2025-03-06 10:05:00', 2200);

📱关注公众号

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

💬加群交流

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

一、题目背景​

一、题目​

三、思路分析​

四、逐步推导​

五、常见坑点​

六、举一反三​

七、知识点总结​

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

你可能还想看