Coding questions · Meta
Label new vs old users over time in SQL
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.
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.
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).
Table users
| column | type | constraints |
|---|---|---|
| user_id | INT | PRIMARY KEY |
| signup_date | DATE | NOT NULL |
Sample data:
| signup_date | user_id |
|---|---|
| 2025-07-25 | 1 |
| 2025-08-20 | 2 |
| 2025-08-01 | 3 |
| 2025-09-01 | 4 |
Table activity
| column | type | constraints |
|---|---|---|
| user_id | INT | NOT NULL |
| activity_date | DATE | NOT NULL |
Sample data:
| activity_date | user_id |
|---|---|
| 2025-08-02 | 1 |
| 2025-08-26 | 1 |
| 2025-08-25 | 2 |
| 2025-09-01 | 2 |
| 2025-08-15 | 3 |
| 2025-09-01 | 3 |
| 2025-09-01 | 4 |
Expected output
One row per (user_id, activity_date) in [2025-08-02, 2025-09-01], labeled as "new" if the activity_date is between signup_date and signup_date + 29 days inclusive, otherwise "old".
| d | label | user_id |
|---|---|---|
| 2025-08-02 | new | 1 |
| 2025-08-26 | old | 1 |
| 2025-08-25 | new | 2 |
| 2025-09-01 | new | 2 |
| 2025-08-15 | new | 3 |
| 2025-09-01 | old | 3 |
| 2025-09-01 | new | 4 |
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:
- Daily rows — one row for each
activity_datein the window on which at least one user was active, with columns:activity_dateactive_new_users: the number of distinct users who were active on that date and labeled "new" on that dateactive_old_users: the number of distinct users who were active on that date and labeled "old" on that date
- 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 windowactive_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.
Table users
| column | type | constraints |
|---|---|---|
| user_id | INT | PRIMARY KEY |
| signup_date | DATE | NOT NULL |
Sample data:
| signup_date | user_id |
|---|---|
| 2025-07-25 | 1 |
| 2025-08-20 | 2 |
| 2025-08-01 | 3 |
| 2025-09-01 | 4 |
Table activity
| column | type | constraints |
|---|---|---|
| user_id | INT | NOT NULL |
| activity_date | DATE | NOT NULL |
Sample data:
| activity_date | user_id |
|---|---|
| 2025-08-02 | 1 |
| 2025-08-26 | 1 |
| 2025-08-25 | 2 |
| 2025-09-01 | 2 |
| 2025-08-15 | 3 |
| 2025-09-01 | 3 |
| 2025-09-01 | 4 |
Expected output
Daily distinct counts of active new vs old users in [2025-08-03, 2025-09-01], followed by one summary row (activity_date = NULL) giving distinct users ever labeled new / old over the window.
| active_new_users | active_old_users | activity_date |
|---|---|---|
| 1 | 0 | 2025-08-15 |
| 1 | 0 | 2025-08-25 |
| 0 | 1 | 2025-08-26 |
| 2 | 1 | 2025-09-01 |
| 3 | 2 | NULL |
Hints
- PostgreSQL lets you add an integer number of days to a DATE directly:
signup_date + 29is the last day a user is still 'new'. (signup_date + INTERVAL '29' DAYwould 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.
- Produce the daily rows and the single window summary as two SELECTs combined with UNION ALL; set the summary row's activity_date to NULL and push it last with a CASE in ORDER BY.