Gaps-and-islands: segment a time series by gaps.
Define a customer 'session' as a run of orders where no two consecutive orders are more than 30 days apart. The first order starts a session; each gap > 30 days from the previous order starts a new session. For each customer, return the number of sessions. Columns: `customer_id`, `session_count`. Order by customer_id. Table: `ci_orders`.
| Column | Type | Key |
|---|---|---|
| order_id | INT | PK |
| customer_id | INT | |
| product_name | TEXT | |
| category | TEXT | |
| order_date | DATE | |
| revenue | NUMERIC |
Each hint you reveal reduces the XP you can earn. Try the query first.
LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) to get the prior order date.
Flag a row as a new-session start: (order_date - prev_date) > 30 OR prev_date IS NULL.
Cumulative SUM of the new-session flag OVER (PARTITION BY customer_id ORDER BY order_date) assigns a session_id.
Then GROUP BY customer_id and COUNT(DISTINCT session_id). Each of these steps belongs in its own CTE.