Compute cohort GMV and payer rate with edge cases
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
You are given the following schema (timestamps are UTC):
users(user_id INT, country STRING, created_at TIMESTAMP)
events(user_id INT, event_ts TIMESTAMP, event_type STRING) -- only app_open rows are relevant
orders(order_id INT, user_id INT, order_ts TIMESTAMP, status STRING) -- status in ('placed','canceled')
payments(payment_id INT, order_id INT, amount DECIMAL(10,2), payment_ts TIMESTAMP, is_valid BOOL)
refunds(refund_id INT, order_id INT, amount DECIMAL(10,2), refund_ts TIMESTAMP)
Small sample data:
users
+---------+---------+---------------------+
| user_id | country | created_at |
+---------+---------+---------------------+
| 1 | US | 2025-06-20 00:00:00 |
| 2 | US | 2025-07-10 00:00:00 |
| 3 | IN | 2025-08-02 00:00:00 |
| 4 | US | 2025-08-25 00:00:00 |
| 5 | US | 2025-07-28 00:00:00 |
| 6 | BR | 2025-08-15 00:00:00 |
+---------+---------+---------------------+
events (only app_open shown)
+---------+---------------------+-----------+
| user_id | event_ts | event_type|
+---------+---------------------+-----------+
| 1 | 2025-08-05 10:00:00 | app_open |
| 2 | 2025-08-10 09:00:00 | app_open |
| 2 | 2025-08-28 13:00:00 | app_open |
| 3 | 2025-08-03 08:30:00 | app_open |
| 4 | 2025-08-30 20:10:00 | app_open |
| 5 | 2025-08-12 12:00:00 | app_open |
| 6 | 2025-08-31 23:55:00 | app_open |
+---------+---------------------+-----------+
orders
+----------+---------+---------------------+----------+
| order_id | user_id | order_ts | status |
+----------+---------+---------------------+----------+
| 101 | 1 | 2025-08-05 10:05:00 | placed |
| 102 | 2 | 2025-08-10 09:05:00 | placed |
| 103 | 2 | 2025-08-28 13:05:00 | canceled |
| 104 | 3 | 2025-08-03 08:35:00 | placed |
| 105 | 4 | 2025-08-30 20:15:00 | placed |
| 106 | 5 | 2025-08-12 12:05:00 | placed |
| 107 | 6 | 2025-08-31 23:58:00 | placed |
+----------+---------+---------------------+----------+
payments
+------------+----------+--------+---------------------+----------+
| payment_id | order_id | amount | payment_ts | is_valid |
+------------+----------+--------+---------------------+----------+
| 201 | 101 | 50.00 | 2025-08-05 10:06:00 | 1 |
| 202 | 102 | 20.00 | 2025-08-10 09:06:00 | 1 |
| 203 | 103 | 15.00 | 2025-08-28 13:06:00 | 1 |
| 204 | 103 | 15.00 | 2025-08-28 13:06:00 | 0 | -- duplicate/invalid
| 205 | 104 | 30.00 | 2025-09-01 00:01:00 | 1 | -- late payment (Sep)
| 206 | 105 | 100.00 | 2025-08-30 20:16:00 | 1 |
| 207 | 106 | 40.00 | 2025-08-12 12:06:00 | 1 |
| 208 | 107 | 25.00 | 2025-09-01 00:10:00 | 1 | -- late payment (Sep)
+------------+----------+--------+---------------------+----------+
refunds
+-----------+----------+--------+---------------------+
| refund_id | order_id | amount | refund_ts |
+-----------+----------+--------+---------------------+
| 301 | 103 | 15.00 | 2025-08-29 10:00:00 |
| 302 | 105 | 100.00 | 2025-09-02 09:00:00 | -- refund after month-end
| 303 | 106 | 10.00 | 2025-08-20 12:00:00 |
+-----------+----------+--------+---------------------+
Task (one Standard SQL query): For calendar month 2025-08, output one row per signup cohort month (cohort_month = DATE_TRUNC(created_at, MONTH)) with: cohort_month, active_users_aug, payers_aug, payer_rate_aug, gmv_aug_usd. Rules and edge cases to handle precisely:
- Active users: distinct users in the cohort with at least one events.event_type = 'app_open' during 2025-08-01 to 2025-08-31 inclusive.
- GMV (gmv_aug_usd): sum of payments.amount where payments.is_valid = 1 and payment_ts in 2025-08, minus sum of refunds.amount where refund_ts in 2025-08, regardless of order status or order_ts. Do not include invalid payments (is_valid=0). GMV may be negative.
- Payers (payers_aug): among active users in the cohort, count distinct users whose net_august_amount = (valid August payments tied to their orders) minus (August refunds tied to their orders) is strictly > 0. A canceled order with payment and same-month full refund should not count as a payer.
- Denominator zero: if active_users_aug = 0, return payer_rate_aug = NULL (not 0).
- Ignore rows with NULL user_id anywhere.
- Late/early timing: payments/refunds outside August must not affect August metrics, even if the related order_ts is in August.
Return the exact SQL that produces the specified output.
Overview: This question evaluates a data scientist's competency in cohort-based GMV calculation, payer-rate measurement, and handling common edge cases across events, orders, payments, and refunds.
Read the full Meta Data Scientist interview experience this question came from
You are given five tables (all timestamps are UTC):
- `users(user_id, country, created_at)` — one row per user; `created_at` is signup time.
- `events(user_id, event_ts, event_type)` — product activity; the relevant event_type is `'app_open'`.
- `orders(order_id, user_id, order_ts, status)` — one row per order.
- `payments(payment_id, order_id, amount, payment_ts, is_valid)` — payments against orders; `is_valid = FALSE` marks an invalid payment.
- `refunds(refund_id, order_id, amount, refund_ts)` — refunds against orders.
**Task.** For the calendar month **August 2025** (the window `2025-08-01 00:00:00` inclusive up to but **not** including `2025-09-01 00:00:00`), produce **one row per signup-cohort month**, where the cohort month of a user is `DATE_TRUNC('month', users.created_at)` (returned as a date, e.g. `2025-06-01`). Include a cohort row for every distinct cohort month present in `users`, even if it has no active or paying users in August.
For each cohort month, return these columns (in this exact order and with these exact names):
1. `cohort_month` — the cohort's first-of-month date.
2. `active_users_aug` — count of distinct users in the cohort who have at least one `events.event_type = 'app_open'` whose `event_ts` falls in the August window.
3. `payers_aug` — among those **active** users, the count of distinct users whose **net August amount is strictly greater than 0**, where a user's net August amount = (sum of that user's **valid** payments with `payment_ts` in August) − (sum of that user's refunds with `refund_ts` in August), summed across all of the user's orders.
4. `payer_rate_aug` — `payers_aug / active_users_aug`, rounded to 4 decimal places; return **NULL** (not 0) when `active_users_aug = 0`.
5. `gmv_aug_usd` — for the whole cohort: (sum of `payments.amount` where `is_valid = TRUE` and `payment_ts` is in the August window) − (sum of `refunds.amount` where `refund_ts` is in the August window), aggregated over all of the cohort's users' orders. This is computed regardless of order status or `order_ts`.
**Rules / edge cases.**
- Ignore invalid payments (`payments.is_valid = FALSE`).
- Only the timestamp of the payment/refund matters for the August window — payments or refunds dated outside August must NOT affect August metrics, even if the related `order_ts` is in August.
- A canceled order that received a valid payment and a same-month full refund nets to 0 for that user, so it must NOT make the user a payer (net must be strictly > 0).
- Ignore rows with NULL `user_id` anywhere (`users.user_id`, `events.user_id`, `orders.user_id`).
Sort the result by `cohort_month` ascending. Write one PostgreSQL query.
Tables
users(user_id INTEGER, country TEXT, created_at TIMESTAMP)
events(user_id INTEGER, event_ts TIMESTAMP, event_type TEXT)
orders(order_id INTEGER, user_id INTEGER, order_ts TIMESTAMP, status TEXT)
payments(payment_id INTEGER, order_id INTEGER, amount NUMERIC, payment_ts TIMESTAMP, is_valid BOOLEAN)
refunds(refund_id INTEGER, order_id INTEGER, amount NUMERIC, refund_ts TIMESTAMP)
Hints
- Filter the August window on the payment/refund timestamp itself (a half-open `>= '2025-08-01' AND < '2025-09-01'` range), NOT on the order's timestamp or status — a late payment or a September refund must not affect August.
- Compute each user's net August amount = (valid Aug payments) - (Aug refunds) first; a payer is an ACTIVE user whose net is strictly > 0, so a payment that was fully refunded the same month nets to zero and is not a payer.