SQL Quiz: Running Totals And Moving Averages
10 questions · exam conditions
0:00
Running Totals And Moving AveragesQuestion 1 of 10

A query returns one row per month, and month_start is unique. The required metric is the average revenue for the current month and the two immediately preceding months.

Which window expression correctly computes the required three-month moving average?

AVG(revenue) OVER (ORDER BY month_start ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
AVG(revenue) OVER (ORDER BY month_start ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
AVG(revenue) OVER (ORDER BY month_start ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING)
AVG(revenue) OVER (ORDER BY month_start ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING)
← Back to quizzes

SQL Quiz

SQL Quiz: Running Totals And Moving Averages

Practice Running Totals And Moving Averages in SQL with focused quiz questions that help you check what you know, review explanations, and build confidence with test-style prompts.

What this quiz covers

This quiz focuses on Running Totals And Moving Averages, giving you a quick way to practice the rules, question types, and explanations that matter most for SQL.

How to use this quiz

Try each quiz question before looking at the correct answer. Use the explanations to review missed ideas, then come back to similar questions until the pattern feels familiar.

All questions

Question 1

A query returns one row per month, and month_start is unique. The required metric is the average revenue for the current month and the two immediately preceding months.

Which window expression correctly computes the required three-month moving average?

  1. AVG(revenue) OVER (ORDER BY month_start ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
  2. AVG(revenue) OVER (ORDER BY month_start ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) (correct answer)
  3. AVG(revenue) OVER (ORDER BY month_start ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING)
  4. AVG(revenue) OVER (ORDER BY month_start ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING)
Explanation: When working with SQL window functions, the key is understanding how ROWS BETWEEN boundaries define exactly which rows are included in the frame — and that boundary counts are inclusive. For a three-month moving average, you want the current row plus the two rows immediately before it — three rows total. The clause ROWS BETWEEN 2 PRECEDING AND CURRENT ROW captures exactly that: start two rows back, end at the current row. Since both endpoints are included, that's positions −2, −1, and 0 — three months. This makes B the correct answer. Here's why the other options miss the mark. A uses 3 PRECEDING, which includes the current row plus three prior rows — four rows total, producing a four-month average, not three. Off by one is the most common trap in frame specification. C uses ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING, which deliberately excludes the current row — you'd get the average of only the two months before the current one, leaving out the very month you're calculating for. D reverses direction entirely with CURRENT ROW AND 2 FOLLOWING, computing a forward-looking average over the current month and the next two — useful in other contexts, but the opposite of what's asked here. A reliable rule of thumb: for an N-month moving average that includes the current period, always use ROWS BETWEEN (N-1) PRECEDING AND CURRENT ROW. Plug in N=3, and you get 2 PRECEDING AND CURRENT ROW every time. Watch for off-by-one errors — they are the most frequently tested trap in window frame questions.

Question 2

A report must show a three-observation moving average, but only after three observations are available. Earlier rows must display NULL. Column observed_at is unique.

Which expression satisfies the requirement?

  1. CASE WHEN COUNT(*) OVER (ORDER BY observed_at ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) = 3 THEN AVG(value) OVER (ORDER BY observed_at ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) END (correct answer)
  2. CASE WHEN COUNT(*) OVER () >= 3 THEN AVG(value) OVER (ORDER BY observed_at ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) END
  3. CASE WHEN COUNT(*) OVER (ORDER BY observed_at ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) = 2 THEN AVG(value) OVER (ORDER BY observed_at ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) END
  4. CASE WHEN COUNT(*) OVER (ORDER BY observed_at ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) = 3 THEN AVG(value) OVER (ORDER BY observed_at ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) END
Explanation: When working with moving averages that should display NULL until enough rows exist, you need two things to align: a window frame that captures exactly the right number of preceding rows, and a guard condition that checks whether that frame is actually full. The key insight is that the window frame ROWS BETWEEN 2 PRECEDING AND CURRENT ROW holds at most 3 rows — the current row plus two before it. On the first row, only 1 row falls in that frame; on the second, only 2. So counting rows within that exact frame and checking = 3 tells you precisely when a full three-observation window is available. Answer A does exactly this: both the COUNT(*) guard and the AVG use the same ROWS BETWEEN 2 PRECEDING AND CURRENT ROW frame, so the condition is TRUE only when the average is computed over a complete window. Answer B is wrong because COUNT(*) OVER () counts all rows in the entire result set — it's a grand total, not a per-row frame count. If the table has 3 or more rows, this condition is TRUE for every row, including the first two, which would incorrectly return an average of fewer than three values. Answer C checks = 2 instead of = 3. A frame count of 2 means only two observations are present, which is one short of the requirement — so it would display a value prematurely and suppress the correct rows. Answer D uses ROWS BETWEEN 3 PRECEDING AND CURRENT ROW, a four-row frame. Checking = 3 on a four-row frame means you're displaying the average one row too early, before the window is truly full by the intended three-row definition. As a study tip: whenever a moving average question asks for conditional NULL display, match the frame definition identically in both the COUNT guard and the AVG — any mismatch between them is a classic trap.

Question 3

A payments table contains four rows for one account, ordered by payment_date: amounts 20, 35, 10, and 40. The dates are unique.

The following expression is evaluated for each row:

SUM(amount) OVER (ORDER BY payment_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

What value is returned for the fourth payment?

  1. 85, because the frame contains the second, third, and fourth payments (correct answer)
  2. 105, because the frame contains every payment through the fourth payment
  3. 50, because the frame contains only the third and fourth payments
  4. 65, because the frame contains the first, third, and fourth payments
Explanation: When you see a window function with ROWS BETWEEN, your job is to count physical rows — not values, not ranges of dates — relative to the current row. The frame boundary 2 PRECEDING AND CURRENT ROW means "start two rows back from me and end at my row," giving you a window of at most three rows. For the fourth payment (amount = 40), two rows preceding it are the second row (35) and third row (10). So the frame contains rows 2, 3, and 4, with amounts 35, 10, and 40. That sum is 85, confirming answer A is correct. Answer B (105) would be the result of summing all four payments (20 + 35 + 10 + 40), which would only happen with a frame like UNBOUNDED PRECEDING AND CURRENT ROW. Don't confuse ROWS BETWEEN 2 PRECEDING with "all prior rows." Answer C (50) contains only the third and fourth payments (10 + 40), as if the frame were 1 PRECEDING AND CURRENT ROW. This is an off-by-one error — 2 PRECEDING reaches back two rows, not one. Answer D (65) suggests skipping the second row and using the first (20), third (10), and fourth (40). ROWS BETWEEN always selects a contiguous block of rows; it never skips rows in the middle. A handy tip: when working through ROWS BETWEEN problems, physically number the rows in order and count backward from the current row. Writing out the row indices that fall inside the frame eliminates most errors before they happen.

Question 4

An events table has these rows: event 1 occurred on 2026-04-01 with amount 10; event 2 occurred on 2026-04-01 with amount 20; event 3 occurred on 2026-04-02 with amount 5. A standards-conforming database uses the default ordered window frame.

For the expression SUM(amount) OVER (ORDER BY event_date), which running-total values correspond to events 1, 2, and 3?

  1. 10, 30, and 35, because tied rows are processed separately by event number
  2. 30, 30, and 35, because tied dates are peers in the default frame (correct answer)
  3. 35, 35, and 35, because the omitted frame includes the full partition
  4. 10, 20, and 5, because the omitted frame contains only the current row
Explanation: When you see ORDER BY inside a window function without an explicit ROWS or RANGE clause, the SQL standard automatically applies a default frame of RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. The critical detail is what "current row" means in RANGE mode: it refers to all peer rows — rows sharing the same ORDER BY value — not just the physical row being processed. In this problem, events 1 and 2 both have event_date = 2026-04-01, making them peers. Under RANGE semantics, the frame for both of these rows extends from the beginning of the partition through the end of their peer group. That means both events see a sum of 10 + 20 = 30. Event 3 has a unique date, so its frame covers all three rows: 10 + 20 + 5 = 35. This confirms B as correct. Choice A is wrong because RANGE mode does not process tied rows separately by row number — that would be ROWS mode behavior, which requires an explicit ROWS keyword. Choice C describes the behavior of an unordered window (no ORDER BY at all), where the frame defaults to the entire partition; here we do have an ORDER BY, so the frame is cumulative, not full. Choice D describes ROWS BETWEEN CURRENT ROW AND CURRENT ROW, which isolates each physical row — a frame SQL would never silently apply by default. The key study tip: memorize the two default frame rules — no ORDER BY means the whole partition; with ORDER BY means RANGE UNBOUNDED PRECEDING, which expands peer groups together, not row by row.

Question 5

For each date, a report must first calculate a cumulative revenue total. It must then calculate a two-row moving average of those cumulative totals. Assume sale_date is unique.

Which query correctly performs the two window-calculation stages without nesting one window function directly inside another?

  1. SELECT sale_date, AVG(revenue) OVER (ORDER BY sale_date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS moving_avg, SUM(revenue) OVER (ORDER BY sale_date) AS running_total FROM sales
  2. SELECT sale_date, AVG(SUM(revenue) OVER (ORDER BY sale_date)) OVER (ORDER BY sale_date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS moving_avg FROM sales
  3. WITH r AS (SELECT sale_date, AVG(revenue) OVER (ORDER BY sale_date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS moving_avg FROM sales) SELECT sale_date, SUM(moving_avg) OVER (ORDER BY sale_date) AS running_total FROM r
  4. WITH r AS (SELECT sale_date, SUM(revenue) OVER (ORDER BY sale_date) AS running_total FROM sales) SELECT sale_date, running_total, AVG(running_total) OVER (ORDER BY sale_date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS moving_avg FROM r (correct answer)
Explanation: When a problem requires chained window calculations — where one window function depends on the output of another — you cannot compute both in a single SELECT. SQL evaluates all window functions at the same logical step, so you must complete the first calculation in one query layer before the second can reference it. A CTE (Common Table Expression) is the clean way to stage this. Option D does exactly this. The CTE r first computes SUM(revenue) OVER (ORDER BY sale_date), producing a running total for each date. The outer query then treats running_total as a plain column and applies AVG(running_total) OVER (ORDER BY sale_date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) — a two-row moving average of those cumulative values. The sequence matches what the problem requires, and no window function is nested inside another. Option A computes the moving average and running total in parallel from raw revenue, meaning the moving average is taken over individual daily revenues rather than cumulative totals — wrong order of operations. Option B attempts to nest SUM(...) OVER (...) directly inside AVG(...) OVER (...), which SQL does not permit; nesting window functions directly is a syntax error in standard SQL. Option C reverses the intended logic: it first calculates a moving average of raw revenue, then sums those averages cumulatively — the opposite of what the problem asks. As a study tip, whenever you see "first calculate X, then calculate Y over X," that's a signal to use a CTE or subquery. Think of it as building a pipeline: finish stage one, then pass its output to stage two.

Question 6

A sales result contains East-region amounts 10 and 20 on January 1 and January 2, followed by West-region amounts 7 and 8 on those same dates. The required metric is a running total that restarts for each region.

Which expression returns 10 and 30 for the East rows, and 7 and 15 for the West rows?

  1. SUM(amount) OVER (ORDER BY region, sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
  2. SUM(amount) OVER (PARTITION BY sale_date ORDER BY region ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
  3. SUM(amount) OVER (PARTITION BY region ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) (correct answer)
  4. SUM(amount) OVER (PARTITION BY region, sale_date ORDER BY amount ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
Explanation: When you see a question about running totals that "restart" per group, your immediate focus should be on the PARTITION BY clause — it's what divides the data into independent windows before the running sum is calculated. For a running total that resets with each region, you need PARTITION BY region. This tells SQL to treat East rows and West rows as completely separate windows. Within each partition, ORDER BY sale_date ensures the cumulative sum accumulates in chronological order. The frame ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW then sums from the first row of that partition up to the current row. For East: row 1 gives 10, row 2 gives 10+20=30. For West: row 1 gives 7, row 2 gives 7+8=15. That's exactly the target output, making C correct. A is wrong because it uses no PARTITION BY at all — the window spans the entire result set. The running total never resets; it just keeps accumulating across both regions. B partitions by sale_date instead of region. This groups January 1 rows together and January 2 rows together, so each date-based window contains one East and one West row — it doesn't produce a per-region running total. D partitions by both region and sale_date, which creates a separate window for every single row (each region-date combination is unique). With only one row per partition, every result is just the row's own amount — no accumulation happens at all. A useful rule of thumb: whatever grouping defines where your calculation restarts belongs in PARTITION BY; whatever defines the order of accumulation belongs in ORDER BY.

Question 7

A sales table can contain many transactions on the same date. A report needs one row per date showing total daily sales and the moving average of that daily total over the current date and the two preceding reported dates.

Which query most directly produces the required result?

  1. SELECT sale_date, SUM(amount) AS daily_total, AVG(amount) OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg FROM sales GROUP BY sale_date
  2. SELECT sale_date, amount, AVG(amount) OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg FROM sales
  3. WITH daily AS (SELECT sale_date, AVG(amount) AS daily_total FROM sales GROUP BY sale_date) SELECT sale_date, daily_total, SUM(daily_total) OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg FROM daily
  4. WITH daily AS (SELECT sale_date, SUM(amount) AS daily_total FROM sales GROUP BY sale_date) SELECT sale_date, daily_total, AVG(daily_total) OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg FROM daily (correct answer)
Explanation: When a question combines aggregation with window functions, you need to think in two distinct layers: first collapse the raw rows into the grain you need (one row per date), then apply your window calculation on top of that result. Mixing these layers incorrectly is the most common trap here. The right approach is D. It uses a CTE to first GROUP BY sale_date and compute SUM(amount) as the true daily total — collapsing every transaction on a given date into one row. Then the outer query applies AVG(daily_total) OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), which takes the average of the pre-summed daily totals across the current date and two preceding dates. That matches the requirement exactly. Option A fails because it applies AVG(amount) as a window function directly against the raw sales table while simultaneously using GROUP BY. Most databases will reject this or produce unexpected results — the window function sees the raw amount values, not the grouped daily totals, so the moving average is computed over individual transactions rather than daily sums. Option B skips the GROUP BY entirely, producing one row per transaction. The moving average would be meaningless — it averages raw transaction amounts rather than daily totals. Option C is close but uses SUM(daily_total) in the window function instead of AVG. A sum of sums is not a moving average; it would accumulate totals rather than average them. Study tip: When you see both aggregation and window functions, reach for a CTE to handle aggregation first, then apply your window function cleanly in the outer query — never try to do both in one SELECT on raw data.

Question 8

Daily sales are 10 on January 1, 20 on January 2, and 30 on January 3. A query uses SUM(sales) OVER (ORDER BY sale_date DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).

What running-total value is assigned to the January 2 row?

  1. 20, because only the current row belongs to the descending frame
  2. 30, because January 1 and January 2 are accumulated chronologically
  3. 50, because January 3 precedes January 2 in descending order (correct answer)
  4. 60, because an unbounded frame always includes the full partition
Explanation: When working with window functions, you must think carefully about two separate things: the ORDER BY direction and what the frame actually includes. These interact in ways that frequently trip up students. The key here is that ORDER BY sale_date DESC sorts the rows so January 3 comes first, January 2 comes second, and January 1 comes last. The frame ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW then accumulates every row from the top of that sorted order down to whichever row is currently being evaluated. For the January 2 row (the second row in descending order), the frame includes January 3 (the first row, value 30) and January 2 itself (value 20), producing a running total of 30+20=5030 + 20 = 50. That makes C the correct answer. Choice A is wrong because it confuses "current row" with "only the current row" — the frame always starts from UNBOUNDED PRECEDING, meaning everything above the current row is included, not just the row itself. Choice B describes a chronological (ascending) accumulation where January 1 and January 2 would combine, but the ordering here is explicitly descending, so January 3 precedes January 2 in the frame, not January 1. Choice D describes what would happen with a full-partition aggregate (like omitting the frame clause entirely or using ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), which would give every row the grand total of 60 — that's not what this frame does. Your study tip: always sketch the rows in the ORDER BY direction first, then apply the frame boundaries as if you're sliding a window down that sorted list. Direction changes everything.

Question 9

A sensor has readings of 10 on January 1, 20 on January 2, and 70 on January 10. A query calculates AVG(reading) OVER (ORDER BY reading_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW).

Which statement correctly describes the value calculated for January 10?

  1. It is approximately 33.33 because the frame uses the three most recent rows, despite the date gap (correct answer)
  2. It is 70 because only the January 10 row falls within the most recent three calendar days
  3. It is 45 because the frame includes January 2 and January 10 but excludes January 1
  4. It is NULL because a three-row frame cannot span more than three calendar days
Explanation: When working with window functions, the most important distinction to internalize is the difference between row-based and range-based frames. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW defines a physical frame — it counts exactly 2 rows before the current row, regardless of what values or dates those rows contain. For January 10, SQL looks backward two physical rows: January 1 (reading = 10), January 2 (reading = 20), and the current row January 10 (reading = 70). The average is simply 10+20+703≈33.33\frac{10 + 20 + 70}{3} \approx 33.33, making A correct. The calendar gap between January 2 and January 10 is completely irrelevant — the engine counts rows, not days. B is wrong because it confuses ROWS framing with date-range logic. Nothing in this query filters by calendar proximity; there is no RANGE BETWEEN INTERVAL '3 days' PRECEDING clause. C is wrong because it implies the frame skips January 1 due to the date gap — again, row-based frames don't work that way. All three rows are physically present and included. D is wrong on two counts: ROWS framing never produces NULL simply because of calendar spacing, and a three-row window spanning any time gap is perfectly valid SQL. A good study rule: whenever you see ROWS BETWEEN, mentally substitute "exactly N physical rows before me." Save the date-gap reasoning for RANGE BETWEEN, which operates on value differences, not row counts.

Question 10

A table has three chronologically ordered sales with amounts 20, 60, and 80. The following query is executed:

SELECT sale_date, amount, SUM(amount) OVER (ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM sales WHERE amount >= 50;

What running total is returned for the row whose amount is 80?

  1. 80, because only the current row is included in the frame
  2. 140, because the window operates only on the two rows that pass the filter (correct answer)
  3. 160, because the window is evaluated before the WHERE clause removes rows
  4. 100, because the frame retains the amounts 20 and 80 while skipping 60
Explanation: When working with window functions and WHERE clauses, the critical concept to understand is order of operations: the WHERE clause filters rows before the window function ever executes. This means your window function only "sees" the rows that survived filtering. In this query, the WHERE amount >= 50 eliminates the row with amount 20, leaving only the rows with amounts 60 and 80. The window function SUM(amount) OVER (ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) then runs on this reduced two-row result set. For the row with amount 80, the frame includes all preceding rows plus itself — that's 60 + 80 = 140. Answer B is correct. Answer A is wrong because ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW explicitly includes all rows from the start of the partition up to the current row, not just the current row alone. A frame of only the current row would require ROWS BETWEEN CURRENT ROW AND CURRENT ROW. Answer C represents the most tempting trap: the misconception that window functions are evaluated before WHERE filtering, giving 20 + 60 + 80 = 160. In reality, WHERE always executes before SELECT-level window functions — the window never sees the filtered-out row. Answer D is pure fabrication — there's no SQL mechanism that "skips" middle rows while retaining others in a standard cumulative frame. Study tip: Always remember the SQL logical processing order — WHERE → window functions. If a question involves both filtering and windowing, mentally remove the filtered rows first, then apply your window logic to whatever remains.