SQL Interview Prep · Lesson

Moving Averages Over a Sliding Window

Rolling N-period averages with BETWEEN PRECEDING AND CURRENT ROW.

Moving Averages Over a Sliding Window is a free SQL Interview Prep lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

The Moving Average Question

Analysts get asked this constantly: "Compute a 7-day moving average of revenue." A moving (or rolling) average smooths noisy daily data by averaging each point with its recent neighbors.

The interview-grade answer is a windowed AVG with an explicit sliding frame. The key skill is choosing the frame bounds so the window slides correctly along the order.

The Core Pattern

A moving average is AVG(value) OVER (ORDER BY ... ROWS BETWEEN n PRECEDING AND CURRENT ROW). The frame slides: at each row it covers the current row and the previous n rows.

For a 7-day window over daily rows, you go back 6 rows plus the current one, giving 7 rows total. Off-by-one here is the most common mistake interviewers catch.

SELECT
  sale_date,
  amount,
  AVG(amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7d
FROM daily_sales;

Counting the Window Size Correctly

The window size equals preceding + 1 (the +1 is the current row). So:

3-row window: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
7-row window: ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
30-row window: ROWS BETWEEN 29 PRECEDING AND CURRENT ROW

Say this arithmetic out loud in an interview so the panel sees you are deliberate, not guessing.

Centered vs Trailing Windows

The example above is a trailing average: it looks only backward, so it is causal and safe for forecasting dashboards.

A centered average looks both ways, e.g. ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING for a 7-row centered window. Centered windows smooth more symmetrically but cannot be computed for the most recent rows in real time. Mention which one the use case needs.

SELECT
  sale_date,
  AVG(amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING
  ) AS centered_avg_7
FROM daily_sales;

The Edge-of-Window Effect

At the very start of the data the full window does not exist yet. For row 1 of a 7-day trailing average, only 1 row is available, so AVG averages just that one value.

This means early rows show a "warm-up" average over fewer rows. Interviewers ask how to handle it. Two options: accept the partial window, or suppress early rows by requiring a full count.

Suppressing Partial Windows

If the business wants a NULL until a full window is available, use COUNT(*) over the same frame and blank out short windows with a CASE expression.

This is a polished touch that shows you thought about correctness at the boundaries, a detail many candidates skip.

SELECT
  sale_date,
  CASE WHEN COUNT(*) OVER w = 7
       THEN AVG(amount) OVER w
  END AS moving_avg_7d
FROM daily_sales
WINDOW w AS (
  ORDER BY sale_date
  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
);

Reusing a Frame With WINDOW

The previous query used a named WINDOW clause. When the same frame appears multiple times, defining it once as WINDOW w AS (...) and referencing OVER w keeps the query DRY and readable.

Postgres, MySQL 8, and SQL Server support named windows. Using one in an interview signals fluency beyond copy-paste window functions.

SELECT
  sale_date,
  AVG(amount) OVER w AS avg_7d,
  SUM(amount) OVER w AS sum_7d
FROM daily_sales
WINDOW w AS (
  ORDER BY sale_date
  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
);

Calendar-Days vs Rows Pitfall

A subtle trap: if the table has missing days, ROWS 6 PRECEDING spans the last 7 recorded rows, which may cover more than 7 calendar days.

For a true calendar-based 7-day average that respects gaps, use RANGE with an interval offset, or first join to a complete date series so every day has a row. Naming this distinction is exactly the ROWS-vs-RANGE insight from the prior lesson.

SELECT
  sale_date,
  AVG(amount) OVER (
    ORDER BY sale_date
    RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
  ) AS calendar_avg_7d
FROM daily_sales;

Per-Group Moving Averages

Just like running totals, moving averages usually need to reset per entity. Add PARTITION BY so each product or store gets its own rolling window that does not bleed across groups.

The frame and order operate independently inside each partition, so the first rows of every group correctly start their own warm-up.

SELECT
  product_id,
  sale_date,
  AVG(amount) OVER (
    PARTITION BY product_id
    ORDER BY sale_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS product_ma_7d
FROM daily_sales;

Smoothing in Action

Why bother? Daily revenue is spiky; a moving average reveals the trend. A common analyst answer pairs the raw value with its smoothed line so the dashboard shows both signal and noise.

You can also compare a short and long moving average (e.g. 7-day vs 30-day) to detect momentum, the SQL equivalent of a moving-average crossover.

SELECT
  sale_date,
  amount,
  AVG(amount) OVER (ORDER BY sale_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)  AS ma_7,
  AVG(amount) OVER (ORDER BY sale_date
    ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS ma_30
FROM daily_sales;

Interview Checklist

To nail a moving-average question, cover:

AVG OVER (ORDER BY ... ROWS BETWEEN n-1 PRECEDING AND CURRENT ROW).
Window size = preceding + 1.
Trailing vs centered choice.
Partial-window warm-up and how to suppress it.
ROWS vs RANGE when days are missing.
PARTITION BY to reset per entity.

Quick Check

Pick the frame for a 7-day trailing moving average over daily rows.

Recap: Moving Averages

A moving average is AVG(value) OVER (ORDER BY ... ROWS BETWEEN n-1 PRECEDING AND CURRENT ROW), where the window size is preceding rows plus one. Choose trailing or centered to match the use case, handle the warm-up rows with a COUNT guard, and switch to a RANGE interval when calendar gaps matter.

Reset per entity with PARTITION BY and reuse frames with a named WINDOW. Next, we turn cumulative sums into percentages with running share-of-total.

Frequently asked questions

Is the “Moving Averages Over a Sliding Window” lesson free?

Yes — the full text of “Moving Averages Over a Sliding Window” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “Moving Averages Over a Sliding Window”?

Rolling N-period averages with BETWEEN PRECEDING AND CURRENT ROW. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Moving Averages Over a Sliding Window” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

Cumulative Sums With Window Frames
ROWS vs RANGE Framing
Moving Averages Over a Sliding Window
Cumulative Distribution and Percent of Total

← Back to SQL Interview Prep