Problem
Retention analytics wants to understand how frequently customers re-order. Using the orders table, return customer_id, order_id, created_at, and days_since_prev (days since that customer's previous order, NULL for their first order), ordered by customer_id then created_at.
Schema
orders
| column | type |
|---|
| id | INTEGER |
| customer_id | INTEGER |
| amount | NUMERIC |
| created_at | DATE |
Sample Data
| id | customer_id | created_at |
|---|
| 1 | 1 | 2024-01-10 |
| 2 | 1 | 2024-01-25 |
| 3 | 1 | 2024-03-01 |
| 4 | 2 | 2024-02-01 |
| 5 | 2 | 2024-02-14 |
Expected Output
| customer_id | order_id | created_at | days_since_prev |
|---|
| 1 | 1 | 2024-01-10 | NULL |
| 1 | 2 | 2024-01-25 | 15 |
| 1 | 3 | 2024-03-01 | 35 |
| 2 | 4 | 2024-02-01 | NULL |
| 2 | 5 | 2024-02-14 | 13 |