Retention wants to know how frequently each customer reorders so the team can time follow-up emails appropriately. Using the orders table, return customer_id and avg_days_between_orders (average number of days between consecutive orders, rounded to 1 decimal place) for customers with at least 2 orders, ordered by customer_id.
orders
| column | type |
|---|---|
| id | INTEGER |
| customer_id | INTEGER |
| amount | NUMERIC |
| created_at | DATE |
| id | customer_id | amount | created_at |
|---|---|---|---|
| 1 | 1 | 50.00 | 2024-01-01 |
| 2 | 1 | 75.00 | 2024-01-11 |
| 3 | 1 | 60.00 | 2024-01-21 |
| 4 | 2 | 100.00 | 2024-01-01 |
| 5 | 2 | 80.00 | 2024-01-16 |
| 6 | 3 | 200.00 | 2024-01-05 |
| customer_id | avg_days_between_orders |
|---|---|
| 1 | 10.0 |
| 2 | 15.0 |
Customer 1 has gaps of 10 days (Jan 1 → Jan 11) and 10 days (Jan 11 → Jan 21), averaging 10.0. Customer 2 has one gap of 15 days (Jan 1 → Jan 16). Customer 3 has only one order and is excluded.