Compute DAU and rolling MAU with zero days
You are provided with two PostgreSQL tables:
Tables
users
user_id(STRING / INT, Primary Key)signup_date(DATE)
logins
user_id(STRING / INT, Foreign Key referencingusers.user_id)browser(TEXT)login_ts(TIMESTAMP) — assume UTC unless stated otherwise
Tasks
-
Daily Active Users (DAU): For a specified date range (e.g.,
2023-01-01to2023-12-31), compute the Daily Active Users. DAU for a given day is defined as the number of distinctuser_ids that logged in on that calendar day.- The output should include two columns:
dateanddau. - Requirement: All dates within the specified range must be included in the output, even if there were zero logins on a particular day (in which case
daushould be0). - Follow-up: Explain how you would create a date dimension or calendar table in PostgreSQL, for example, using
generate_series.
- The output should include two columns:
-
Rolling Monthly Active Users (MAU) by Date: Compute a rolling-window MAU for each calendar date within the specified range.
- MAU is defined as the number of distinct users who logged in within the last 30 days, including the current date (a "Last 30 Days" or L30D lookback window).
- The output should include two columns:
dateandmau_l30d. - Follow-up discussion: If the product is primarily used on weekdays with little to no weekend usage, what potential issues might arise from an L30D definition, and what alternative window definitions could be more appropriate?
AI interview prep
Core dilemma
Balancing the computational complexity of calculating distinct user counts over rolling time windows on potentially large datasets, while ensuring complete time series data (including zero-activity days) and maintaining database performance.
3 tradeoffs and 3 stress tests locked
Interview prep
Unlock the interviewer X-Ray rubric, tradeoff matrix, stress tests, clarifying questions, and key concepts.
Compute DAU and rolling MAU with zero days
You are provided with two PostgreSQL tables:
Tables
users
user_id(STRING / INT, Primary Key)signup_date(DATE)
logins
user_id(STRING / INT, Foreign Key referencingusers.user_id)browser(TEXT)login_ts(TIMESTAMP) — assume UTC unless stated otherwise
Tasks
-
Daily Active Users (DAU): For a specified date range (e.g.,
2023-01-01to2023-12-31), compute the Daily Active Users. DAU for a given day is defined as the number of distinctuser_ids that logged in on that calendar day.- The output should include two columns:
dateanddau. - Requirement: All dates within the specified range must be included in the output, even if there were zero logins on a particular day (in which case
daushould be0). - Follow-up: Explain how you would create a date dimension or calendar table in PostgreSQL, for example, using
generate_series.
- The output should include two columns:
-
Rolling Monthly Active Users (MAU) by Date: Compute a rolling-window MAU for each calendar date within the specified range.
- MAU is defined as the number of distinct users who logged in within the last 30 days, including the current date (a "Last 30 Days" or L30D lookback window).
- The output should include two columns:
dateandmau_l30d. - Follow-up discussion: If the product is primarily used on weekdays with little to no weekend usage, what potential issues might arise from an L30D definition, and what alternative window definitions could be more appropriate?