Rolling 7-Day Total
Hard · Window Functions
A window frame decides which rows a window function sees. ROWS counts rows, but RANGE works on the values of the ORDER BY column, so you can use a time span: RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW. Rows with the same timestamp as the current row are always included.
For every payment, compute the activity of the same account in the 7 days up to that payment: all payments with paid_at from exactly 7 days before up to paid_at (both ends included, and payments at the same moment count too).
Return id, account_id, paid_at, amount, week_total (sum of amount) and week_count (number of payments), sorted by account_id, paid_at, then id.