Calculate DAU, WAU, MAU for an E-commerce Platform

SQL coding challenge · Difficulty: hard · +200 XP

An e-commerce platform wants to track user engagement. Given a user_activity table of login records, compute three metrics for each date in the data:

• DAU (Daily Active Users) — distinct users active on that day.
• WAU (Weekly Active Users) — distinct users active in the trailing 7 days (that day and the 6 days before).
• MAU (Monthly Active Users) — distinct users active in the trailing 30 days (that day and the 29 days before).

Return activity_date, DAU, WAU, MAU, ordered by activity_date ascending.

Example input

user_activity table:

| activity_id | user_id | activity_date |
| --- | --- | --- |
| 1 | 201 | 2024-01-01 |
| 2 | 202 | 2024-01-01 |
| 3 | 203 | 2024-01-01 |
| 4 | 201 | 2024-01-02 |
| 5 | 202 | 2024-01-03 |
| 6 | 204 | 2024-01-03 |
| 7 | 205 | 2024-01-04 |

Expected output

| activity_date | DAU | WAU | MAU |
| --- | --- | --- | --- |
| 2024-01-01 | 3 | 3 | 3 |
| 2024-01-02 | 1 | 3 | 3 |
| 2024-01-03 | 2 | 4 | 4 |
| 2024-01-04 | 1 | 5 | 5 |