Define and analyze new-vs-existing activity
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
Ambiguous product question: Are existing users more active than new users over the last 28 days (ending today = 2025-09-01)? 1) Propose two reasonable, mutually exclusive definitions for existing vs new (e.g., by signup date or by prior activity), and two defensible definitions of active (e.g., DAU, sessions/week). Briefly state pros/cons and pick one pair to implement. 2) Using the schema and sample data below, write SQL that: a) labels users as new or existing; b) computes each cohort's 7-day rolling active rate and average daily events/user over the last 28 days; c) adjusts for partial observation windows for users who signed up within the window; d) produces a final table with date, cohort, active_users, total_users_observed, active_rate_7d, avg_events_per_user. Use window functions (e.g., partition by user, rolling windows) and avoid double-counting users across cohorts. 3) Extend your query to stratify by country and then produce a cohort-level weighted average controlling for country mix. 4) Briefly note two bias risks (e.g., survivorship, seasonality) and one SQL-side mitigation you implemented.
Schema (you may add a small date calendar CTE if needed):
users(user_id INT, signup_date DATE, country STRING)
events(user_id INT, event_date DATE, event_type STRING)
Sample rows:
users
user_id | signup_date | country
1 | 2025-08-15 | US
2 | 2025-06-10 | US
3 | 2025-08-30 | CA
4 | 2025-07-01 | IN
5 | 2025-08-20 | US
events
user_id | event_date | event_type
1 | 2025-08-29 | view
1 | 2025-09-01 | message
2 | 2025-08-25 | like
2 | 2025-08-31 | view
3 | 2025-09-01 | view
4 | 2025-08-28 | view
5 | 2025-08-31 | comment
Overview: This question evaluates a candidate's competency in cohort definition, time-series event aggregation, use of window functions, cohort-level weighting and bias-aware analytics using SQL and Python.
Read the full Meta Data Scientist interview experience this question came from
New vs Existing Cohorts: 7-day Rolling Active Rate and Daily Events per User
Assume today is 2025-09-01. Analyze the 28-day period FROM 2025-08-05 TO 2025-09-01 (inclusive).
Use the following mutually exclusive cohort definition:
- 'existing' users: signup_date < 2025-08-05
- 'new' users: signup_date BETWEEN 2025-08-05 AND 2025-09-01
Define a user as "active" on a given date if they generated at least one event on that date.
Write a single SQL query (you may use CTEs, including a date calendar CTE) that produces a daily cohort-level table with the following columns:
- date
- cohort (existing/new)
- active_users: number of active users on that date (distinct users)
- total_users_observed: number of users in the cohort who have signed up on or before that date (to adjust for partial observation windows)
- active_rate_7d: 7-day rolling active rate = (number of users with >=1 event in the 7-day window ending on date) / total_users_observed
- avg_events_per_user: total events on that date / total_users_observed
Requirements:
- Use window functions to compute the 7-day rolling activity per user.
- Avoid double-counting users across cohorts.
- Output one row per date per cohort for all dates 2025-08-05 through 2025-09-01.
- For cohort-date combinations with total_users_observed = 0, return NULL for rate/average metrics (avoid divide-by-zero).
Tables
users(user_id INT, signup_date DATE, country VARCHAR(2))
events(user_id INT, event_date DATE, event_type VARCHAR(20))
Hints
- Create a calendar of dates (2025-08-05..2025-09-01), then build a user-date grid where date >= signup_date to handle partial observation.
- Compute a per-user daily active flag (0/1), then use a 7-row window (ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) to mark whether a user was active in the last 7 days.
Country-Standardized Cohort Metrics (Weighted by Overall Country Mix)
You are analyzing daily user activity for two signup cohorts and want to compare them while **controlling for differences in country mix** between the cohorts. There are two tables:
- **`users`** — one row per user: `user_id`, `signup_date`, `country` (2-letter code).
- **`events`** — one row per user action: `user_id`, `event_date`, `event_type`.
**Cohort definition** (assign every user to exactly one cohort by signup date):
- `existing` — `signup_date < 2025-08-05`
- `new` — `signup_date BETWEEN 2025-08-05 AND 2025-09-01`
**Observation window.** A user is *observed* on every calendar day from their `signup_date` through `2025-09-01` (a user contributes one row per (date, user) and must be counted only once per date). Build daily activity over the window `2025-08-05` through `2025-09-01`.
**Per-user-day metrics.**
- `events_cnt` = number of events that user generated on that date (0 if none).
- A user is **7-day active** on a date if they generated at least one event in the trailing 7-day window (the date itself plus the 6 preceding observed days). Compute this with a window function (`ROWS BETWEEN 6 PRECEDING AND CURRENT ROW` over each user's ordered days).
For **report dates 2025-08-30, 2025-08-31, and 2025-09-01 (inclusive)** produce country-standardized cohort metrics:
1. **Per (date, cohort, country)** compute:
- `total_users_observed_country` = number of observed users,
- `active_users_7d_country` = number of 7-day-active users,
- `total_events_country` = sum of `events_cnt`,
- `active_rate_7d_country` = `active_users_7d_country / total_users_observed_country`,
- `avg_events_per_user_country` = `total_events_country / total_users_observed_country`.
2. **Per (date, country)** compute a weight from **all observed users that date (both cohorts combined)**:
- `weight_country` = `total_observed_in_country / total_observed_all_countries`.
3. **Final output, one row per (date, cohort):**
- `standardized_active_rate_7d` = `SUM(active_rate_7d_country * weight_country)` over that date's countries,
- `standardized_avg_events_per_user` = `SUM(avg_events_per_user_country * weight_country)` over that date's countries.
**Output columns** (in this order): `date`, `cohort`, `standardized_active_rate_7d`, `standardized_avg_events_per_user`. Round both metrics to 6 decimal places. **Sort by** `date` ascending, then cohort with `existing` before `new`.
Tables
users(user_id INT, signup_date DATE, country VARCHAR(2))
events(user_id INT, event_date DATE, event_type VARCHAR(20))
Hints
- Build a per-(date,user) observation grid first (calendar CROSS/JOIN users from signup_date onward) so every user is counted exactly once per date.
- For the 7-day active flag, use SUM(is_active) OVER (PARTITION BY user_id ORDER BY activity_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) > 0.