Katkat
Back

SQL: Zero-Filled DAU, Rolling 30-Day MAU, and the MAU Impact of a User-ID Rehash

GleanData Scientist · Onsite
MediumData ScientistOnsiteData Manipulation (SQL/Python)

Last updated

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.

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:

ColumnTypeMeaning
user_idBIGINTthe user who logged in
login_tsTIMESTAMPwhen 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.

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?

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?

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

Hints

Hint 1

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.

Hint 2

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.

Hint 3

Who is counted twice: For a window that straddles the rehash date, describe exactly which users appear under two different ids.

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?