Spot the subtle window-frame mistake that leaks the current row into a training feature.
Codesql
-- Goal: build a feature = average of a user's PAST order amounts
-- (must NOT include the current order, to avoid target leakage)
SELECT
user_id,
order_date,
amount,
AVG(amount) OVER (
PARTITION BY user_id
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS prior_avg_amount
FROM orders;
The team notices this 'prior average' feature is suspiciously predictive in training but useless in production. What is the bug?