Growth wants to separate acquisition from retention. Using the orders table, return order_month, customer_type ('new' or 'returning'), and customer_count (distinct customers of that type in that month), ordered by order_month then customer_type.
orders
| column | type |
|---|---|
| id | INTEGER |
| customer_id | INTEGER |
| amount | NUMERIC |
| created_at | DATE |
| id | customer_id | amount | created_at |
|---|---|---|---|
| 1 | 1 | 100.00 | 2024-01-05 |
| 2 | 2 | 200.00 | 2024-01-20 |
| 3 | 1 | 150.00 | 2024-02-10 |
| 4 | 3 | 75.00 | 2024-02-25 |
| order_month | customer_type | customer_count |
|---|---|---|
| 2024-01-01 | new | 2 |
| 2024-02-01 | new | 1 |
| 2024-02-01 | returning | 1 |
In January, customers 1 and 2 ordered for the first time — both are classified as 'new'. In February, customer 1 returns (classified 'returning') and customer 3 orders for the first time (classified 'new').