Keyboard shortcuts

Press ← or → to navigate between chapters

Press S or / to search in the book

Press ? to show this help

Press Esc to hide this help

Window Ranking Functions Examples

Table of Contents

  1. ROW_NUMBER, RANK, DENSE_RANK

  2. NTILE

  3. CUME_DIST

1. ROW_NUMBER, RANK, DENSE_RANK

1.1 Rank orders by sales (highest to lowest)

Task 1: Rank all orders by Sales from highest to lowest and show three ranking styles:

  • ROW_NUMBER() – unique rank (no ties)
  • RANK() – ties share the same rank, gaps appear
  • DENSE_RANK() – ties share rank, no gaps

Include OrderID, ProductID, Sales, and the three ranking columns.

💡 Suggested Answers 
SELECT
    OrderID,
    ProductID,
    Sales,
    ROW_NUMBER() OVER (ORDER BY Sales DESC) AS SalesRank_Row,
    RANK() OVER (ORDER BY Sales DESC) AS SalesRank_Rank,
    DENSE_RANK() OVER (ORDER BY Sales DESC) AS SalesRank_Dense
FROM Sales.Orders;

1.2 Top-N: highest sale for each product

Task 2 (Top-N use case): For each ProductID, find the order with the highest Sales (top 1 per product). Use ROW_NUMBER() partitioned by product and filter to keep only the top row.

💡 Suggested Answers 
SELECT *
FROM (
    SELECT
        OrderID,
        ProductID,
        Sales,
        ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY Sales DESC) AS RankByProduct
    FROM Sales.Orders
) AS TopProductSales
WHERE RankByProduct = 1;

1.3 Bottom-N: lowest 2 customers by total sales

Task 3 (Bottom-N use case): Find the 2 customers with the lowest total Sales across all their orders. First aggregate sales per customer, then rank them using ROW_NUMBER() and keep the bottom 2.

💡 Suggested Answers 
SELECT *
FROM (
    SELECT
        CustomerID,
        SUM(Sales) AS TotalSales,
        ROW_NUMBER() OVER (ORDER BY SUM(Sales)) AS RankCustomers
    FROM Sales.Orders
    GROUP BY CustomerID
) AS BottomCustomerSales
WHERE RankCustomers <= 2;

1.4 Assign unique IDs to OrdersArchive rows

Task 4 (Use case – unique IDs): Assign a unique sequential ID to each row in Sales.OrdersArchive, ordered by OrderID and OrderDate. Return this UniqueID plus all original columns.

💡 Suggested Answers 
SELECT
    ROW_NUMBER() OVER (ORDER BY OrderID, OrderDate) AS UniqueID,
    *
FROM Sales.OrdersArchive;

1.5 Remove duplicates from OrdersArchive (keep latest)

Task 5 (Use case – identify duplicates & clean): In Sales.OrdersArchive, there may be multiple rows per OrderID. Use ROW_NUMBER() to:

  • identify duplicates per OrderID, ordered so that latest CreationTime is first
  • keep only the most recent row per OrderID (i.e. remove duplicates)
💡 Suggested Answers 
SELECT *
FROM (
    SELECT
        ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY CreationTime DESC) AS rn,
        *
    FROM Sales.OrdersArchive
) AS UniqueOrdersArchive
WHERE rn = 1;

2. NTILE

2.1 Divide orders into multiple numeric buckets by sales

Task 6: Divide orders into different numbers of buckets by Sales using NTILE:

  • 1 bucket
  • 2 buckets
  • 3 buckets
  • 4 buckets
  • plus 2 buckets per product (PARTITION BY ProductID)

Show OrderID, Sales, and all bucket columns.

💡 Suggested Answers 
SELECT 
    OrderID,
    Sales,
    NTILE(1) OVER (ORDER BY Sales) AS OneBucket,
    NTILE(2) OVER (ORDER BY Sales) AS TwoBuckets,
    NTILE(3) OVER (ORDER BY Sales) AS ThreeBuckets,
    NTILE(4) OVER (ORDER BY Sales) AS FourBuckets,
    NTILE(2) OVER (PARTITION BY ProductID ORDER BY Sales) AS TwoBucketByProducts
FROM Sales.Orders;

2.2 Segment orders into High / Medium / Low sales groups

Task 7 (Segmentation use case): Segment orders into 3 sales categories using NTILE(3) over Sales DESC:

  • Bucket 1 → High
  • Bucket 2 → Medium
  • Bucket 3 → Low

Show OrderID, Sales, the bucket number, and the text label.

💡 Suggested Answers 
SELECT
    OrderID,
    Sales,
    Buckets,
    CASE 
        WHEN Buckets = 1 THEN 'High'
        WHEN Buckets = 2 THEN 'Medium'
        WHEN Buckets = 3 THEN 'Low'
    END AS SalesSegmentations
FROM (
    SELECT
        OrderID,
        Sales,
        NTILE(3) OVER (ORDER BY Sales DESC) AS Buckets
    FROM Sales.Orders
) AS SalesBuckets;

2.3 Divide orders into 5 groups for processing

Task 8: Split all orders into 5 roughly equal groups (buckets) for sequential processing, based on OrderID. Show the bucket number and all original order columns.

💡 Suggested Answers 
SELECT 
    NTILE(5) OVER (ORDER BY OrderID) AS Buckets,
    *
FROM Sales.Orders;

3. CUME_DIST

3.1 Find products in the highest 40% of prices

Task 9 (distribution / top % use case): Using Sales.Products, find products whose price is within the highest 40% of all prices.

Steps inside the query:

  • compute CUME_DIST() over Price DESC as DistRank
  • keep only rows where DistRank <= 0.4
  • also show DistRank as a percentage string
💡 Suggested Answers 
SELECT 
    Product,
    Price,
    DistRank,
    CONCAT(DistRank * 100, '%') AS DistRankPerc
FROM (
    SELECT
        Product,
        Price,
        CUME_DIST() OVER (ORDER BY Price DESC) AS DistRank
    FROM Sales.Products
) AS PriceDistribution
WHERE DistRank <= 0.4;