Marketing teams track signup cohorts to measure the long-term value of customers acquired each month. A cohort is the group of customers who first signed up in the same calendar month; summing their lifetime orders reveals which acquisition periods produced the most revenue. Using the customers and orders tables, return cohort_month (the month the customers signed up, truncated to the first of the month) and revenue (total order revenue from that cohort), ordered by cohort_month.
customers
| column | type |
|---|---|
| id | INTEGER |
| name | TEXT |
| TEXT | |
| created_at | DATE |
orders
| column | type |
|---|---|
| id | INTEGER |
| customer_id | INTEGER |
| amount | NUMERIC |
| created_at | DATE |
customers
| id | name | created_at | |
|---|---|---|---|
| 1 | Alice | alice@example.com | 2024-01-05 |
| 2 | Bob | bob@example.com | 2024-01-20 |
| 3 | Carol | carol@example.com | 2024-02-10 |
orders
| id | customer_id | amount | created_at |
|---|---|---|---|
| 1 | 1 | 200.00 | 2024-02-01 |
| 2 | 1 | 150.00 | 2024-03-15 |
| 3 | 2 | 150.00 | 2024-03-10 |
| 4 | 3 | 300.00 | 2024-04-05 |
| cohort_month | revenue |
|---|---|
| 2024-01-01 | 500.00 |
| 2024-02-01 | 300.00 |
Alice (Jan cohort) placed orders of 200 + 150 = 350; Bob (Jan cohort) placed an order of 150 — total 500.00 for the January cohort. Carol (Feb cohort) placed one order of 300.00.