Meta·Data Scientist·Technical Phone Screen·Senior

Senior

Jun 2026Remote

Summary

Meta data scientist technical screen, one big SQL question that took up basically the whole session. The problem was more involved than I expected and I definitely fumbled some of the edge case handling under pressure.

Questions Asked (1)

Given session-level data, compare engagement between users who exclusively used the 'social' category versus those who exclusively used the 'game' category over a fixed 28-day window. Define two mutually exclusive cohorts, exclude mixed-category users and anyone with neither category, then compute per cohort: user count, median active days, share of users active 10+ days, and average minutes per active day. Write a single SQL query handling nulls and non-positive durations.

Product Analytics & MetricsData Modeling

Author's notes

This one took me a while to even parse correctly.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the data model and defining the 28-day window and active day. Then build two mutually exclusive cohorts using conditional aggregation to flag exclusive social or game users, excluding mixed and neither. Finally, compute the required metrics per cohort using CTEs for readability and handle nulls and non-positive durations with NULLIF and CASE.

Pro tip: Use a single pass over the session data with conditional aggregation to avoid multiple scans, and explicitly state how you handle edge cases like zero or negative durations and null user IDs, as this demonstrates production-ready SQL.

1. Clarify requirements and data assumptions

Confirm the definition of an active day (e.g., any session with positive duration), the 28-day window boundaries, and how to treat nulls and non-positive durations. State that you will exclude users with no sessions in either category.

2. Aggregate session data per user

Use a CTE to aggregate sessions per user, computing total active days (distinct dates with valid sessions) and total valid minutes (sum of durations where duration > 0). Filter out null user IDs and non-positive durations.

3. Define mutually exclusive cohorts

In another CTE, flag users who have only social sessions and users who have only game sessions. Exclude users who have both or neither by using conditional aggregation on category counts.

4. Compute per-cohort metrics

Join the cohort flags with the user-level aggregates, then compute user count, median active days (using PERCENTILE_CONT), percentage of users with 10+ active days, and average minutes per active day (total minutes / total active days, handling division by zero).

5. Format and validate results

Present the final query with clear column aliases and cohort labels. Mention that you would validate by checking cohort sizes and ensuring no user appears in both cohorts.

Key Points to Mention

Use of CTEs for step-by-step logic and readability
Conditional aggregation to create mutually exclusive cohorts (e.g., SUM(CASE WHEN category = 'social' THEN 1 ELSE 0 END) > 0 AND SUM(CASE WHEN category = 'game' THEN 1 ELSE 0 END) = 0)
Handling nulls and non-positive durations: filter WHERE duration > 0 and user_id IS NOT NULL, or use NULLIF to avoid division by zero
Median calculation using PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY active_days)
Average minutes per active day: total_minutes / NULLIF(active_days, 0)
Ensuring the 28-day window is applied consistently (e.g., session_date BETWEEN start_date AND start_date + INTERVAL '27 days')

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