When working with sales data, looking at daily sales figures individually does not always give you a clear picture of business performance.

For example, sales might increase on Monday, drop on Tuesday, and rise again on Wednesday. These daily changes can make it difficult to understand the overall trend.

This is where a Moving Average in SQL becomes useful.

A moving average helps smooth out daily fluctuations by calculating the average over a specific number of previous rows.

In this SQL challenge, let's see how to calculate a 7-row moving average using the AVG() window function in Oracle SQL.

The Business Scenario

Imagine you are working on a sales reporting project. Your manager wants to see the average sales for the current day and the previous six days.

You have a table named "daily_sales" containing daily sales information.

Sample Table: daily_sales

sales_date total_sales
01-Nov-2026 1000
02-Nov-2026 1500
03-Nov-2026 1200
04-Nov-2026 1800
05-Nov-2026 2000
06-Nov-2026 1700
07-Nov-2026 2100
08-Nov-2026 2400

Your SQL Challenge

Write an Oracle SQL query to:

  1. Display the sales date and total sales.
  2. Calculate the average sales for the current row and previous six rows.
  3. Use the AVG() window function.
  4. Arrange the calculation in sales date order.
  5. Round the moving average to two decimal places.

Think about it before checking the solution.

Which window frame would you use to include the current row and six preceding rows?

SQL Solution (Oracle)

SELECT
sales_date,
total_sales,
ROUND(
AVG(total_sales) OVER (
ORDER BY sales_date
ROWS BETWEEN 6 PRECEDING
AND CURRENT ROW
), 2
) AS moving_avg_7_days
FROM daily_sales
ORDER BY sales_date;

Expected Output

sales_date total_sales moving_avg_7_days
01-Nov-2026 1000 1000.00
02-Nov-2026 1500 1250.00
03-Nov-2026 1200 1233.33
04-Nov-2026 1800 1375.00
05-Nov-2026 2000 1500.00
06-Nov-2026 1700 1533.33
07-Nov-2026 2100 1614.29
08-Nov-2026 2400 1814.29

Understanding the SQL Query

Let's understand the important parts of this query.

  1. AVG(total_sales)

Calculates the average of sales amounts within the specified window.

  1. ORDER BY sales_date

Arranges the rows by sales date before calculating the moving average.

  1. ROWS BETWEEN 6 PRECEDING AND CURRENT ROW

This is the most important part of the query.

It tells SQL to consider:

  • The current row.
  • The six rows immediately before it.

Together, these make a maximum of seven rows.

  1. ROUND(..., 2)

Rounds the calculated average to two decimal places.

Important: For the first six rows, SQL uses the available rows because six preceding rows do not yet exist. Starting from the seventh row, the calculation uses seven rows.

Also, "ROWS" counts records, not calendar days. If your table has missing dates, this calculates a seven-row moving average rather than necessarily covering seven consecutive calendar days.

Where Is a Moving Average Used in Real Projects?

Moving averages are useful in several data analytics and reporting scenarios:

  • Daily and weekly sales trend analysis.
  • Revenue monitoring and business performance reports.
  • Customer activity analysis.
  • Financial data analysis.
  • Business intelligence dashboards.

They help analysts identify trends without getting distracted by every small daily fluctuation.

One More SQL Challenge

Suppose your manager now wants to calculate the average sales for the current row and previous 29 rows.

What would you change in the above SQL query to calculate a 30-row moving average?

Think about the window frame before answering.

Share your SQL query in the comments!

Learn More: SQL Window Functions and Analytic Functions

Want to practise more SQL window functions?

Explore this collection of SQL Window Functions and Analytic Functions interview questions, answers and examples:

https://www.sankalandtech.com/Tutorials/sql-plsql-faq-interview/window-functions-analytic-functions-sql-faq.html

SQL #OracleSQL #SQLChallenge #WindowFunctions #MovingAverage #DataAnalytics #LearnSQL #SQLInterview #DataEngineering