Level 10 · Debug: customers reorder every two hours?

LAG looks at the previous row and LEAD at the next one. Both are only meaningful when “previous” and “next” stay inside the right group.

Spotting the bug

Run the gaps CTE on its own and look at a few rows in order: neighbouring rows belong to different customers. Without PARTITION BY, the window is the whole table sorted by time, so LEAD returns the date of the next order anyone placed. With about 10,800 completed orders over three years, that is usually the same day, hence the 0.1-day average.

Two signs give it away: a gap that is physically implausible, and a “has a next order” count that is almost every order. Only one order in the whole table has no successor in the starter’s window, while in reality each customer’s last order has none.

LAG and LEAD together

SELECT order_id,
       CAST(order_date AS DATE) AS order_day,
       LAG(CAST(order_date AS DATE)) OVER w AS prev_day,
       LEAD(CAST(order_date AS DATE)) OVER w AS next_day
FROM orders
WHERE status = 'completed' AND customer_id = 125
WINDOW w AS (PARTITION BY customer_id ORDER BY order_date, order_id);

For customer 125 this lists eight completed orders, from 2023-01-01 to 2025-04-20. The gaps range from 2 days (November 30 to December 2, 2024, around Black Friday) to almost 11 months, which is why an average over many customers is more useful than any single gap. Checking one customer like this, with the partition in place, is the fastest way to see whether a window behaves before you aggregate it.

Same window, two directions: LAG answers “how long since the last order”, LEAD “how long until the next one”. The named WINDOW clause avoids repeating the definition. On the first order prev_day is NULL, on the last one next_day is NULL.

Common mistakes

• No PARTITION BY on a per-entity window.
• Subtracting timestamps instead of dates: you get an interval with hours, not whole days.
• Filtering the period before the window. Restricting to 2025 first hides a December 2024 order that was a customer’s real previous order.

In SQL Server

LEAD and LAG are written the same way. For day differences use DATEDIFF(day, order_day, next_order_day); subtracting two dates directly does not work there. The WINDOW clause exists only from SQL Server 2022.