Compute cohort GMV and payer rate with edge cases

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.

Cohort GMV and payer rate for Aug 2025 (handle refunds, invalid payments, and late payments)

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.

Table users

column	type	constraints
user_id	INTEGER	PRIMARY KEY
country	TEXT
created_at	TIMESTAMP	NOT NULL

Sample data:

country	created_at	user_id
US	2025-06-20 00:00:00	1
US	2025-07-10 00:00:00	2
IN	2025-08-02 00:00:00	3
US	2025-08-25 00:00:00	4
US	2025-07-28 00:00:00	5
BR	2025-08-15 00:00:00	6

Table events

column	type	constraints
user_id	INTEGER
event_ts	TIMESTAMP	NOT NULL
event_type	TEXT	NOT NULL

Sample data:

event_ts	event_type	user_id
2025-08-05 10:00:00	app_open	1
2025-08-10 09:00:00	app_open	2
2025-08-28 13:00:00	app_open	2
2025-08-03 08:30:00	app_open	3
2025-08-30 20:10:00	app_open	4
2025-08-12 12:00:00	app_open	5
2025-08-31 23:55:00	app_open	6

Table orders

column	type	constraints
order_id	INTEGER	PRIMARY KEY
user_id	INTEGER
order_ts	TIMESTAMP	NOT NULL
status	TEXT	NOT NULL

Sample data:

order_id	order_ts	status	user_id
101	2025-08-05 10:05:00	placed	1
102	2025-08-10 09:05:00	placed	2
103	2025-08-28 13:05:00	canceled	2
104	2025-08-03 08:35:00	placed	3
105	2025-08-30 20:15:00	placed	4
106	2025-08-12 12:05:00	placed	5
107	2025-08-31 23:58:00	placed	6

Table payments

column	type	constraints
payment_id	INTEGER	PRIMARY KEY
order_id	INTEGER	NOT NULL
amount	NUMERIC	NOT NULL
payment_ts	TIMESTAMP	NOT NULL
is_valid	BOOLEAN	NOT NULL

Sample data:

amount	is_valid	order_id	payment_id	payment_ts
50	true	101	201	2025-08-05 10:06:00
20	true	102	202	2025-08-10 09:06:00
15	true	103	203	2025-08-28 13:06:00
15	false	103	204	2025-08-28 13:06:00
30	true	104	205	2025-09-01 00:01:00
100	true	105	206	2025-08-30 20:16:00
40	true	106	207	2025-08-12 12:06:00
25	true	107	208	2025-09-01 00:10:00

Table refunds

column	type	constraints
refund_id	INTEGER	PRIMARY KEY
order_id	INTEGER	NOT NULL
amount	NUMERIC	NOT NULL
refund_ts	TIMESTAMP	NOT NULL

Sample data:

amount	order_id	refund_id	refund_ts
15	103	301	2025-08-29 10:00:00
100	105	302	2025-09-02 09:00:00
10	106	303	2025-08-20 12:00:00

Expected output

One row per signup cohort month in users, with August 2025 active users, payers (net Aug amount > 0), payer rate (NULL if no active users), and net August GMV (valid Aug payments minus Aug refunds), ordered by cohort_month.

active_users_aug	cohort_month	gmv_aug_usd	payer_rate_aug	payers_aug
1	2025-06-01	50.0	1.0000	1
2	2025-07-01	50.0	1.0000	2
3	2025-08-01	100.0	0.3333	1

Hints

1. 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.
2. 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.
3. Drive the output off the distinct cohort months and LEFT JOIN the aggregates so empty cohorts still appear; use a CASE to return NULL for payer_rate_aug when active_users_aug = 0.