SQL data engineering interview problem. Difficulty: advanced. Pattern: Window Functions. About 22 minutes. Part of the Pro drill bank.
daily_activity has one row per user and day the user was active. For every date that appears in the table, return: dau: distinct users active on that day rolling_7d_users: distinct users active on that day or any of the previous 6 days mau_30d: distinct users active on that day or any of the previous 29 days dau_mau_ratio: dau / mau_30d rounded to 2 decimals Columns: day, dau, rolling_7d_users, mau_30d, dau_mau_ratio. Order by day. Users must be counted once per window even if active on several days of it. Days with no activity are not rows.
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
Interview-style drill: Per active day: daily users, distinct users over the trailing 7 and 30 days, and the DAU/MAU ratio.
`daily_activity` has one row per user and day the user was active. For every date that appears in the table, return: - `dau`: distinct users active on that day - `rolling_7d_users`: distinct users active on that day or any of the previous 6 days - `mau_30d`: distinct users active on that day or any of the previous 29 days - `dau_mau_ratio`: `dau / mau_30d` rounded to 2 decimals Columns: `day`, `dau`, `rolling_7d_users`, `mau_30d`, `dau_mau_ratio`. Order by `day`. Users must be counted once per window even if active on several days of it. Days with no activity are not rows.