SQL 限量款抽签中签率：参与人数/中签人数（得物面试题）

Q: SQL 限量款抽签中签率：参与人数/中签人数（得物面试题）

得物大数据面试SQL-限量款抽签中签率统计

一、题目

得物平台对热门限量款商品采用抽签方式分配购买资格。请统计每款限量商品的参与人数、中签人数、中签率，以及中签后的购买转化率。

注意：同一用户可能参与多款商品的抽签，同一用户在同一商品的多次参与需去重统计。

给定两张表：t2_lottery_participant（抽签参与记录）和 t2_lottery_winner（中签记录）。

t2_lottery_participant 抽签参与表：

+-----------------+----------+-------------+---------------------+----------------------+-------+
| participant_id  | user_id  | product_id  |    product_name     |      apply_time      | size  |
+-----------------+----------+-------------+---------------------+----------------------+-------+
| LP001           | U301     | P2001       | AJ1 High 'Chicago'  | 2025-02-01 10:00:00  | 42    |
| LP002           | U302     | P2001       | AJ1 High 'Chicago'  | 2025-02-01 10:05:00  | 43    |
| LP003           | U303     | P2001       | AJ1 High 'Chicago'  | 2025-02-01 10:10:00  | 41    |
| LP004           | U304     | P2001       | AJ1 High 'Chicago'  | 2025-02-01 10:15:00  | 42    |
| LP005           | U305     | P2001       | AJ1 High 'Chicago'  | 2025-02-01 10:20:00  | 44    |
| LP006           | U301     | P2002       | Dunk SB 'Panda'     | 2025-02-05 09:00:00  | 42    |
| LP007           | U306     | P2002       | Dunk SB 'Panda'     | 2025-02-05 09:05:00  | 43    |
| LP008           | U307     | P2002       | Dunk SB 'Panda'     | 2025-02-05 09:10:00  | 40    |
| LP009           | U308     | P2002       | Dunk SB 'Panda'     | 2025-02-05 09:15:00  | 41    |
| LP010           | U305     | P2002       | Dunk SB 'Panda'     | 2025-02-05 09:20:00  | 42    |
| LP011           | U309     | P2002       | Dunk SB 'Panda'     | 2025-02-05 09:25:00  | 43    |
| LP012           | U310     | P2003       | Yeezy 350 'Zebra'   | 2025-02-08 11:00:00  | 42    |
| LP013           | U301     | P2003       | Yeezy 350 'Zebra'   | 2025-02-08 11:05:00  | 42    |
| LP014           | U311     | P2003       | Yeezy 350 'Zebra'   | 2025-02-08 11:10:00  | 43    |
+-----------------+----------+-------------+---------------------+----------------------+-------+

t2_lottery_winner 中签记录表：

+------------+----------+-------------+----------------------+------------------+
| winner_id  | user_id  | product_id  |       win_time       | purchase_status  |
+------------+----------+-------------+----------------------+------------------+
| W001       | U301     | P2001       | 2025-02-02 08:00:00  | purchased        |
| W002       | U303     | P2001       | 2025-02-02 08:00:00  | expired          |
| W003       | U306     | P2002       | 2025-02-06 08:00:00  | purchased        |
| W004       | U308     | P2002       | 2025-02-06 08:00:00  | purchased        |
| W005       | U301     | P2003       | 2025-02-09 08:00:00  | purchased        |
+------------+----------+-------------+----------------------+------------------+

要求：

统计每款商品的参与人数、中签人数和中签率（中签率 = 中签人数 / 参与人数）
计算中签后的购买转化率（购买转化率 = 已购买人数 / 中签人数）

二、思路分析

本题考察多表关联和漏斗转化分析，核心是不同粒度的用户去重统计。

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

从 t2_lottery_participant 表按 product_id 分组，使用 count(distinct user_id) 统计每款商品的参与人数（同一用户多次参与同一商品需去重）
从 t2_lottery_winner 表按 product_id 分组，统计每款商品的中签人数和已购买人数
使用 left join 关联两张表，确保无人中签的商品也能展示（中签人数为 0）
中签率 = 中签人数 / 参与人数；购买转化率 = 已购买人数 / 中签人数，分母使用 nullif 防除零

三、逐步推导

1.统计各商品的中签率与购买转化率

按商品统计参与人数、中签人数、中签率及购买转化率。

执行SQL

with participant_cnt as (
    select
        product_id,
        max(product_name) as product_name,
        count(distinct user_id) as participant_users
    from t2_lottery_participant
    group by product_id
),
winner_cnt as (
    select
        product_id,
        count(distinct user_id) as winner_users,
        sum(case when purchase_status = 'purchased' then 1 else 0 end) as purchased_cnt
    from t2_lottery_winner
    group by product_id
)
select
    p.product_id,
    p.product_name,
    p.participant_users,
    coalesce(w.winner_users, 0) as winner_users,
    round(coalesce(w.winner_users, 0) * 100.0 / p.participant_users, 2) as win_rate,
    coalesce(w.purchased_cnt, 0) as purchased_cnt,
    round(
        coalesce(w.purchased_cnt, 0) * 100.0 / nullif(w.winner_users, 0), 2
    ) as purchase_conversion_rate
from participant_cnt p
left join winner_cnt w on p.product_id = w.product_id
order by p.participant_users desc;

执行结果

+-------------+---------------------+--------------------+---------------+-----------+----------------+---------------------------+
| product_id  |    product_name     | participant_users  | winner_users  | win_rate  | purchased_cnt  | purchase_conversion_rate  |
+-------------+---------------------+--------------------+---------------+-----------+----------------+---------------------------+
| P2002       | Dunk SB 'Panda'     | 6                  | 2             | 33.33     | 2              | 100.00                    |
| P2001       | AJ1 High 'Chicago'  | 5                  | 2             | 40.00     | 1              | 50.00                     |
| P2003       | Yeezy 350 'Zebra'   | 3                  | 1             | 33.33     | 1              | 100.00                    |
+-------------+---------------------+--------------------+---------------+-----------+----------------+---------------------------+
3 rows selected (2.047 seconds)

四、常见坑点

坑1：LEFT JOIN 确保无人中签的商品也能显示 — 并非每款商品都有人中签，如果使用 inner join，无人中签的商品会被直接过滤掉，导致参与人数统计不完整。应使用 left join + coalesce 将中签人数补为 0。

坑2：count(distinct user_id) vs count(*) — 同一用户可能在同一商品的抽签中多次参与（如更换尺码），统计参与人数时必须使用 count(distinct user_id) 去重，若用 count(*) 会高估参与规模。

坑3：购买转化率的分母需 nullif 防除零 — 当某款商品中签人数为 0 时，直接做除法 purchased_cnt / winner_users 会导致除零错误。使用 nullif(winner_users, 0) 将 0 转为 null，避免报错，结果为 null 表示转化率不适用。

五、知识点总结

+---------------------+--------------------------------------------------------+
| 考点                | 说明                                                   |
+---------------------+--------------------------------------------------------+
| COUNT DISTINCT 去重 | 统计唯一用户数，避免同一用户多条记录干扰聚合结果       |
| LEFT JOIN + COALESCE | 保留主表所有数据，确保无匹配记录的商品不会丢失        |
| CASE WHEN 条件聚合  | 在聚合函数内按条件计数，灵活实现多维度分类统计         |
| NULLIF 防除零       | 将分母中的 0 转为 null，避免除法运算报错               |
+---------------------+--------------------------------------------------------+

六、建表语句

点击展开 DDL & DML

create table t2_lottery_participant (
    participant_id string,
    user_id string,
    product_id string,
    product_name string,
    apply_time string,
    size int
);

create table t2_lottery_winner (
    winner_id string,
    user_id string,
    product_id string,
    win_time string,
    purchase_status string
);

insert into t2_lottery_participant values
('LP001', 'U301', 'P2001', "AJ1 High 'Chicago'", '2025-02-01 10:00:00', 42),
('LP002', 'U302', 'P2001', "AJ1 High 'Chicago'", '2025-02-01 10:05:00', 43),
('LP003', 'U303', 'P2001', "AJ1 High 'Chicago'", '2025-02-01 10:10:00', 41),
('LP004', 'U304', 'P2001', "AJ1 High 'Chicago'", '2025-02-01 10:15:00', 42),
('LP005', 'U305', 'P2001', "AJ1 High 'Chicago'", '2025-02-01 10:20:00', 44),
('LP006', 'U301', 'P2002', "Dunk SB 'Panda'", '2025-02-05 09:00:00', 42),
('LP007', 'U306', 'P2002', "Dunk SB 'Panda'", '2025-02-05 09:05:00', 43),
('LP008', 'U307', 'P2002', "Dunk SB 'Panda'", '2025-02-05 09:10:00', 40),
('LP009', 'U308', 'P2002', "Dunk SB 'Panda'", '2025-02-05 09:15:00', 41),
('LP010', 'U305', 'P2002', "Dunk SB 'Panda'", '2025-02-05 09:20:00', 42),
('LP011', 'U309', 'P2002', "Dunk SB 'Panda'", '2025-02-05 09:25:00', 43),
('LP012', 'U310', 'P2003', "Yeezy 350 'Zebra'", '2025-02-08 11:00:00', 42),
('LP013', 'U301', 'P2003', "Yeezy 350 'Zebra'", '2025-02-08 11:05:00', 42),
('LP014', 'U311', 'P2003', "Yeezy 350 'Zebra'", '2025-02-08 11:10:00', 43);

insert into t2_lottery_winner values
('W001', 'U301', 'P2001', '2025-02-02 08:00:00', 'purchased'),
('W002', 'U303', 'P2001', '2025-02-02 08:00:00', 'expired'),
('W003', 'U306', 'P2002', '2025-02-06 08:00:00', 'purchased'),
('W004', 'U308', 'P2002', '2025-02-06 08:00:00', 'purchased'),
('W005', 'U301', 'P2003', '2025-02-09 08:00:00', 'purchased');

📱关注公众号

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

💬加群交流

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

一、题目​

二、思路分析​

三、逐步推导​

1.统计各商品的中签率与购买转化率​

四、常见坑点​

五、知识点总结​

六、建表语句​

你可能还想看