Quick Overview

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.

Compute DAU and rolling MAU with zero days

Company: Glean

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: easy

Interview Round: Technical Screen

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 1) **Daily Active Users (DAU)**: For a given date range (e.g., `2023-01-01` to `2023-12-31`), compute DAU where DAU on a day = number of distinct `user_id` that 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`). 2) **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?

Overview: 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.

Read the full Glean Data Scientist interview experience this question came from

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_id` values 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_date` ascending.

Tables

users(user_id INT, signup_date DATE)

logins(login_id INT, user_id INT, browser VARCHAR(20), login_date DATE)

Hints

  1. Use `generate_series(start_date, end_date, interval '1 day')` to create the missing dates.
  2. 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 DISTINCT `user_id` values 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_date` ascending.

Tables

users(user_id INT, signup_date DATE)

logins(login_id INT, user_id INT, browser VARCHAR(20), login_date DATE)

Hints

  1. Generate the output dates first, then join logins using a BETWEEN condition for the rolling window.
  2. 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 new `user_id`). - `user_id_rehash`: mapping from old IDs to new IDs and the `rehash_date` when the new IDs start being used. Task (PostgreSQL): 1) 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(DISTINCT `logins.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). 2) For each date compute `overestimate_pct = (naive_mau_30d - corrected_mau_30d) / corrected_mau_30d * 100`. 3) Return a single row with the maximum and minimum `overestimate_pct` observed across dates 2025-05-20 to 2025-06-01. Output columns: `max_overestimate_pct`, `min_overestimate_pct` (rounded to 2 decimals).

Tables

users(user_id INT, signup_date DATE)

user_id_rehash(old_user_id INT, new_user_id INT, rehash_date DATE)

logins(login_id INT, user_id INT, browser VARCHAR(20), login_date DATE)

Hints

  1. Map `new_user_id` back to `old_user_id` to get a canonical ID before doing DISTINCT counting.
  2. Compute naive vs corrected per day first, then take MAX/MIN over the date range.

Loading coding console...