跳到主要内容

SQL DAU/WAU/MAU计算：日活/周活/月活三种口径（小米面试题）

一、题目​

基于小米应用商店的用户访问日志 t3_user_access_log，计算2025年6月的以下指标：

  • DAU（Daily Active Users）：每日去重用户数
  • WAU（Weekly Active Users）：每周去重用户数（按自然周 weekofyear 划分）
  • MAU（Monthly Active Users）：全月去重用户数
  • DAU/MAU 比值（用户粘性指标）：日均DAU ÷ MAU

假设有用户访问日志表 t3_user_access_log，记录每次用户访问的时间戳，同一用户一天内可能多次访问：

+---------+-------------------+
| user_id |    access_time    |
+---------+-------------------+
| u01     | 2025-06-01 08:00  |
| u01     | 2025-06-01 18:30  |
| u02     | 2025-06-01 09:15  |
| u03     | 2025-06-01 10:00  |
| u01     | 2025-06-02 08:00  |
| u04     | 2025-06-02 10:30  |
| u04     | 2025-06-02 14:00  |
| u01     | 2025-06-07 08:00  |
| u02     | 2025-06-07 10:00  |
| u02     | 2025-06-07 19:00  |
| u05     | 2025-06-07 11:00  |
| u01     | 2025-06-08 08:00  |
| u01     | 2025-06-08 20:00  |
| u03     | 2025-06-08 09:00  |
| u01     | 2025-06-15 08:00  |
| u06     | 2025-06-15 10:00  |
| u02     | 2025-06-22 08:00  |
| u04     | 2025-06-22 11:00  |
| u05     | 2025-06-22 14:00  |
| u05     | 2025-06-22 20:30  |
+---------+-------------------+

二、思路分析​

  1. 原始数据是访问时间戳，需要先用 substr(access_time, 1, 10) 提取日期；
  2. DAU：按日期分组，count(distinct user_id) 去重得到每日活跃用户数；
  3. WAU：按 weekofyear 分组，去重统计每周活跃用户数；
  4. MAU：全月去重用户数，一次 count(distinct user_id) 即可；
  5. DAU/MAU 比值：cross join 将月级 MAU 广播到每日，计算比值。
维度评分
题目难度⭐️⭐️
题目清晰度⭐️⭐️⭐️⭐️
业务常见度⭐️⭐️⭐️⭐️⭐️

三、逐步推导​

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

从访问日志中提取日期，统计全月去重用户数（MAU）和日均活跃用户数。

执行SQL

select count(distinct user_id) as mau,
       round(count(distinct user_id) / count(distinct substr(access_time, 1, 10)), 2) as avg_dau
from t3_user_access_log
where substr(access_time, 1, 10) between '2025-06-01' and '2025-06-30'

查询结果

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

count(distinct substr(access_time,1,10)) 统计6月有多少个有用户访问的日期，用于估算日均DAU。

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

从时间戳提取日期后按天分组统计DAU，通过 cross join 关联 MAU 计算比值。

执行SQL

select t.active_date,
       t.dau,
       m.mau,
       round(t.dau / m.mau, 4) as dau_mau_ratio
from (
    select substr(access_time, 1, 10) as active_date,
           count(distinct user_id) as dau
    from t3_user_access_log
    where substr(access_time, 1, 10) between '2025-06-01' and '2025-06-30'
    group by substr(access_time, 1, 10)
) t
cross join (
    select count(distinct user_id) as mau
    from t3_user_access_log
    where substr(access_time, 1, 10) 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         |
+-------------+-----+-----+----------------+

u01 在 06-01 访问了两次（08:00、18:30），count(distinct user_id) 正确地将其计为 1 个 DAU。

3.按周统计WAU​

按 weekofyear 提取周序号，去重统计每周活跃用户数。

执行SQL

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

查询结果

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

四、常见坑点​

坑1：同一用户一天多次访问需用 count(distinct user_id) 去重 — 如 u01 在 06-01 有两条访问记录（08:00、18:30），直接用 count(user_id) 会将其计为 2 次，而 DAU 需要的是去重后的 1 人。count(distinct user_id) 配合 substr() 提取日期再分组才能正确计算出每日的独立用户数。

坑2：WAU 边界周不完整 — 按 weekofyear 分周时，月初和月末的周可能只有部分天数落在目标月份内（如6月1日所在的 W23 实际包含5月底的几天）。边界周的 WAU 会偏低，分析时需要标注。

坑3：cross join 必须确保 MAU 子查询只返回一行 — cross join 会将右侧结果复制到每一行。MAU 子查询已完成 count(distinct user_id) 聚合，结果只有一行，因此 cross join 安全。若误写成未聚合的子查询，行数会爆炸。

五、知识点总结​

考点说明
count(distinct) 去重同一用户一天多次访问只计1人，DAU/WAU/MAU 的核心前提
substr 提取日期从时间戳字段中截取日期部分，将明细日志转化为日级粒度
cross join 广播将单行 MAU 结果广播到每一行，用于计算每日 DAU/MAU 比值
weekofyear 周粒度按自然周分组统计 WAU，需要注意边界周不完整的问题

六、建表语句和数据插入​

点击展开 DDL & DML
-- 建表语句
create table t3_user_access_log (
    user_id     string comment '用户ID',
    access_time string comment '访问时间'
) comment '用户访问日志表';

-- 插入数据
insert into t3_user_access_log(user_id, access_time) values
('u01','2025-06-01 08:00'),
('u01','2025-06-01 18:30'),
('u02','2025-06-01 09:15'),
('u03','2025-06-01 10:00'),
('u01','2025-06-02 08:00'),
('u04','2025-06-02 10:30'),
('u04','2025-06-02 14:00'),
('u01','2025-06-07 08:00'),
('u02','2025-06-07 10:00'),
('u02','2025-06-07 19:00'),
('u05','2025-06-07 11:00'),
('u01','2025-06-08 08:00'),
('u01','2025-06-08 20:00'),
('u03','2025-06-08 09:00'),
('u01','2025-06-15 08:00'),
('u06','2025-06-15 10:00'),
('u02','2025-06-22 08:00'),
('u04','2025-06-22 11:00'),
('u05','2025-06-22 14:00'),
('u05','2025-06-22 20:30');
📱关注公众号

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

微信公众号二维码
💬加群交流

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

交流微信二维码

你可能还想看

相关题目
01. IoT设备在线率02. MIUI系统版本分布04. 手机用户换机周期