SQL: Zero-Filled DAU, Rolling 30-Day MAU, and the MAU Impact of a User-ID Rehash
Company: Glean
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
This SQL round for a data scientist role packed five SQL questions, each with a follow-up, into one hour. The bar was described as error-free answers, follow-ups included, with a justification for every choice made in pulling the data. Three of the questions were reported, and they are given below in order.
Assume a PostgreSQL table of login events. The report does not give the schema, so this one is an assumption:
| Column | Type | Meaning |
|---|---|---|
| `user_id` | `BIGINT` | the user who logged in |
| `login_ts` | `TIMESTAMP` | when the login happened, in UTC |
A user can log in many times a day, and the table has no primary key.
### Clarifying Questions
- Which date range should the outputs cover: from the first to the last login in the table, or a reporting range passed in?
- Should days be cut in UTC or in each user's local time zone?
- Are test or internal accounts in the table, and should they be excluded?
### Part 1 — Daily active users, including days with none
Write a query that returns one row per calendar day with the number of distinct users who logged in that day. Days on which nobody logged in must appear with a count of `0`.
```hint Missing days
A `GROUP BY` over the login table can only produce days that appear in it. Think about where the other days come from.
```
#### What This Part Should Cover
- Counting distinct users, not logins
- A row for every day in the range, including empty ones
- The ordering and the date range of the output
### Part 2 — Rolling 30-day MAU for each date
For every date, return the number of distinct users who logged in during the trailing 30 days ending on that date (the "L30D" window).
Follow-up: suppose the product is a workday product that nobody uses on weekends. Is the L30D lookback a problem?
```hint Windows and distinct counts
Check whether your database supports a distinct count inside a window function before relying on one, and be precise about how many calendar days "the last 30 days" covers.
```
#### Clarifying Questions for this Part
- Should dates whose window reaches back before the first day of data be reported, flagged or dropped?
#### What This Part Should Cover
- A correct, inclusive 30-day window in which each user counts once
- How the query's cost grows with the table
- How a weekday-only usage pattern interacts with a 30-day window
### Part 3 — Effect of a user-ID rehash on MAU
On some date the `user_id` values were rehashed. Assume this means every user received a new id that day and logs in under the new id from then on. The report says only "user_id rehash", so confirm this interpretation. How does the rehash affect the rolling MAU from Part 2? What is the maximum possible overestimate, and the minimum, as a percentage of the true MAU?
```hint Who is counted twice
For a window that straddles the rehash date, describe exactly which users appear under two different ids.
```
#### What This Part Should Cover
- Which dates' MAU values are affected, and for how long
- The overestimate expressed as a formula, and its extreme cases
- How to detect and correct the problem in the data
### What a Strong Answer Covers
- Queries that are correct on the first attempt, follow-ups included
- A justification for each data choice: distinct counting, the calendar source, window bounds, the time zone
- Awareness of database-specific limitations and of query cost on large tables
- Clear reasoning about metric artifacts such as weekday patterns and identity changes, not only query syntax
### Follow-up Questions
- How would you compute the DAU/MAU stickiness ratio, and how do weekends affect it for this product?
- How would you make the rolling MAU query cheap enough to run daily over billions of login rows?
- If only some users were rehashed and you have a table mapping old ids to new ones, how would you fix the historical MAU?
Overview: Three SQL questions on a login table: daily active users with zero-filled days, rolling 30-day MAU per date with a weekday-usage follow-up, and how a user-ID rehash inflates MAU. Tests distinct counting, calendar generation, window boundaries, query cost and reasoning about metric artifacts.
Community answers
Answer by stilllearning
Part 1
/ We need to create a date spine such that every date within the max and the min date can be captured. /
WITH date_spine AS (
SELECT generate_series(
MIN(login_ts)::date,
MAX(login_ts)::date,
INTERVAL '1 day'
)::date AS dt
FROM user_logins
),
daily AS (
SELECT
login_ts::date AS dt,
COUNT(DISTINCT user_id) AS dau
FROM user_logins
GROUP BY login_ts::date
)
SELECT
d.dt,
COALESCE(a.dau, 0) AS dau
FROM date_spine d
LEFT JOIN daily a
ON d.dt = a.dt
ORDER BY d.dt;
Part 2
with activity as (
Select distinct user_id, cast(login_ts::date as date) login_date
From user_logins
)
dates as (
SELECT generate_series(
MIN(login_ts)::date,
MAX(login_ts)::date,
INTERVAL '1 day'
)::date AS dt
FROM user_logins
)
Select d.dates, count(distinct case when a.login_dates = d.dates then user_id else null end) day
, count(distinct user_id) trailing_mau
From dates d
Left join activity a on a.login_date between d.dates and d.dates - INTERVAL ’29 days’
Part 3
In order to accurately answer if there was a duplication we need to have a mapping table to map the old ids to rehashed ids. After that we can remove those old ids which hits rehashed mapping and calculate a new MAU. The difference would give the answer
Answer by datachamp
WITH daily_activity AS (
SELECT DISTINCT
user_id,
login_date::date AS login_date
FROM logins
),
user_dates AS (
SELECT generate_series(
min(login_date)::date,
max(login_date)::date,
INTERVAL '1 day'
)::date AS activity_date
from
daily_activity
),
SELECT
d.activity_date,
COUNT(DISTINCT a.user_id) AS mau_30d
FROM user_dates d
LEFT JOIN daily_activity a
ON a.login_date BETWEEN d.activity_date - 29 AND d.activity_date
GROUP BY d.activity_date
ORDER BY d.activity_date;