End-to-end SQL Server capstone

This lesson uses DDI_SQL_Training dataset v1.0.0 on SQL Server 2019 or later. Combine filtering, joins, aggregation, and ranking for a repeatable customer-spend report.

Run the example

WITH CustomerSpend AS (
    SELECT o.CustomerID, SUM(i.Quantity * i.UnitPrice) AS Spend
    FROM dbo.Orders AS o
    JOIN dbo.OrderItems AS i ON i.OrderID = o.OrderID
    WHERE o.OrderStatus IN (N'Shipped', N'Completed')
    GROUP BY o.CustomerID
), Ranked AS (
    SELECT CustomerID, Spend,
           ROW_NUMBER() OVER (ORDER BY Spend DESC, CustomerID) AS rn
    FROM CustomerSpend
)
SELECT r.CustomerID, c.FirstName, r.Spend, r.rn
FROM Ranked AS r JOIN dbo.Customers AS c ON c.CustomerID = r.CustomerID
WHERE r.rn <= 3 ORDER BY r.rn;

Read it step by step

1. Filter to Shipped and Completed orders before summing their order-item line totals.
2. GROUP BY CustomerID establishes one spend row per customer, preventing join-count inflation.
3. ROW_NUMBER ranks spend with CustomerID as a deterministic tie-breaker; the final join adds the name. ROW_NUMBER is safe here because the three highest spend values are all different; when ties are possible, use RANK, as the practice does.

Check the result

Spend is from qualifying order lines at their stored UnitPrice, not current product ListPrice.

Practice

Find products in the top three rank positions by sold quantity in Shipped or Completed orders. Include every product tied at a qualifying rank.

WITH ProductUnits AS (
    SELECT i.ProductID, SUM(i.Quantity) AS UnitsSold
    FROM dbo.Orders AS o
    JOIN dbo.OrderItems AS i ON i.OrderID = o.OrderID
    WHERE o.OrderStatus IN (N'Shipped', N'Completed')
    GROUP BY i.ProductID
), Ranked AS (
    SELECT ProductID, UnitsSold,
           RANK() OVER (ORDER BY UnitsSold DESC) AS SalesRank
    FROM ProductUnits
)
SELECT ProductID, UnitsSold, SalesRank
FROM Ranked WHERE SalesRank <= 3
ORDER BY UnitsSold DESC, ProductID;