Coding questions · Glean
Compute DAU and rolling MAU with zero days
This question evaluates a data scientist's proficiency in time-series analytics and data aggregation, focusing on computing daily active users and a 30-day rolling MAU from user and login tables, and falls under the Data Manipulation (SQL/Python) domain.
You have two tables in PostgreSQL:
Tables
users
user_id(STRING / INT, PK)signup_date(DATE)
logins
user_id(STRING / INT, FK →users.user_id)browser(TEXT)login_ts(TIMESTAMP) — assume UTC unless stated otherwise
Tasks
-
Daily Active Users (DAU): For a given date range (e.g.,
2023-01-01to2023-12-31), compute DAU where DAU on a day = number of distinctuser_idthat logged in on that calendar day.- Output columns:
date,dau - Requirement: Include all dates in the range, even if there were zero logins that day (show
dau = 0). - Follow-up: If you need a date dimension/calendar, explain how you would create it in PostgreSQL (e.g., via
generate_series).
- Output columns:
-
Rolling MAU by date: Compute a rolling-window MAU for each calendar date in the range.
- Define MAU as distinct users who logged in in the last 30 days including the current date (an L30D lookback).
- Output columns:
date,mau_l30d - Follow-up discussion: If the product is primarily used on weekdays (little/no weekend usage), what are potential issues with an L30D definition, and what alternative window definitions could be more appropriate?
Compute DAU (include zero-activity days)
You are given two tables: users (who signed up) and logins (each login event). Write a SQL query (PostgreSQL) to compute daily active users (DAU) for each calendar date from 2025-05-25 to 2025-06-01 (inclusive).
Requirements:
- DAU for a date = number of DISTINCT
user_idvalues that logged in on that date. - If there are dates with no logins, they must still appear in the output with DAU = 0.
- Output columns:
activity_date,dau. - Order by
activity_dateascending.
Table users
| column | type | constraints |
|---|---|---|
| user_id | INT | PRIMARY KEY |
| signup_date | DATE | NOT NULL |
Sample data:
| signup_date | user_id |
|---|---|
| 2025-04-01 | 1 |
| 2025-04-15 | 2 |
| 2025-05-01 | 3 |
| 2025-05-15 | 4 |
| 2025-05-30 | 5 |
Table logins
| column | type | constraints |
|---|---|---|
| login_id | INT | PRIMARY KEY |
| user_id | INT | NOT NULL |
| browser | VARCHAR(20) | NOT NULL |
| login_date | DATE | NOT NULL |
Sample data:
| browser | login_date | login_id | user_id |
|---|---|---|---|
| chrome | 2025-05-05 | 1 | 2 |
| safari | 2025-05-12 | 2 | 3 |
| chrome | 2025-05-18 | 3 | 2 |
| firefox | 2025-05-21 | 4 | 1 |
| chrome | 2025-05-22 | 5 | 102 |
| safari | 2025-05-23 | 6 | 103 |
| firefox | 2025-05-25 | 7 | 1 |
| chrome | 2025-05-25 | 8 | 102 |
| firefox | 2025-05-27 | 9 | 1 |
| safari | 2025-05-28 | 10 | 103 |
| edge | 2025-05-30 | 11 | 4 |
| firefox | 2025-05-30 | 12 | 1 |
| chrome | 2025-06-01 | 13 | 5 |
Expected output
One row per calendar date from 2025-05-25 to 2025-06-01 with DAU, including dates with no logins showing 0.
| activity_date | dau |
|---|---|
| 2025-05-25 | 2 |
| 2025-05-26 | 0 |
| 2025-05-27 | 1 |
| 2025-05-28 | 1 |
| 2025-05-29 | 0 |
| 2025-05-30 | 2 |
| 2025-05-31 | 0 |
| 2025-06-01 | 1 |
Hints
- Use
generate_series(start_date, end_date, interval '1 day')to create the missing dates. - LEFT JOIN the generated calendar to the logins and aggregate with COUNT(DISTINCT user_id).
Compute rolling 30-day MAU by date (calendar-day window)
Using the same logins table, compute a rolling 30-day MAU for each calendar date from 2025-05-25 to 2025-06-01 (inclusive).
Definitions:
- For each
activity_date, MAU_30d = number of DISTINCTuser_idvalues with at least one login in the calendar window from (activity_date - 29 days) through activity_date (inclusive).
Requirements:
- Output must include all dates in the range even if there are no logins that day.
- Output columns:
activity_date,mau_30d. - Order by
activity_dateascending.
Table users
| column | type | constraints |
|---|---|---|
| user_id | INT | PRIMARY KEY |
| signup_date | DATE | NOT NULL |
Sample data:
| signup_date | user_id |
|---|---|
| 2025-04-01 | 1 |
| 2025-04-15 | 2 |
| 2025-05-01 | 3 |
| 2025-05-15 | 4 |
| 2025-05-30 | 5 |
Table logins
| column | type | constraints |
|---|---|---|
| login_id | INT | PRIMARY KEY |
| user_id | INT | NOT NULL |
| browser | VARCHAR(20) | NOT NULL |
| login_date | DATE | NOT NULL |
Sample data:
| browser | login_date | login_id | user_id |
|---|---|---|---|
| chrome | 2025-05-05 | 1 | 2 |
| safari | 2025-05-12 | 2 | 3 |
| chrome | 2025-05-18 | 3 | 2 |
| firefox | 2025-05-21 | 4 | 1 |
| chrome | 2025-05-22 | 5 | 102 |
| safari | 2025-05-23 | 6 | 103 |
| firefox | 2025-05-25 | 7 | 1 |
| chrome | 2025-05-25 | 8 | 102 |
| firefox | 2025-05-27 | 9 | 1 |
| safari | 2025-05-28 | 10 | 103 |
| edge | 2025-05-30 | 11 | 4 |
| firefox | 2025-05-30 | 12 | 1 |
| chrome | 2025-06-01 | 13 | 5 |
Expected output
One row per calendar date from 2025-05-25 to 2025-06-01 with distinct users seen in the preceding 30 calendar days (inclusive).
| activity_date | mau_30d |
|---|---|
| 2025-05-25 | 5 |
| 2025-05-26 | 5 |
| 2025-05-27 | 5 |
| 2025-05-28 | 5 |
| 2025-05-29 | 5 |
| 2025-05-30 | 6 |
| 2025-05-31 | 6 |
| 2025-06-01 | 7 |
Hints
- Generate the output dates first, then join logins using a BETWEEN condition for the rolling window.
- MAU is a distinct count over a date range, not a sum of DAU.
One-time user_id rehash: compute MAU overestimate (min/max %)
Sometimes a system performs a one-time full rehash of user IDs: the same real user gets a new user_id from a specific date onward.
You have:
logins: login events (after the rehash date, affected users appear under their newuser_id).user_id_rehash: mapping from old IDs to new IDs and therehash_datewhen the new IDs start being used.
Task (PostgreSQL):
- For each calendar date from 2025-05-20 to 2025-06-01 (inclusive), compute rolling 30-day MAU in two ways:
naive_mau_30d: COUNT(DISTINCTlogins.user_id) in [activity_date - 29, activity_date]corrected_mau_30d: COUNT(DISTINCT canonical_user_id) in [activity_date - 29, activity_date], where canonical_user_id = old_user_id for rehashed users (i.e., map new IDs back to old IDs).
- For each date compute
overestimate_pct = (naive_mau_30d - corrected_mau_30d) / corrected_mau_30d * 100. - Return a single row with the maximum and minimum
overestimate_pctobserved across dates 2025-05-20 to 2025-06-01.
Output columns: max_overestimate_pct, min_overestimate_pct (rounded to 2 decimals).
Table users
| column | type | constraints |
|---|---|---|
| user_id | INT | PRIMARY KEY |
| signup_date | DATE | NOT NULL |
Sample data:
| signup_date | user_id |
|---|---|
| 2025-04-01 | 1 |
| 2025-04-15 | 2 |
| 2025-05-01 | 3 |
| 2025-05-15 | 4 |
| 2025-05-30 | 5 |
Table user_id_rehash
| column | type | constraints |
|---|---|---|
| old_user_id | INT | NOT NULL |
| new_user_id | INT | PRIMARY KEY |
| rehash_date | DATE | NOT NULL |
Sample data:
| new_user_id | old_user_id | rehash_date |
|---|---|---|
| 102 | 2 | 2025-05-20 |
| 103 | 3 | 2025-05-20 |
Table logins
| column | type | constraints |
|---|---|---|
| login_id | INT | PRIMARY KEY |
| user_id | INT | NOT NULL |
| browser | VARCHAR(20) | NOT NULL |
| login_date | DATE | NOT NULL |
Sample data:
| browser | login_date | login_id | user_id |
|---|---|---|---|
| chrome | 2025-05-05 | 1 | 2 |
| safari | 2025-05-12 | 2 | 3 |
| chrome | 2025-05-18 | 3 | 2 |
| firefox | 2025-05-21 | 4 | 1 |
| chrome | 2025-05-22 | 5 | 102 |
| safari | 2025-05-23 | 6 | 103 |
| firefox | 2025-05-25 | 7 | 1 |
| chrome | 2025-05-25 | 8 | 102 |
| firefox | 2025-05-27 | 9 | 1 |
| safari | 2025-05-28 | 10 | 103 |
| edge | 2025-05-30 | 11 | 4 |
| firefox | 2025-05-30 | 12 | 1 |
| chrome | 2025-06-01 | 13 | 5 |
Expected output
A single-row summary showing the maximum and minimum overestimation percentages caused by counting rehashed IDs as different users (within the date range 2025-05-20 to 2025-06-01).
| max_overestimate_pct | min_overestimate_pct |
|---|---|
| 66.67 | 0 |
Hints
- Map
new_user_idback toold_user_idto get a canonical ID before doing DISTINCT counting. - Compute naive vs corrected per day first, then take MAX/MIN over the date range.