Attribute refunded revenue to acquisition cohorts
Description
Report lifetime net revenue by each customer's first settled-payment month. Tables: payments(id INTEGER PRIMARY KEY, customer_id TEXT, paid_at TEXT, status TEXT, cents INTEGER) and refunds(id INTEGER PRIMARY KEY, payment_id INTEGER, posted_at TEXT, status TEXT, cents INTEGER). All dates are valid YYYY-MM-DD strings. Payment status is settled, pending, or cancelled; refund status is posted or pending. IDs are unique, refunds reference existing payments, and cents are nonnegative integers. Only settled payments contribute revenue or determine a customer's cohort. The cohort is YYYY-MM from that customer's earliest settled paid_at. Subtract every posted refund referencing a settled payment, even when its posted_at falls in a later month. Attribute all settled payments and their posted refunds to the customer's original cohort. Do not join raw refund rows in a way that repeats payment revenue. Return every cohort with a settled payment, including zero or negative net revenue; customers without settled payments do not create a cohort. Output columns: cohort, net_cents. Sort cohort ascending. No data produces no rows. Use integer cents without rounding.
Examples
CREATE TABLE payments(id INTEGER PRIMARY KEY, customer_id TEXT NOT NULL, paid_at TEXT NOT NULL, status TEXT NOT NULL, cents INTEGER NOT NULL); CREATE TABLE refunds(id INTEGER PRIMARY KEY, payment_id INTEGER NOT NULL, posted_at TEXT NOT NULL, status TEXT NOT NULL, cents INTEGER NOT NULL); INSERT INTO payments VALUES (1,'a','2026-01-02','settled',100); INSERT INTO refunds VALUES (1,1,'2026-02-03','posted',30);2026-01|70The first settled payment puts the customer in January. Its February refund still reduces January cohort revenue from 100 to 70 cents.
CREATE TABLE payments(id INTEGER PRIMARY KEY, customer_id TEXT NOT NULL, paid_at TEXT NOT NULL, status TEXT NOT NULL, cents INTEGER NOT NULL); CREATE TABLE refunds(id INTEGER PRIMARY KEY, payment_id INTEGER NOT NULL, posted_at TEXT NOT NULL, status TEXT NOT NULL, cents INTEGER NOT NULL); INSERT INTO payments VALUES (1,'a','2026-01-02','settled',100),(2,'a','2026-02-02','settled',200); INSERT INTO refunds VALUES (1,2,'2026-03-01','posted',40);2026-01|260Both payments belong to the first settled-payment cohort, January. The March refund subtracts 40 cents, giving 100 + 200 - 40 = 260.
Constraints
- •
SQLite 3.27 compatible SELECT query; window functions and CTEs are available. - •
Return columns in the specified order; separate columns with the engine output format. - •
At most 500 rows per table; each cents value is at most 1,000,000. Use integer sums.
Ready to solve this problem?
Practice solo and sharpen your skills for technical interviews.