Skip to content
>_sqlbuddy
mediumWindow FunctionsAggregationDate Functions

Seven-Day Rolling Average

Compute each sale's 7-day rolling average of amount, per account.

Start practice

Problem statement

Prompt

Given the sales table below, write a query that returns each sale with a rolling_avg column: the average of that account's amount over the current sale and the six preceding sales (a 7-row window), ordered by sale_date.

  • The window must reset per account.
  • Round rolling_avg to 2 decimal places with ROUND(..., 2).
  • Output columns: id, account_id, sale_date, amount, rolling_avg.
  • Order the result by account_id, then sale_date, then id.
  • sales.amount may be NULL; AVG ignores NULL values (the window still counts 7 rows, but NULLs don't contribute).

Example

idaccount_idsale_dateamount
112024-01-0110
212024-01-0220
312024-01-0330

Expected result (window has only 3 rows so far):

idaccount_idsale_dateamountrolling_avg
112024-01-011010
212024-01-022015
312024-01-033020

Constraints

  • sales.id is the primary key.
  • sales.amount is nullable.

Tips

  • AVG(amount) OVER (PARTITION BY account_id ORDER BY sale_date, id ROWS BETWEEN 6 PRECEDING AND CURRENT ROW).
  • The frame is row-based: it averages the last 7 _rows_, not 7 calendar days.

Schema

CREATE TABLE sales (
    id         INTEGER PRIMARY KEY,
    account_id INTEGER NOT NULL,
    sale_date  DATE NOT NULL,
    amount     INTEGER  -- NULL allowed
);

Sample data

INSERT INTO sales (id, account_id, sale_date, amount) VALUES
  (1, 1, '2024-01-01', 10),
  (2, 1, '2024-01-02', 20),
  (3, 1, '2024-01-03', 30),
  (4, 1, '2024-01-04', 40),
  (5, 2, '2024-01-01', 5),
  (6, 2, '2024-01-02', 15),
  (7, 2, '2024-01-03', 25);

Additional hidden fixtures are applied during validation to test edge cases.

Solution

One correct approach — try solving it yourself in the practice editor first, then compare. There is usually more than one valid solution.

-- Reference: 7-row rolling average per account.
SELECT id, account_id, sale_date, amount,
       ROUND(AVG(amount) OVER (
           PARTITION BY account_id
           ORDER BY sale_date, id
           ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ), 2) AS rolling_avg
FROM sales
ORDER BY account_id, sale_date, id;

Key concepts

  • AVG OVER
  • ROWS BETWEEN
  • PARTITION BY

Related questions

Learn more