반응형
> DAU 데이터 구조
| event_date | dau |
| 2025-07-01 | 12,000 |
| 2025-07-02 | 13,000 |
| 2025-07-03 | 15,000 |
| 2025-07-04 | 13,500 |
| 2025-07-05 | 16,000 |
| 2025-07-06 | 15,000 |
| 2025-07-07 | 19,000 |
> DAU 및 Z-Score 조회 쿼리
SELECT
event_date,
dau,
ROUND((dau - stats.avg_dau) / stats.std_dau, 4) AS dau_zscore
FROM
tbl_dau as ta,
(
SELECT
AVG(dau) AS avg_dau,
STDDEV(dau) AS std_dau
FROM tbl_dau
where event_date >= '2025-07-01'
and event_date < '2025-07-11'
) AS stats
where ta.event_date >= '2025-07-01'
and ta.event_date < '2025-07-11'
ORDER BY event_date;
> 조회 결과
| event_date | dau | dau_zscore |
| 2025-07-01 | 12,000 | -1.3044 |
| 2025-07-02 | 13,000 | -0.8361 |
| 2025-07-03 | 15,000 | 0.1003 |
| 2025-07-04 | 13,500 | -0.602 |
| 2025-07-05 | 16,000 | 0.5686 |
| 2025-07-06 | 15,000 | 0.1003 |
| 2025-07-07 | 19,000 | 1.9733 |
반응형