Write SQL to compare social-only vs game-only engagement
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
You are given two tables capturing Oculus app usage. Define an 'active day' as a UTC date on which a user generates at least one event. Consider only the window 2025-07-01 through 2025-08-31 (inclusive). Define 'social-only' users as those whose every event in this window has category = 'social' (no events in 'game' or any other category during the window). Define 'game-only' analogously. A 'regularly engaged week' is a Monday–Sunday week with active_days >= 3. Write ANSI SQL that outputs one row per cohort with: cohort ('social_only' or 'game_only'), number_of_users, avg_weekly_active_days_per_user (average across all user-weeks in the window for users in the cohort), and pct_regular_weeks (fraction of user-weeks in the cohort with active_days >= 3). Treat weeks that partially fall outside the window by counting only days inside the window; exclude users with zero events in the window; exclude users who have events in multiple categories or in categories outside {'social','game'}. Use the schema and small sample below to illustrate your approach.
Schema:
- users(user_id INT, signup_date DATE)
- events(user_id INT, event_time TIMESTAMP, category STRING, event_type STRING)
Sample (UTC):
users
user_id | signup_date
1 | 2025-06-28
2 | 2025-07-05
3 | 2025-07-10
4 | 2025-07-02
5 | 2025-08-01
events
user_id | event_time | category | event_type
1 | 2025-07-03 10:00:00 | social | view
1 | 2025-07-03 12:00:00 | social | post
1 | 2025-07-04 09:00:00 | social | view
2 | 2025-07-06 14:00:00 | game | launch
2 | 2025-07-07 15:00:00 | game | score
2 | 2025-07-09 19:00:00 | game | launch
3 | 2025-07-12 11:00:00 | social | view
3 | 2025-07-13 11:00:00 | game | launch
4 | 2025-07-15 09:00:00 | social | view
4 | 2025-07-16 09:00:00 | social | message
5 | 2025-08-10 08:00:00 | game | launch
5 | 2025-08-12 08:00:00 | other | purchase
Overview: This question evaluates SQL proficiency in data manipulation tasks such as cohorting, time-window filtering, date/week bucketing, categorical inclusion/exclusion, and computing per-user and per-week engagement metrics.
Read the full Meta Data Scientist interview experience this question came from
You are given two tables capturing Oculus app usage. Define an 'active day' as a UTC date on which a user generates at least one event.
Consider only events in the window 2025-07-01 through 2025-08-31 (inclusive).
Cohorts:
- A user is 'social_only' if EVERY event they generate in this window has category = 'social'.
- A user is 'game_only' if EVERY event they generate in this window has category = 'game'.
Exclusions:
- Exclude users with zero events in the window.
- Exclude users who have events in multiple categories during the window.
- Exclude users who have events in categories outside {'social','game'} during the window.
Weekly engagement:
- A 'week' is Monday–Sunday.
- A 'regularly engaged week' is a user-week with active_days >= 3.
- For weeks that partially fall outside the window, count only active days inside the window (i.e., only consider events in the window).
Task: Write ANSI SQL that outputs one row per cohort with:
- cohort ('social_only' or 'game_only')
- number_of_users
- avg_weekly_active_days_per_user (average active_days across all user-weeks in the window for users in the cohort)
- pct_regular_weeks (fraction of user-weeks in the cohort with active_days >= 3)
Return only cohorts that exist in the data.
Tables
users(user_id INT, signup_date DATE)
events(user_id INT, event_time TIMESTAMP, category VARCHAR(20), event_type VARCHAR(30))
Hints
- First filter events to the date window, then determine eligibility by enforcing exactly one distinct category per user and restricting it to ('social','game').
- Compute week start as the Monday of the event_date (ISO day-of-week) and then count distinct active dates per user-week.
Community answers
Answer by SS
WITH filtered_events AS (
SELECT
user_id,
DATE(event_time) AS ds,
DATE_TRUNC('week', DATE(event_time)) AS week_start,
category
FROM events
WHERE DATE(event_time) BETWEEN DATE '2025-07-01' AND DATE '2025-08-31'
),
-- Step 1: valid cohort users
user_cohort AS (
SELECT
user_id,
MIN(category) AS category,
COUNT(DISTINCT category) AS distinct_categories
FROM filtered_events
GROUP BY user_id
HAVING COUNT(DISTINCT category) = 1
AND MIN(category) IN ('social', 'game')
),
cohorted_events AS (
SELECT f.*
FROM filtered_events f
JOIN user_cohort u
ON f.user_id = u.user_id
),
-- Step 2: active days per user-week
user_week AS (
SELECT
user_id,
week_start,
COUNT(DISTINCT ds) AS active_days
FROM cohorted_events
GROUP BY user_id, week_start
),
-- Step 3: attach cohort label
user_week_cohort AS (
SELECT
uw.user_id,
uw.week_start,
uw.active_days,
uc.category AS cohort
FROM user_week uw
JOIN user_cohort uc
ON uw.user_id = uc.user_id
)
-- Step 4: final aggregation
SELECT
CASE
WHEN cohort = 'social' THEN 'social_only'
WHEN cohort = 'game' THEN 'game_only'
END AS cohort,
COUNT(DISTINCT user_id) AS number_of_users,
AVG(active_days) AS avg_weekly_active_days_per_user,
SUM(CASE WHEN active_days >= 3 THEN 1 ELSE 0 END) * 1.0
/ COUNT(*) AS pct_regular_weeks
FROM user_week_cohort
GROUP BY 1;