Order Gaps - Products with Inactivity Exceeding 7 Days

Preview mode. Log in to edit, run, submit, and save progress.

Medium

Description

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

Database Schema (Inferred)

ProductOrders

Column Name	Example Value
id	1
product_id	101
order_date	2023-01-01

Example

ProductOrders

id	product_id	order_date
1	101	2023-01-01
2	101	2023-01-05
3	101	2023-01-20
4	101	2023-01-22
5	202	2023-02-01
6	202	2023-02-03
7	303	2023-03-01
8	303	2023-03-15
9	303	2023-03-16

Output

product_id	gap_start	gap_end	gap_days
101	2023-01-05	2023-01-20	15
303	2023-03-01	2023-03-15	14

Explanation:

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.