5 SQL window-function traps that show up in data engineering interviews (with runnable DuckDB examples)

1. The default frame for window functions in SQL is RANGE, not ROWS. This means that rows with the same order_date are considered peers and included in the frame. To explicitly define the frame, specify the range using ORDER BY and ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

2. Using ROW_NUMBER() without a tie-breaker can lead to non-deterministic results. If multiple orders have the same date, the order in which they are numbered is not guaranteed. To ensure consistent numbering, include a unique tie-breaker, such as order_id, in the ORDER BY clause.

3. The LAST_VALUE() function returns the current row's amount by default. To obtain the actual last amount, you need to specify the full frame using ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

4. The WHERE clause filters rows before window functions are applied. If you want to display data from a specific date range, compute the window function first, then apply the WHERE clause to filter the results. This ensures that all rows are considered before filtering based on the date.

5. When calculating the last 7 rows, using ROWS BETWEEN 6 PRECEDING AND CURRENT ROW counts the rows instead of days. If there are gaps in the data (no orders between Jan 2 and Jan 12), the window may include much older rows. To accurately calculate the last 7 days, use a time-based frame like RANGE BETWEEN INTERVAL 6 DAYS PRECEDING AND CURRENT ROW.