Cumulative Aggregates: Running Totals & Moving Averages0%

Value Boundary Functions: FIRST_VALUE, LAST_VALUE, NTH_VALUE

Cumulative Aggregates: Running Totals & Moving Averages

Advanced13 min read•Updated: 2026-09-12

Cumulative Aggregates: Running Totals & Moving Averages

Cumulative metrics and rolling averages are standard fixtures in financial dashboards, executive KPI trackers, and algorithmic data processing:

Year-To-Date (YTD) Revenue: Cumulative revenue that resets at the start of every calendar year.
Customer Lifetime Value (LTV) Progression: Cumulative spend tracked chronologically per user.
7-Day Exponential / Simple Moving Average: Smoothing short-term volatility in metrics like daily active users (DAU).

Step-by-Step Implementation: Year-to-Date (YTD) Running Total

1SELECT 
  YEAR(order_date) AS order_year,
  order_date,
  customer_id,
  amount,
  -- Running total resetting every year
  SUM(amount) OVER (
      PARTITION BY YEAR(order_date)
      ORDER BY order_date
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS ytd_cumulative_revenue
12FROM orders
13ORDER BY order_date;

Execution Trace:

PARTITION BY YEAR(order_date): Resets the cumulative accumulator at the start of 2025, 2026, etc.
ORDER BY order_date: Orders records chronologically.
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: Adds each row's amount to the sum of all preceding rows in the current year.

Calculating Trailing Moving Averages

Moving averages smooth volatile daily fluctuations to identify underlying business trends:

1SELECT 
  metric_date,
  daily_signups,
  -- 7-Day moving average of daily signups
  ROUND(
      AVG(daily_signups) OVER (
          ORDER BY metric_date
          ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
      ), 
      2
  ) AS moving_avg_7day,
  -- 30-Day moving average
  ROUND(
      AVG(daily_signups) OVER (
          ORDER BY metric_date
          ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
      ), 
      2
  ) AS moving_avg_30day
20FROM user_signup_metrics;

Moving Minimums & Maximums (Volatility Bands)

Beyond SUM and AVG, any standard aggregate function can be framed. For example, calculating price bands in fintech:

1SELECT 
  trade_timestamp,
  stock_ticker,
  price,
  MIN(price) OVER (
      PARTITION BY stock_ticker 
      ORDER BY trade_timestamp 
      ROWS BETWEEN 50 PRECEDING AND CURRENT ROW
  ) AS support_low_50,
  MAX(price) OVER (
      PARTITION BY stock_ticker 
      ORDER BY trade_timestamp 
      ROWS BETWEEN 50 PRECEDING AND CURRENT ROW
  ) AS resistance_high_50
15FROM stock_trades;

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Which frame clause correctly calculates a true cumulative Year-to-Date sum that adds all preceding rows in the year partition?

Question 2

How many total rows are included in AVG(x) OVER (ORDER BY d ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)?

Question 3

What resets the cumulative running total when calculating customer-specific progression?

Question 4

In moving average calculations, what is the effect of using a wider frame (e.g., 30 days vs 7 days)?

Question 5

Can MIN() and MAX() functions be combined with sliding window frames?

Next Lesson

Value Boundary Functions: FIRST_VALUE, LAST_VALUE, NTH_VALUE

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Sliding Window Frames: ROWS & RANGE	Value Boundary Functions: FIRST_VALUE, LAST_VALUE, NTH_VALUE

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Value Boundary Functions: FIRST_VALUE, LAST_VALUE, NTH_VALUE

Cumulative Aggregates: Running Totals & Moving Averages

Advanced13 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Cumulative Aggregates: Running Totals & Moving Averages

Cumulative metrics and rolling averages are standard fixtures in financial dashboards, executive KPI trackers, and algorithmic data processing:

Year-To-Date (YTD) Revenue: Cumulative revenue that resets at the start of every calendar year.
Customer Lifetime Value (LTV) Progression: Cumulative spend tracked chronologically per user.
7-Day Exponential / Simple Moving Average: Smoothing short-term volatility in metrics like daily active users (DAU).

Step-by-Step Implementation: Year-to-Date (YTD) Running Total

1SELECT 
  YEAR(order_date) AS order_year,
  order_date,
  customer_id,
  amount,
  -- Running total resetting every year
  SUM(amount) OVER (
      PARTITION BY YEAR(order_date)
      ORDER BY order_date
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS ytd_cumulative_revenue
12FROM orders
13ORDER BY order_date;

Execution Trace:

PARTITION BY YEAR(order_date): Resets the cumulative accumulator at the start of 2025, 2026, etc.
ORDER BY order_date: Orders records chronologically.
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: Adds each row's amount to the sum of all preceding rows in the current year.

Calculating Trailing Moving Averages

Moving averages smooth volatile daily fluctuations to identify underlying business trends:

1SELECT 
  metric_date,
  daily_signups,
  -- 7-Day moving average of daily signups
  ROUND(
      AVG(daily_signups) OVER (
          ORDER BY metric_date
          ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
      ), 
      2
  ) AS moving_avg_7day,
  -- 30-Day moving average
  ROUND(
      AVG(daily_signups) OVER (
          ORDER BY metric_date
          ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
      ), 
      2
  ) AS moving_avg_30day
20FROM user_signup_metrics;

Moving Minimums & Maximums (Volatility Bands)

Beyond SUM and AVG, any standard aggregate function can be framed. For example, calculating price bands in fintech:

1SELECT 
  trade_timestamp,
  stock_ticker,
  price,
  MIN(price) OVER (
      PARTITION BY stock_ticker 
      ORDER BY trade_timestamp 
      ROWS BETWEEN 50 PRECEDING AND CURRENT ROW
  ) AS support_low_50,
  MAX(price) OVER (
      PARTITION BY stock_ticker 
      ORDER BY trade_timestamp 
      ROWS BETWEEN 50 PRECEDING AND CURRENT ROW
  ) AS resistance_high_50
15FROM stock_trades;

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Which frame clause correctly calculates a true cumulative Year-to-Date sum that adds all preceding rows in the year partition?

Question 2

How many total rows are included in AVG(x) OVER (ORDER BY d ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)?

Question 3

What resets the cumulative running total when calculating customer-specific progression?

Question 4

In moving average calculations, what is the effect of using a wider frame (e.g., 30 days vs 7 days)?

Question 5

Can MIN() and MAX() functions be combined with sliding window frames?

Next Lesson

Value Boundary Functions: FIRST_VALUE, LAST_VALUE, NTH_VALUE

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Sliding Window Frames: ROWS & RANGE	Value Boundary Functions: FIRST_VALUE, LAST_VALUE, NTH_VALUE

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next