Interview Drills · Window frames · ROWS vs RANGE
43. 3-transaction moving average
Concept: Window frames · ROWS vs RANGEshow
A window frame picks which rows an aggregate sees. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW means this row and the two physically before it. Without a frame clause, ORDER BY inside OVER defaults to RANGE UNBOUNDED PRECEDING: everything so far, plus every row tied with the current one (its 'peers').
Example (different data):
SELECT day, temp, AVG(temp) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7d FROM weather;
Your task
A spending-trend chart for account 102 smooths out spikes with a moving average over the last three card charges.
For account 102, return id, txn_date, amount and moving_avg: the average amount of this transaction and the two before it (fewer for the first two), ordering transactions by txn_date then id, rounded to 2 decimals. Sort by txn_date, id.
Starting Postgres in your browser…
Checking your access…