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

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?