理想汽车大数据面试SQL-OTA升级完成率

⚠️ 待修正

一、题目背景

这道题来自理想汽车的数据分析岗面试。理想汽车是新能源车企，数据分析师需要从海量业务数据中挖掘洞见。

业务场景：车联网相关需求在日常工作中频繁出现，这类型的SQL题是面试高频考点。

一、题目

现有一张 OTA 升级记录表 t1_ota_record，记录了每辆车每次 OTA 升级的状态信息。请统计各车型的 OTA 升级完成率（升级成功数 / 总升级发起数），按完成率降序排列。

OTA升级记录表 t1_ota_record：

+----------+--------+------------+------------+
| car_id   | model  | ota_version| status     |
+----------+--------+------------+------------+
| C001     | L7     | v4.5.0     | success    |
| C002     | L9     | v4.5.0     | success    |
| C003     | L8     | v4.5.0     | success    |
| C004     | L7     | v4.5.0     | failed     |
| C005     | L9     | v4.5.0     | success    |
| C006     | L7     | v4.5.0     | success    |
| C007     | L8     | v4.5.0     | downloading|
| C008     | L9     | v4.5.0     | failed     |
| C009     | L7     | v4.5.0     | success    |
| C010     | L8     | v4.5.0     | success    |
+----------+--------+------------+------------+

三、思路分析

本题考察条件聚合和分组统计。核心在于正确区分"已发起"和"已成功"的升级记录。

解题步骤：

1. 按 model 分组统计总升级次数和成功次数；
2. 使用 COUNT(1) 统计总数，SUM(CASE WHEN status='success' THEN 1 ELSE 0 END) 统计成功数；
3. 计算完成率 = 成功数 / 总数，按完成率降序。

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

四、逐步推导

1. 按车型统计升级总数和成功数

执行SQL

select model,
       count(1)                                              as total_cnt,
       sum(case when status = 'success' then 1 else 0 end)   as success_cnt
from t1_ota_record
group by model

执行结果

+--------+-----------+-------------+
| model  | total_cnt | success_cnt |
+--------+-----------+-------------+
| L7     | 4         | 3           |
| L8     | 3         | 2           |
| L9     | 3         | 2           |
+--------+-----------+-------------+

2. 计算完成率并排序

执行SQL

select model,
       count(1)                                                   as total_cnt,
       sum(case when status = 'success' then 1 else 0 end)        as success_cnt,
       round(sum(case when status = 'success' then 1 else 0 end) / count(1), 4) as completion_rate
from t1_ota_record
group by model
order by completion_rate desc

执行结果

+--------+-----------+-------------+-----------------+
| model  | total_cnt | success_cnt | completion_rate |
+--------+-----------+-------------+-----------------+
| L7     | 4         | 3           | 0.7500          |
| L8     | 3         | 2           | 0.6667          |
| L9     | 3         | 2           | 0.6667          |
+--------+-----------+-------------+-----------------+

五、常见坑点

坑1：分子分母的业务定义 — 明确分子是"延迟订单数"还是"延迟用户数"，分母对应"总订单数"还是"总用户数"，口径不同结果差异大。

坑2：时间差计算的单位 — unix_timestamp 返回秒，除以60才是分钟。忘记单位转换会导致判断完全错误。

六、举一反三

1. 按骑手/站点/城市维度下钻：定位问题来源，是某个骑手还是某个区域的延迟率偏高

2. 按延迟程度分级：CASE WHEN 分为轻微/中等/严重三级，关注严重延迟的占比

3. 时段分析：按小时统计，识别午晚高峰时段的配送压力变化

七、知识点总结

考点	说明
CASE WHEN 条件聚合	灵活实现分段统计、条件计数、标签化处理
GROUP BY + 聚合函数	分组聚合是数据分析的基础，配合HAVING筛选分组结果

八、建表语句和数据插入

点击展开 DDL & DML

-- 建表语句
CREATE TABLE t1_ota_record (
    car_id      string COMMENT '车辆ID',
    model       string COMMENT '车型',
    ota_version string COMMENT 'OTA版本号',
    status      string COMMENT '升级状态(success/failed/downloading)'
) COMMENT 'OTA升级记录表';

-- 数据插入
INSERT INTO t1_ota_record VALUES
('C001', 'L7', 'v4.5.0', 'success'),
('C002', 'L9', 'v4.5.0', 'success'),
('C003', 'L8', 'v4.5.0', 'success'),
('C004', 'L7', 'v4.5.0', 'failed'),
('C005', 'L9', 'v4.5.0', 'success'),
('C006', 'L7', 'v4.5.0', 'success'),
('C007', 'L8', 'v4.5.0', 'downloading'),
('C008', 'L9', 'v4.5.0', 'failed'),
('C009', 'L7', 'v4.5.0', 'success'),
('C010', 'L8', 'v4.5.0', 'success');

📱关注公众号

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

💬加群交流

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