Example 1: Cumulative Sales

This SQL query calculates the cumulative sales amount over time, adding up sales amounts from the beginning of the dataset to each row’s date. It returns the sales date, the sales amount for that date, and the cumulative sales amount up to that date.

SELECT sales_date, 
       sales_amount, 
       SUM(sales_amount) OVER (ORDER BY sales_date 
                               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sales
FROM sales
ORDER BY sales_date;

Query Breakdown:

1. SELECT Clause:
   • sales_date: Retrieves the date of the sales transaction.
   • sales_amount: Retrieves the amount of sales for that specific date.
   • SUM(sales_amount) OVER (ORDER BY sales_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sales: This is the window function calculating the cumulative sales.
2. Window Function Explanation:
   • SUM(sales_amount): This is the window function used to sum the sales amounts.
   • OVER: Defines the window of rows for the function.
     • ORDER BY sales_date: Orders the rows by sales_date. This ordering is crucial for calculating the cumulative total in the correct sequence.
     • ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: This frame clause specifies the range of rows for each calculation.
       • UNBOUNDED PRECEDING: Starts the frame at the first row of the partition.
       • CURRENT ROW: Ends the frame at the current row.
3. FROM Clause:
   • FROM sales: Specifies the table from which to retrieve the data.
4. ORDER BY Clause:
   • ORDER BY sales_date: Orders the final result set by sales_date to ensure that the cumulative totals are presented in chronological order.

Detailed Step-by-Step Execution

Initial State: Assume the sales table has the following data

Processing Each Row:

• First Row (2024-01-01):
  • Cumulative Sales = 100 (Sum of sales_amount from the start to 2024-01-01)
• Second Row (2024-01-02):
  • Cumulative Sales = 100 + 150 = 250
• Third Row (2024-01-03):
  • Cumulative Sales = 100 + 150 + 200 = 450

Final Output: