LAG window function + MAX aggregation to find the longest gap.
For each customer, find the longest gap (in days) between any two consecutive orders. Columns: `customer_id`, `longest_gap_days`. Order by longest_gap_days descending. 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 each order's previous order date.
Wrap in a CTE, then GROUP BY customer_id and MAX(order_date - prev_date).
Customers with only one order have no gap — filter WHERE prev_date IS NOT NULL in the outer query before aggregating.