小米大数据面试SQL-用户日活/周活/月活DAU/WAU/MAU

⚠️ 待修正

一、题目背景

这道题来自小米的数据分析岗面试。小米是消费电子和IoT，数据分析师需要从海量业务数据中挖掘洞见。

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

一、题目

基于小米应用商店的日活日志，计算2025年6月的DAU（日活跃用户数）、WAU（周活跃用户数）和MAU（月活跃用户数），并分析DAU/MAU比值（用户粘性指标）。

假设有日活表 daily_active：

+------------+---------+
| active_date| user_id |
+------------+---------+
| 2025-06-01 | u01     |
| 2025-06-01 | u02     |
| 2025-06-01 | u03     |
| 2025-06-02 | u01     |
| 2025-06-02 | u04     |
| 2025-06-07 | u01     |
| 2025-06-07 | u02     |
| 2025-06-07 | u05     |
| 2025-06-08 | u01     |
| 2025-06-08 | u03     |
| 2025-06-15 | u01     |
| 2025-06-15 | u06     |
| 2025-06-22 | u02     |
| 2025-06-22 | u04     |
| 2025-06-22 | u05     |
+------------+---------+

三、思路分析

1. DAU：每日去重用户数；
2. WAU：以某一天为基准，过去7天内去重用户数。用 weekofyear 或自定义周标识；
3. MAU：整个6月份的去重用户数；
4. DAU/MAU 比值反映用户粘性，DAU取均值即可。

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

四、逐步推导

1.计算6月整体MAU和日均DAU

执行SQL

select count(distinct user_id) as mau,
       round(count(distinct user_id) / count(distinct active_date), 2) as avg_dau
from daily_active
where active_date between '2025-06-01' and '2025-06-30'

查询结果

+-----+---------+
| mau | avg_dau |
+-----+---------+
| 6   | 1.20    |
+-----+---------+

2.按天计算DAU，并关联MAU计算DAU/MAU比值

执行SQL

select t.active_date,
       t.dau,
       m.mau,
       round(t.dau / m.mau, 4) as dau_mau_ratio
from (
    select active_date,
           count(distinct user_id) as dau
    from daily_active
    where active_date between '2025-06-01' and '2025-06-30'
    group by active_date
) t
cross join (
    select count(distinct user_id) as mau
    from daily_active
    where active_date between '2025-06-01' and '2025-06-30'
) m
order by t.active_date

查询结果

+-------------+-----+-----+----------------+
| active_date | dau | mau | dau_mau_ratio  |
+-------------+-----+-----+----------------+
| 2025-06-01  | 3   | 6   | 0.5000         |
| 2025-06-02  | 2   | 6   | 0.3333         |
| 2025-06-07  | 3   | 6   | 0.5000         |
| 2025-06-08  | 2   | 6   | 0.3333         |
| 2025-06-15  | 2   | 6   | 0.3333         |
| 2025-06-22  | 3   | 6   | 0.5000         |
+-------------+-----+-----+----------------+

3.按周统计WAU（以每周日为基准，取该周活跃用户数）

执行SQL

select concat(substr(active_date, 1, 4), '-W', weekofyear(active_date)) as week_label,
       count(distinct user_id) as wau
from daily_active
where active_date between '2025-06-01' and '2025-06-30'
group by concat(substr(active_date, 1, 4), '-W', weekofyear(active_date))

查询结果

+------------+-----+
| week_label | wau |
+------------+-----+
| 2025-W23   | 5   |
| 2025-W24   | 2   |
| 2025-W25   | 2   |
| 2025-W26   | 3   |
+------------+-----+

五、常见坑点

坑1：NULL值的隐式处理 — 聚合函数跳过NULL，窗口函数不跳过。WHERE需显式过滤或用COALESCE替代。

坑2：数据类型不一致导致JOIN失效 — string vs int隐式转换可能触发全表扫描，性能骤降。

六、举一反三

1. 增加时间维度对比：按天/周/月分组，观察指标的趋势和季节性波动

2. 增加分组维度：按品类/地区/渠道拆解，发现差异化的业务洞察

3. 设置预警阈值：在WHERE中加阈值判断，自动筛选异常数据

七、知识点总结

考点	说明
多表JOIN	LEFT JOIN保留主表所有数据，COALESCE处理NULL
COUNT DISTINCT 去重	统计唯一用户/事件数，避免重复记录干扰聚合结果
GROUP BY + 聚合函数	分组聚合是数据分析的基础，配合HAVING筛选分组结果
日期函数	DATEDIFF / DATE_ADD / unix_timestamp处理日期运算

八、建表语句和数据插入

点击展开 DDL & DML

--建表语句
CREATE TABLE daily_active (
    active_date string COMMENT '活跃日期',
    user_id     string COMMENT '用户ID'
) COMMENT '日活跃用户表';

-- 插入数据
insert into daily_active(active_date, user_id) values
('2025-06-01','u01'),
('2025-06-01','u02'),
('2025-06-01','u03'),
('2025-06-02','u01'),
('2025-06-02','u04'),
('2025-06-07','u01'),
('2025-06-07','u02'),
('2025-06-07','u05'),
('2025-06-08','u01'),
('2025-06-08','u03'),
('2025-06-15','u01'),
('2025-06-15','u06'),
('2025-06-22','u02'),
('2025-06-22','u04'),
('2025-06-22','u05');

📱关注公众号

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

💬加群交流

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