Meta·Data Scientist·Online Assessment (OA)·Senior

Senior

Jun 2026Remote

Summary

Meta DS interview, technical phone screen or OA style, one big SQL problem covering cohort analysis with a bunch of edge cases baked in. The schema was realistic and the question had enough gotchas that you couldn't just wing it.

Questions Asked (1)

Given a multi-table schema (users, events, orders, payments, refunds), write a single SQL query that produces one row per signup cohort month for August 2025, reporting active users, payers, payer rate, and GMV. The query must handle invalid payments, same-month refunds that cancel out a payer, late payments landing outside the month, a NULL-safe denominator, and NULL user_ids.

Product Analytics & MetricsData Modeling

Author's notes

This one took me longer than I expected to outline before writing a single line.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the definitions of active users, payers, payer rate, and GMV, and confirm the handling of edge cases like invalid payments, same-month refunds, late payments, NULL user_ids, and NULL-safe denominators. Then structure the query using CTEs to compute each metric separately, ensuring correct joins and filters, and finally combine them into a single query that outputs one row per signup cohort month for August 2025.

Pro tip: Explicitly state your assumptions about business definitions (e.g., what constitutes an active user, how refunds affect GMV) and edge cases before writing the query; this demonstrates product sense and prevents misinterpretation.

1. Clarify metric definitions and edge cases

Define active users, payers, payer rate, and GMV, and confirm how to handle invalid payments, same-month refunds, late payments, NULL user_ids, and NULL-safe denominators.

2. Identify relevant tables and filters

Determine which tables (users, events, orders, payments, refunds) are needed and apply filters for signup cohort month (August 2025) and activity/payment periods.

3. Compute each metric using CTEs

Write separate CTEs to calculate active users, payers (after adjusting for refunds and invalid payments), and GMV (net of refunds), ensuring proper joins and NULL handling.

4. Combine metrics and calculate payer rate

Join the CTEs on signup cohort month and compute payer rate as payers divided by active users, using NULLIF or COALESCE to avoid division by zero.

5. Finalize query and validate logic

Assemble the final SELECT statement, add comments for clarity, and mentally test with edge cases to ensure correctness.

Key Points to Mention

Definition of active users: likely distinct users with at least one event in the month, excluding NULL user_ids.
Payer definition: users with at least one valid payment in the month, but same-month refunds may cancel out the payment, so net payers should be considered.
GMV calculation: sum of valid payment amounts minus refunds, ensuring refunds are matched to payments and handled within the same month.
Handling invalid payments: filter out payments with status indicating failure or invalidity.
Late payments: payments that occur after the cohort month should not count towards that month's metrics; only payments within the month are considered.
NULL-safe denominator: use NULLIF or COALESCE to prevent division by zero when calculating payer rate.

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