Label new vs old users over time in SQL
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Define users as “new” during the first 30 days inclusive after their signup_date, and “old” thereafter. Produce per-user, per-day labels over a window, acknowledging that a single user naturally has different labels on different days. Use today = 2025-09-01.
Schema:
users(user_id INT, signup_date DATE)
activity(user_id INT, activity_date DATE) -- any daily activity counts as presence
Sample data:
users
+---------+-------------+
| user_id | signup_date |
+---------+-------------+
| 1 | 2025-07-25 |
| 2 | 2025-08-20 |
| 3 | 2025-08-01 |
| 4 | 2025-09-01 |
+---------+-------------+
activity
+---------+---------------+
| user_id | activity_date |
+---------+---------------+
| 1 | 2025-08-02 |
| 1 | 2025-08-26 |
| 2 | 2025-08-25 |
| 2 | 2025-09-01 |
| 3 | 2025-08-15 |
| 3 | 2025-09-01 |
| 4 | 2025-09-01 |
+---------+---------------+
Tasks:
A) Write SQL that outputs (user_id, d, label) for every day d in [2025-08-02, 2025-09-01], labeling “new” if d between signup_date and signup_date + 29 days inclusive, else “old”. Avoid assigning multiple labels for the same user-day. Prefer generating dates only where activity exists (join to activity) to reduce scan cost.
B) Using your labels, compute two aggregates for the last 30 days relative to today (window = [2025-08-03, 2025-09-01]): (i) active_new_users and active_old_users per day; (ii) a single summary row with total distinct users by label over the window. Explain any assumptions about time zones and inclusive/exclusive bounds.
Overview: This question evaluates a candidate's ability to perform time-based user labeling and aggregation in SQL, testing skills such as date arithmetic, joins, deduplication of user-day records, and efficient generation of per-user-per-day labels.
Read the full Meta Data Scientist interview experience this question came from
Label new vs old users per active day
You are given two tables:
- users(user_id INT, signup_date DATE)
- activity(user_id INT, activity_date DATE)
A user is considered "new" from their signup_date through signup_date + 29 days (inclusive), and "old" on any later day. Any activity on a given day counts as presence for that user on that date.
Write SQL that outputs one row per (user_id, d, label) for every active day d in the range [2025-08-02, 2025-09-01] (inclusive), where d comes from activity.activity_date. The label must be "new" if d is between signup_date and signup_date + 29 days inclusive, otherwise "old". Avoid generating multiple labels for the same user-day, and prefer generating dates only where activity exists (by joining to the activity table).
Tables
users(user_id INT, signup_date DATE)
activity(user_id INT, activity_date DATE)
Hints
- Join the activity table to the users table on user_id to get each user’s signup_date for each activity_date.
- Use a CASE expression with date arithmetic to compare activity_date to signup_date and signup_date + 29 days.
Aggregate active new vs old users over the last 30 days
You are given two tables that track user signups and daily activity at a product.
**`users`**
| column | type | notes |
|---|---|---|
| `user_id` | INT | primary key |
| `signup_date` | DATE | the day the user signed up (NOT NULL) |
**`activity`**
| column | type | notes |
|---|---|---|
| `user_id` | INT | the user who was active (NOT NULL) |
| `activity_date` | DATE | a day on which that user was active (NOT NULL) |
**Labeling rule (same as Question 1).** For a given calendar day, a user is labeled **"new"** if that day is between `signup_date` and `signup_date + 29 days`, inclusive; otherwise the user is labeled **"old"** on that day. A user can therefore be "new" on some days and "old" on later days.
**Assume today is `2025-09-01`** and consider the fixed 30-day window **`[2025-08-03, 2025-09-01]`** (both endpoints inclusive). All dates are in the same time zone.
Write a **single** PostgreSQL query that returns one result set containing:
1. **Daily rows** — one row for each `activity_date` in the window on which at least one user was active, with columns:
- `activity_date`
- `active_new_users`: the number of distinct users who were active on that date **and** labeled "new" on that date
- `active_old_users`: the number of distinct users who were active on that date **and** labeled "old" on that date
2. **One summary row** for the whole window, identified by `activity_date = NULL`, where:
- `active_new_users`: the number of distinct users who were labeled "new" on **at least one** active day in the window
- `active_old_users`: the number of distinct users who were labeled "old" on **at least one** active day in the window
Note: because a user can switch labels across days, the summary `active_new_users` and `active_old_users` counts can overlap (a user counted in both). Days with no activity inside the window do not produce a row.
**Output ordering:** sort the daily rows by `activity_date` ascending, and place the summary row (`activity_date = NULL`) last.
Tables
users(user_id INT, signup_date DATE)
activity(user_id INT, activity_date DATE)
Hints
- PostgreSQL lets you add an integer number of days to a DATE directly: `signup_date + 29` is the last day a user is still 'new'. (`signup_date + INTERVAL '29' DAY` would return a TIMESTAMP and can break the comparison.)
- Compute the per-(user, day) 'new'/'old' label in a CTE first, filtered to the window, then aggregate.
Community answers
Answer by SS
With combine as ( Select a.user_id , a.signup_Date, b.activity_date , case when b.activity_date >= a.signup_date and b.activity_date <= a.signup_date + interval '29 days' then 'New' else 'Old' end as label from users a inner join activity b on a.user_id = b.user_id where b.activity_date between date '2025-08-02' and date '2025-09-01')Select activity_date as d , label , user_idfrom combine order by user_id , activity_date