1934. 确认率

【SQL面试题解析】计算用户的确认率（confirmation rate）

在日常数据分析和数据处理场景中，我们经常需要对用户行为数据进行汇总与分析。本篇博客将介绍一道经典的 SQL 面试题，目标是统计用户的确认率。我们将通过详细的题目解析、解题思路、SQL 实现、示例说明以及方法对比，帮助大家掌握这类问题的常规处理方式。

---

一、题目描述

给定两个表：Signups 和 Confirmations。

表结构如下：

Signups 表（用户注册信息）

Column Name	Type	Description
user_id	int	用户ID，主键
time_stamp	datetime	注册时间

Confirmations 表（用户确认操作信息）

Column Name	Type	Description
user_id	int	用户ID，外键，引用 Signups 表
time_stamp	datetime	发起确认请求的时间
action	ENUM	‘confirmed’ 或 ‘timeout’，确认操作的结果

每条 Confirmations 表中的记录表示一个用户的确认请求，该请求可能被成功确认（confirmed）或者超时失效（timeout）。

任务：

对每个用户，计算其确认率（confirmation rate），即：

确认率 = 确认成功的次数 / 确认请求的总次数

注意事项：

• 如果某个用户没有任何确认请求，其确认率应为 0.00。
• 输出保留两位小数。
• 结果中每个用户都必须出现，即使没有任何确认行为。

---

二、解题思路分析

这个问题的核心在于：

1. 汇总统计用户的确认请求记录；
2. **处理空值（NULL）**情况（即用户没有确认请求时如何处理）；
3. **保留精度（ROUND）**以及避免除以0的错误；
4. 左连接保证所有用户都有记录，包括没有确认行为的用户。

关键点在于正确使用：

• LEFT JOIN 保证每个用户都被统计；
• COUNT(...)、SUM(...) 分别用于总次数与确认次数；
• NULLIF 和 COALESCE 保证不会出现除以 0 或 NULL 值的结果；
• ROUND(..., 2) 保留两位小数。

---

三、SQL 解法

# Write your MySQL query statement below
SELECT 
    s.user_id,
    COALESCE(
        ROUND(
            SUM(CASE WHEN c.action = 'confirmed' THEN 1 ELSE 0 END) * 1.0 
            / NULLIF(COUNT(c.action), 0),
        2), 
    0.00) AS confirmation_rate
FROM Signups s
LEFT JOIN Confirmations c ON s.user_id = c.user_id
GROUP BY s.user_id;

说明：

• LEFT JOIN 保证 Signups 中的每个用户都能出现在最终结果中。
• SUM(CASE WHEN action = 'confirmed' THEN 1 ELSE 0 END) 用于计算确认成功的次数。
• COUNT(c.action) 用于统计总请求次数。
• NULLIF(..., 0) 防止除以 0 导致错误。
• COALESCE(..., 0.00) 用于处理 NULL，将没有请求记录的用户的确认率设为 0。
• ROUND(..., 2) 用于四舍五入保留两位小数。

---

四、示例说明

输入数据：

Signups 表：

user_id	time_stamp
3	2020-03-21 10:16:13
7	2020-01-04 13:57:59
2	2020-07-29 23:09:44
6	2020-12-09 10:39:37

Confirmations 表：

user_id	time_stamp	action
3	2021-01-06 03:30:46	timeout
3	2021-07-14 14:00:00	timeout
7	2021-06-12 11:57:29	confirmed
7	2021-06-13 12:58:28	confirmed
7	2021-06-14 13:59:27	confirmed
2	2021-01-22 00:00:00	confirmed
2	2021-02-28 23:59:59	timeout

期望输出：

user_id	confirmation_rate
6	0.00
3	0.00
7	1.00
2	0.50

说明：

• 用户6无任何请求，确认率为 0.00。
• 用户3请求了两次，但均失败，确认率为 0.00。
• 用户7共3次请求，全部成功，确认率为 1.00。
• 用户2有2次请求，其中1次成功，确认率为 0.50。

---

五、对比分析与扩展

✅ 本解法优点：

• 语义清晰，结构规范；
• 完整处理所有边界情况（无确认请求、除零）；
• 可扩展性强（比如换算为百分比、统计每天的确认率等）；
• 兼容 MySQL、PostgreSQL 等主流 SQL 数据库。

⚠️ 可能的陷阱：

• 如果忘记使用 LEFT JOIN，就会漏掉没有确认请求的用户；
• 忘记使用 NULLIF(..., 0) 会导致除以 0 报错；
• 忽略 COALESCE 会导致某些结果变成 NULL，不符合题目要求；
• 若忘记 ROUND(..., 2)，可能结果精度过高，无法与题目要求对齐。

---

六、总结

本题是 SQL 中经典的“左连接 + 分组 + 条件统计”组合题，主要考察对：

• 连接操作（特别是 LEFT JOIN）的理解；
• 分组与条件聚合的掌握；
• 空值处理与容错设计的能力；
• SQL 数学表达能力（如除法、保留小数等）的掌握。

这种模式在业务数据分析中极为常见，如计算用户转化率、活动完成率等，非常值得掌握。