5 Window Traps That Lie
Basket-split LAG gaps, ghost orders, nondeterministic ROW_NUMBER, RANK gaps, unframed running totals: 5 window traps with fixes and SQL included.
1. The basket-split LAG trap
MIN(order_date - LAG(order_date)) per customer. In quick commerce ~8% of orders land within 30 minutes — forgotten coriander, not replenishment. Fix: exclude same-day gaps and use the median distribution, not MIN.
2. Ghost orders
Cancelled order re-placed 5 minutes later counts a logistics failure as loyalty. Fix: filter status = 'delivered' before windowing.
3. Nondeterministic ROW_NUMBER
Ties broken arbitrarily. Fix: always add a second ORDER BY key (e.g. order_id).
4. The RANK gap
Ranks 1,2,2,4 with exactly-3-rows required. Fix: switch to DENSE_RANK and disclose tie inflation.
5. The unframed running total
Defaults differ by dialect. Fix: state the frame explicitly — ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
Writing SQL is the easy 20%. Scoping what the metric means is the 80% that gets you hired. Drill windows on messy data at Topfolio Practice.
Related: Sql Rank Dense Rank Tie Traps · Sql Date Patterns Interview
Frequently Asked Questions
Why is MIN with LAG dangerous for repeat-purchase gaps?
Raw MIN captures the most extreme outlier — same-day basket splits and cancelled re-orders. Use the median distribution excluding same-day top-ups.
Do I need to filter before applying window functions?
Yes. Ghost orders (cancelled then re-ordered) and test rows must be filtered first, or the window faithfully computes over garbage.
What is a ghost order?
A cancelled order re-placed minutes later. Without a status filter, window math counts a logistics failure as customer loyalty.
Written by
Founder at Topfolio with 6+ years in data & analytics across JPMC, Ultrahuman, and high-growth startups. Sat on hiring panels, reviewed 500+ resumes, and writes practical SQL & data guides.
Related Articles
RANK vs DENSE_RANK: Tie-Traps
Top-3-per-group with ties: ROW_NUMBER vs RANK vs DENSE_RANK explained side by side, plus 5 tie-trap drills with full answer keys for interviews.
30 SQL Interview Questions, Free
The 30 SQL patterns analyst interviews repeat — JOINs, windows, GROUP BY traps, NULLs, dates, CTEs — indexed by pattern with live practice links.
4 GROUP BY Traps, Wrong vs Right
WHERE vs HAVING, nullable grouping keys, bare SELECT columns: 4 GROUP BY traps that run without errors yet fail take-homes, each with the fix.