Interview Question Bank · RANGE window with INTERVAL · daily pre-aggregation
9. Days over a rolling 7-day spending limit
HardBankingQuestion #272
Concept: RANGE window with INTERVAL · daily pre-aggregationshow
A rolling N-day total is a window over dates, not over rows: SUM(x) OVER (PARTITION BY id ORDER BY day RANGE BETWEEN INTERVAL 'N-1 days' PRECEDING AND CURRENT ROW). ROWS counts rows, which goes wrong as soon as some days are missing. Aggregate to one row per id and day first, so several events on one day become one total and the output has one row per day. Filter on the window result in an outer query, since WHERE can't see window functions.
Example (different data):
WITH daily AS (
SELECT store_id, sale_date, SUM(amount) AS sales
FROM sales GROUP BY store_id, sale_date
)
SELECT store_id, sale_date,
SUM(sales) OVER (PARTITION BY store_id ORDER BY sale_date
RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW) AS sales_3d
FROM daily;Your task
Ashgrove Federal sets a weekly spending limit on each account and wants every day on which an account's debits over the trailing 7 days (that day plus the 6 before it) went over the limit. Some days have several debits, and many days have none.
For each account and each date with at least one debit, rolling_7d_spend is the total debited from 6 days before that date through that date. Return account_id, debit_date, rolling_7d_spend and weekly_limit for the days where rolling_7d_spend is greater than weekly_limit, one row per account and date. Sort by account_id, then debit_date.
Starting Postgres in your browser…
Checking your access…