Learn Advanced Aggregations: Moving Averages

Moving averages are a powerful tool in finance for analyzing trends in time-series data. By smoothing out short-term fluctuations, moving averages help you identify underlying patterns and long-term trends in financial metrics such as sales, expenses, or stock prices. In financial analysis, moving averages are commonly used to monitor performance, forecast future values, and support decision-making by reducing the noise present in raw data.


              12345678
            
SELECT transaction_date, amount,
    AVG(amount) OVER (
        ORDER BY transaction_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3day
FROM transactions
WHERE category = 'Sales'
ORDER BY transaction_date;

The query above calculates a 3-day moving average of sales amounts using a SQL window function. The AVG(amount) OVER (...) part tells SQL to compute the average of the amount field over a specific window of rows. The clause ORDER BY transaction_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW defines a moving window that includes the current row and the two preceding rows, effectively creating a rolling average over three consecutive sales transactions. This approach helps you observe trends in sales while minimizing the impact of daily fluctuations.


              12345678
            
SELECT transaction_date, amount,
    SUM(amount) OVER (
        ORDER BY transaction_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM transactions
WHERE category = 'Expense'
ORDER BY transaction_date;

A running total, also known as a cumulative sum, is the total of a sequence of numbers that is updated each time a new number is added. In finance, running totals are essential for tracking account balances, monitoring cumulative expenses, or analyzing cash flow over time. By calculating running totals, you gain real-time insight into your financial position and can quickly spot trends or anomalies as new transactions occur.

Note

Other window functions useful in finance include ROW_NUMBER, which assigns a unique sequential number to rows; LAG and LEAD, which allow you to access data from previous or subsequent rows within the result set. These functions are invaluable for period-over-period comparisons, ranking, and advanced time-series analysis.

Everything was clear?

Thanks for your feedback!

Section 1. Chapter 3

Ask AI

Ask anything or try one of the suggested questions to begin our chat

Swipe to show menu


              12345678
            
SELECT transaction_date, amount,
    AVG(amount) OVER (
        ORDER BY transaction_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3day
FROM transactions
WHERE category = 'Sales'
ORDER BY transaction_date;


              12345678
            
SELECT transaction_date, amount,
    SUM(amount) OVER (
        ORDER BY transaction_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM transactions
WHERE category = 'Expense'
ORDER BY transaction_date;

Note

Everything was clear?

Thanks for your feedback!

Section 1. Chapter 3

Advanced Aggregations: Moving Averages

1. What is a moving average and why is it useful in financial analysis?

2. Which SQL clause is essential for calculating running totals?

3. What does the `OVER()` clause do in SQL window functions?

Advanced Aggregations: Moving Averages

1. What is a moving average and why is it useful in financial analysis?

2. Which SQL clause is essential for calculating running totals?

3. What does the `OVER()` clause do in SQL window functions?

Advanced Aggregations: Moving Averages

1. What is a moving average and why is it useful in financial analysis?

2. Which SQL clause is essential for calculating running totals?

3. What does the OVER() clause do in SQL window functions?

Advanced Aggregations: Moving Averages

1. What is a moving average and why is it useful in financial analysis?

2. Which SQL clause is essential for calculating running totals?

3. What does the OVER() clause do in SQL window functions?

3. What does the `OVER()` clause do in SQL window functions?

3. What does the `OVER()` clause do in SQL window functions?