Order Gaps - Products with Inactivity Exceeding 7 Days
Preview mode. Log in to edit, run, submit, and save progress.
Order Gaps - Products with Inactivity Exceeding 7 Days
You are given a table of product orders. Each row records a product and the date an order was placed. Write a SQL query to find all consecutive order pairs for each product where the gap between them is strictly greater than 7 days. For each such gap return the product_id, the date of the order before the gap (gap_start), the date of the order after the gap (gap_end), and the gap length in days (gap_days). Return results ordered by product_id then gap_start. Table: ProductOrders
| Column Name | Type | Description |
|---|---|---|
| id | INT | Primary key |
| product_id | INT | Product identifier |
| order_date | DATE | Date the order was placed |
Examples
Use LAG() to get the previous order date per product, compute the day difference, then filter for gaps > 7. Product 202 only has a 2-day gap so it is excluded.
Approach hint
Start with a simple approach, explain the trade-off, then move toward a cleaner or more scalable solution.
Common mistake
Skipping assumptions, edge cases, or trade-offs can make an otherwise good answer feel incomplete.