Meta·Data Scientist·Technical Phone Screen·Senior

Senior

Jun 2026

Summary

Meta Data Scientist technical screen, heavy SQL with a lot of edge case traps baked into one problem. The question looked like a cohort analysis but there were about five different places to get it wrong quietly.

Questions Asked (1)

Given two tables of Oculus app usage data, write ANSI SQL to classify users as 'social-only' or 'game-only' within a two-month window, then compute per-cohort metrics: number of users, average weekly active days per user, and the fraction of user-weeks where active days hit at least 3. Partial weeks at the window boundary should only count days inside the window, and any user with events in multiple categories or outside the two allowed categories should be excluded entirely.

Data ModelingProduct Analytics & MetricsAlgorithms & Data Structures

Author's notes

The filtering logic alone took me a while to think through clearly.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Break the problem into three phases: first, filter and classify users based on their activity within the two-month window, excluding any user with events in multiple categories or outside the allowed categories; second, generate a user-week spine and compute active days per user-week, handling partial weeks at the boundaries; third, aggregate per cohort to get user count, average weekly active days per user, and the fraction of user-weeks with at least 3 active days. Use CTEs and window functions to keep the logic modular and readable.

Pro tip: Explicitly state your assumptions about the data schema (e.g., event timestamps, category values) and clarify how you handle edge cases like users with no activity in a week or partial weeks; this shows you think about data quality and metric definitions, which is critical for product analytics roles.

1. Filter and classify users

Identify users who have events only in one allowed category (e.g., 'social' or 'game') within the two-month window, excluding any user with events in multiple categories or outside the allowed categories. Use a GROUP BY with HAVING COUNT(DISTINCT category) = 1 and filter for allowed categories.

2. Generate user-week activity

Create a spine of all weeks in the window and join with user events to compute active days per user per week. For partial weeks at the boundaries, only count days that fall within the window. Use date functions to truncate event timestamps to week and count distinct active days.

3. Compute per-user metrics

For each user, calculate total active days, number of weeks active (or total weeks in window?), and average weekly active days. Also flag weeks where active days >= 3. Be careful to define 'weekly active days per user' as total active days divided by number of weeks in the window (or weeks with activity?)—clarify with the interviewer.

4. Aggregate per cohort

Group by cohort (social-only vs game-only) to compute: number of users (COUNT DISTINCT user_id), average weekly active days per user (AVG of per-user averages), and fraction of user-weeks with >=3 active days (SUM of weeks with >=3 / total user-weeks).

Key Points to Mention

Use of CTEs for modularity and readability
Handling of partial weeks at window boundaries by restricting to days within the window
Exclusion logic: users with events in multiple categories or outside allowed categories
Definition of 'active day' (e.g., at least one event on that day)
Calculation of fraction of user-weeks with >=3 active days: numerator and denominator definitions
Potential need for a date spine to account for weeks with zero activity

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.