跳到主要内容

SQL 酒店+机票打包购买转化率：交叉销售分析（携程面试题）

一、题目​

携程经常推出"机+酒"打包套餐产品，需要统计浏览过打包套餐页面的用户中，购买打包套餐的转化率，以及未购买打包套餐的用户是否另有单独预订机票或酒店的行为。 给定四张表：t5_user_visit（用户浏览日志）、t5_package_order（打包套餐订单）、t5_flight_order（机票订单）、t5_hotel_order（酒店订单）。

要求：

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

t5_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  |
+-----------+----------+------------+----------------------+

t5_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    |
+-----------+----------+---------------+----------------------+---------+

t5_flight_order 机票订单表：

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

t5_hotel_order 酒店订单表：

+-----------+----------+-------------+----------------------+---------+
| order_id  | user_id  | order_type  |      order_time      | amount  |
+-----------+----------+-------------+----------------------+---------+
| HTL001    | U203     | hotel       | 2025-03-06 10:05:00  | 2200    |
+-----------+----------+-------------+----------------------+---------+

二、思路分析​

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

维度评分
题目难度⭐⭐⭐
题目清晰度⭐⭐⭐⭐
业务常见度⭐⭐⭐⭐⭐
  1. 从 t5_user_visit 中取出浏览过 page_type = 'package' 的独立用户集合；
  2. 用 left join 关联 t5_package_order，标记用户是否购买打包套餐；
  3. 用 left join 关联分散购买记录（union 机票和酒店订单），标记未购买打包套餐的用户是否有分散购买行为；
  4. 注意：一个用户可能多次浏览需去重；分散购买表须先 union 再关联。

三、逐步推导​

1.统计转化率​

以浏览打包页的用户为主表，left join 购买用户，计算转化率。

执行SQL

with visited_users as (
    -- 浏览过打包页面的去重用户
    select distinct user_id
    from t5_user_visit
    where page_type = 'package'
),
package_buyers as (
    -- 购买打包套餐的去重用户
    select distinct user_id
    from t5_package_order
)
select
    count(distinct v.user_id) as visited_users,
    count(distinct p.user_id) as package_buyers,
    round(count(distinct p.user_id) * 1.0 / count(distinct v.user_id), 4) 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               | 0.5000           |
+----------------+-----------------+------------------+
1 row selected (0.607 seconds)(https://www.dwsql.com)

2.分析未购买用户的分散购买行为​

合并机票和酒店订单表，left join 判断未购打包套餐的用户是否有单独预订。

执行SQL

with visited_users as (
    select distinct user_id
    from t5_user_visit
    where page_type = 'package'
),
package_buyers as (
    select distinct user_id
    from t5_package_order
),
separate_orders as (
    -- union合并机票和酒店的去重用户
    select distinct user_id from t5_flight_order
    union
    select distinct user_id from t5_hotel_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     | 未购打包，但有分散购买    |
+----------+----------------+
6 rows selected (1.352 seconds)(https://www.dwsql.com)

四、常见坑点​

坑1：left join 保留所有浏览用户是转化率计算的关键 — 必须以浏览用户为主表，用 left join 关联购买用户表。如果误用 inner join，未购买的用户会被过滤掉，分母缩小导致转化率虚高。

坑2：union 去重与 union all 的区别 — 合并机票和酒店订单时使用 union，会自动去除完全重复的行。虽然子查询中已用 select distinct 按 (user_id, buy_type) 去重，但 union 提供了额外的跨表去重保障。如果省略子查询中的 distinct 而改用 union all，同一用户的多笔同类型订单会产生重复行，导致 left join 时匹配多次。

坑3：多次访问需 distinct user_id — t5_user_visit 表中同一用户可能多次浏览打包套餐页面（如 U201 有 V001 和 V010 两条记录）。统计"浏览过"的用户数时必须 select distinct user_id，否则 count(*) 会将同一用户重复计数，导致分母偏大、转化率偏低。

五、知识点总结​

考点说明
left join + count distinct以主表为基准左关联，count(distinct) 统计唯一用户数，计算转化率时分母必须用 left join 保留全量
union 合并多表将机票和酒店两张订单表合并为统一的分散购买记录，union 自动去重确保数据干净
case when 行为分类根据是否购买打包套餐、是否有分散购买，将用户分为三类行为标签
CTE 分层使用 with ... as 将浏览用户、购买用户、分散订单分别定义为独立 CTE，逻辑清晰易维护

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

点击展开 DDL & DML
-- 用户浏览日志表
create table t5_user_visit (
    visit_id string comment '访问ID',
    user_id string comment '用户ID',
    page_type string comment '页面类型',
    visit_time string comment '访问时间'
) comment '用户浏览日志表';

insert into t5_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 t5_package_order (
    order_id string comment '订单ID',
    user_id string comment '用户ID',
    package_name string comment '套餐名称',
    order_time string comment '下单时间',
    amount int comment '订单金额'
) comment '打包套餐订单表';

insert into t5_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 t5_flight_order (
    order_id string comment '订单ID',
    user_id string comment '用户ID',
    order_type string comment '订单类型',
    order_time string comment '下单时间',
    amount int comment '订单金额'
) comment '机票订单表';

insert into t5_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 t5_hotel_order (
    order_id string comment '订单ID',
    user_id string comment '用户ID',
    order_type string comment '订单类型',
    order_time string comment '下单时间',
    amount int comment '订单金额'
) comment '酒店订单表';

insert into t5_hotel_order values
('HTL001', 'U203', 'hotel', '2025-03-06 10:05:00', 2200);
📱关注公众号

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

微信公众号二维码
💬加群交流

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

交流微信二维码

你可能还想看

专题
JOIN 可视化工具
相关题目
01. 酒店入住率02. 差旅用户出差频次03. 机票价格波动趋势