SQL166 每天的日活數及新用戶占比

題目理解

本SQL查詢旨在分析用戶活躍數據，計算兩個關鍵指標：

1. 每日活躍用戶數(DAU)
2. 每日新增用戶占比(新用戶占活躍用戶的比例)

解題思路

1. 數據準備階段

首先我們需要獲取所有用戶的活躍記錄，包括：

• 用戶進入時間(in_time)
• 用戶離開時間(out_time)

由于一個用戶在同一天可能有多次活躍記錄，我們需要對數據進行去重處理。

---

2. 核心計算邏輯

通過三個CTE(Common Table Expressions)分步處理數據：

1. ?user_activity_records?：合并所有活躍記錄

   • 從in_time和out_time中提取日期
   • 使用UNION合并結果并自動去重

2. ?user_first_activity?：計算每個用戶的首次活躍日期

   • 按用戶分組
   • 使用MIN函數找出每個用戶的最早活躍日期

3. ?user_activity_with_first_date?：關聯活躍記錄與首次活躍日期

   • 將活躍記錄與用戶首次活躍日期關聯
   • 為后續計算準備完整數據集

---

3. 最終指標計算

基于準備好的數據，計算兩個核心指標：

1. ?daily_active_users?：每日活躍用戶數

   • 按日期分組
   • 使用COUNT(*)計算每日不重復用戶數

2. ?new_user_ratio?：新增用戶占比

   • 判斷當前活躍日期是否為用戶的首次活躍日期
   • 計算新增用戶數占總活躍用戶數的比例
   • 使用ROUND保留兩位小數

---

技術亮點

1. ?UNION自動去重?：高效處理用戶可能在同一天多次活躍的情況
2. ?CTE分步處理?：使復雜查詢邏輯清晰易讀
3. ?IF條件計數?：優雅地實現條件計數功能
4. ?JOIN USING語法?：簡化相同列名的連接操作

最終代碼

WITH-- 獲取用戶活躍日期（合并in_time和out_time）user_activity_records AS (SELECTuid,DATE(in_time) AS activity_dateFROMtb_user_logUNIONSELECTuid,DATE(out_time) AS activity_dateFROMtb_user_log),-- 計算每個用戶的首次活躍日期user_first_activity AS (SELECTuid,MIN(activity_date) AS first_activity_dateFROMuser_activity_recordsGROUP BYuid),-- 合并活躍記錄和首次活躍日期user_activity_with_first_date AS (SELECTuar.uid,uar.activity_date,ufa.first_activity_dateFROMuser_activity_records uarJOINuser_first_activity ufa USING (uid))-- 計算每日活躍用戶數和新增用戶占比
SELECTactivity_date,COUNT(*) AS daily_active_users,ROUND(COUNT(IF(first_activity_date = activity_date, 1, NULL)) / COUNT(*),2) AS new_user_ratio
FROMuser_activity_with_first_date
GROUP BYactivity_date
ORDER BYactivity_date;