Search⌘ K
AI Features
Home
Courses
SQL Interview Preparation – Advanced Level

Answer: Window Functions

Explore how to use window functions in MySQL to compute running totals and assign order ranks per customer. This lesson helps you understand partitioning, ordering within window functions, and their application with joins and aggregate functions. You will learn to write queries that generate cumulative sums and sequential numbering ordered by dates, enhancing reporting and analytical capabilities.

We'll cover the following...

Solution

The solution is given below:

MySQL 8.0
SELECT   c.CustomerID, c.CustomerName, o.OrderID, o.OrderDate, o.TotalAmount,
         SUM(o.TotalAmount) OVER (
            PARTITION BY c.CustomerID
            ORDER BY o.OrderDate
         ) AS RunningTotal,
         ROW_NUMBER() OVER (
            PARTITION BY c.CustomerID
            ORDER BY o.OrderDate
         ) AS OrderRank
FROM     Customers c
JOIN     Orders o
         ON c.CustomerID = o.CustomerID
ORDER BY c.CustomerID, o.OrderDate;

Explanation

The explanation of the solution code is given below:

  • Line 1: Select the customer identifiers, customer names, and order details including OrderID, OrderDate, and TotalAmount.

  • Lines 2–5: Compute a running total using SUM(o.TotalAmount) as a window function. The clause PARTITION BY c.CustomerID separates the calculation per customer, and ORDER BY o.OrderDate ensures amounts accumulate in chronological order. The result is exposed as RunningTotal.

  • Lines 6–9: Assign a sequential rank with ...