SQLRepPath
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960
00/60 solved

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…