蚂蚁集团大数据面试SQL-花呗账单分期率统计
⚠️ 待修正
一、题目背景
这道题来自蚂蚁集团花呗事业部的数据分析岗面试。花呗账单分期是花呗的核心收入来源之一——用户将大额消费分摊到多个月还款，蚂蚁从中赚取分期手续费。分期率（选择分期的账单占比）是衡量分期产品健康度的北极星指标。
业务场景：每月初运营团队会看各月账单的分期率变化，如果分期率下滑，可能需要加大分期免息活动的推广力度。这道SQL就是分期率看板背后的查询逻辑。
二、题目
现有一张花呗账单表 t2_huabei_bill，记录了用户每月的账单信息。请统计每月的账单总数、分期账单数以及分期率。
花呗账单表 t2_huabei_bill：
+----------+----------+-------------+--------+----------------+
| bill_id | user_id | bill_month | amount | is_installment |
+----------+----------+-------------+--------+----------------+
| B001 | u01 | 2023-01 | 5000 | 1 |
| B002 | u02 | 2023-01 | 3000 | 0 |
| B003 | u03 | 2023-01 | 8000 | 1 |
| B004 | u01 | 2023-01 | 2000 | 0 |
| B005 | u04 | 2023-02 | 12000 | 1 |
| B006 | u01 | 2023-02 | 6000 | 1 |
| B007 | u02 | 2023-02 | 4500 | 0 |
| B008 | u03 | 2023-02 | 7000 | 0 |
| B009 | u05 | 2023-02 | 3500 | 1 |
| B010 | u02 | 2023-03 | 9000 | 1 |
| B011 | u04 | 2023-03 | 5500 | 0 |
| B012 | u01 | 2023-03 | 4000 | 0 |
+----------+----------+-------------+--------+----------------+
is_installment: 1-已分期, 0-未分期
三、思路分析
本题考察按月聚合统计和条件聚合：
- 按月分组：
GROUP BY bill_month按账单月份聚合 - 条件计数：
SUM(CASE WHEN is_installment = 1 THEN 1 ELSE 0 END)统计分期账单数 - 分期率：分期账单数 / 总账单数
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️⭐️ |
四、逐步推导
步骤1：按月统计总账单数和分期数
执行SQL
select bill_month,
count(1) as total_bill_cnt,
sum(case when is_installment = 1 then 1 else 0 end) as installment_cnt
from t2_huabei_bill
group by bill_month
order by bill_month
执行结果
+-------------+----------------+------------------+
| bill_month | total_bill_cnt | installment_cnt |
+-------------+----------------+------------------+
| 2023-01 | 4 | 2 |
| 2023-02 | 5 | 3 |
| 2023-03 | 3 | 1 |
+-------------+----------------+------------------+
步骤2：计算分期率
执行SQL
select bill_month,
total_bill_cnt,
installment_cnt,
round(installment_cnt / total_bill_cnt, 4) as installment_rate
from (
select bill_month,
count(1) as total_bill_cnt,
sum(case when is_installment = 1 then 1 else 0 end) as installment_cnt
from t2_huabei_bill
group by bill_month
) t
order by bill_month
执行结果
+-------------+----------------+------------------+-------------------+
| bill_month | total_bill_cnt | installment_cnt | installment_rate |
+-------------+----------------+------------------+-------------------+
| 2023-01 | 4 | 2 | 0.5000 |
| 2023-02 | 5 | 3 | 0.6000 |
| 2023-03 | 3 | 1 | 0.3333 |
+-------------+----------------+------------------+-------------------+
2月分期率最高（60%），可能与春节消费大额账单集中有关；3月分期率最低（33%）。
五、常见坑点
坑1：SUM + CASE WHEN vs COUNT + WHERE 的区别
SUM(CASE WHEN is_installment = 1 THEN 1 ELSE 0 END) 和 COUNT(CASE WHEN is_installment = 1 THEN 1 END) 结果相同，但前者显式保留0值、后者依赖 NULL 不计数。从可读性角度推荐 SUM+CASE WHEN 方案，意图更明确。
坑2：分期率的分子分母口径
如果一个用户当月有多笔账单，每笔账单独立计算分期。不要用 COUNT(DISTINCT user_id) 做分母——那算的是"分期的用户占比"，不是"分期的账单占比"，两个指标含义不同。
六、举一反三
- 按金额加权分期率：
SUM(CASE WHEN is_installment=1 THEN amount ELSE 0 END) / SUM(amount)看分期金额占比，高额账单的分期意愿通常更强 - 按用户分层：关联用户信用分表，对比不同信用等级用户的分期率差异
- 分期期数分布：如果 is_installment 字段改为 installment_periods（3/6/12/24期），用 CASE WHEN 分组统计各期数占比
- 环比对比：LAG(installment_rate) OVER (ORDER BY bill_month) 看分期率的月度环比变化
七、知识点总结
| 考点 | 说明 |
|---|---|
| SUM + CASE WHEN 条件聚合 | 一行内完成条件计数，比子查询更高效 |
| GROUP BY 按月汇总 | 按账单月份维度聚合所有账单 |
| ROUND 格式化比率 | 保留4位小数，便于展示百分比 |
| 子查询分层 | 先聚合再计算比率，逻辑清晰 |
八、建表语句和数据插入
点击展开 DDL & DML
CREATE TABLE t2_huabei_bill (
bill_id string COMMENT '账单ID',
user_id string COMMENT '用户ID',
bill_month string COMMENT '账单月份',
amount decimal(10,2) COMMENT '账单金额',
is_installment int COMMENT '是否分期：1-是，0-否'
) COMMENT '花呗账单表';
INSERT INTO t2_huabei_bill VALUES
('B001', 'u01', '2023-01', 5000, 1),
('B002', 'u02', '2023-01', 3000, 0),
('B003', 'u03', '2023-01', 8000, 1),
('B004', 'u01', '2023-01', 2000, 0),
('B005', 'u04', '2023-02', 12000, 1),
('B006', 'u01', '2023-02', 6000, 1),
('B007', 'u02', '2023-02', 4500, 0),
('B008', 'u03', '2023-02', 7000, 0),
('B009', 'u05', '2023-02', 3500, 1),
('B010', 'u02', '2023-03', 9000, 1),
('B011', 'u04', '2023-03', 5500, 0),
('B012', 'u01', '2023-03', 4000, 0);
📱关注公众号
「数据仓库技术」文章同步更新，不错过每一篇干货
💬加群交流
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
