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 ROW_NUMBER(). The partition groups rows by CustomerID, and ordering by OrderDate numbers each of the customer’s orders from earliest to latest. The output column is OrderRank.

  • Line 10: Specify the Customers table as the base ...