Rolling 7-day active users and DAU/MAU

SQL data engineering interview problem. Difficulty: advanced. Pattern: Window Functions. About 22 minutes. Part of the Pro drill bank.

Product teams track how many different users are active. daily_activity has 1 row for each user and each day the user was active. For every date that appears in the table, work out four things. dau is the number of distinct users active on that day. rolling_7d_users is the number of distinct users active on that day or any of the 6 days before it. mau_30d is the same for the last 30 days (that day and the 29 before). dau_mau_ratio is dau divided by mau_30d, rounded to 2 decimals. A user must count once in a window even if they were active on several days of it. Return day, dau, rolling_7d_users, mau_30d and dau_mau_ratio, ordered by day. Days with no activity do not get a row.

Requirements

• Return 1 row for each date that appears in daily_activity.
• Count distinct users for the day, for the last 7 days and for the last 30 days.
• The windows are calendar days, including the current day.
• Calculate dau_mau_ratio as dau / mau_30d, rounded to 2 decimals.
• Order by day.

Constraints

• A user appears at most once per day.
• Windows are measured in calendar days, not in rows. Dates with gaps still use the right window.
• Multiply by 1.0 (or cast) before dividing so the ratio is not a whole-number division.

Examples

Input: daily_activity user_id | activity_date a | 2024-02-01 b | 2024-02-01 a | 2024-02-02 c | 2024-02-02 a | 2024-02-04 d | 2024-02-05 b | 2024-02-05 e | 2024-02-07 a | 2024-02-08 c | 2024-02-09 f | 2024-02-09 b | 2024-02-12 ... Output: day | dau | rolling_7d_users | mau_30d | dau_mau_ratio 2024-02-01 | 2 | 2 | 2 | 1 2024-02-02 | 2 | 3 | 3 | 0.67 2024-02-04 | 1 | 3 | 3 | 0.33 2024-02-05 | 2 | 4 | 4 | 0.5 2024-02-07 | 1 | 5 | 5 | 0.2 2024-02-08 | 1 | 5 | 5 | 0.2 2024-02-09 | 2 | 6 | 6 | 0.33 2024-02-12 | 1 | 5 | 6 | 0.17 2024-02-14 | 2 | 5 | 7 | 0.29 2024-02-20 | 2 | 3 | 7 | 0.29 2024-02-28 | 1 | 1 | 8 | 0.13 Why this passes: On 2024-02-09 the trailing 7 days contain users a, d, b, e, c and f so 6 distinct users, even though only 2 were active that day. The 30-day count keeps growing as new users appear.

Input: daily_activity user_id | activity_date a | 2024-02-01 a | 2024-02-02 b | 2024-02-02 Output: day | dau | rolling_7d_users | mau_30d | dau_mau_ratio 2024-02-01 | 1 | 1 | 1 | 1 2024-02-02 | 2 | 2 | 2 | 1 Why this passes: A user active on two consecutive days counts once in the rolling window.

Topics: lakebench, sql, rolling distinct, dau, mau.

More SQL interview questions · All interview problems · Learn data engineering