Top-N Selling Products per Category with Dense Rank

What you will learn

• Join dimensional product catalogs with transactional order line items
• Compute aggregated revenue per product using SUM(quantity * price)
• Rank items within category partitions using DENSE_RANK() to handle revenue ties gracefully
• Filter window rankings within Common Table Expressions and calculate category revenue contribution shares

What you need first

SELECT category, product_name, DENSE_RANK() OVER (PARTITION BY category ORDER BY price DESC) AS rnk FROM products LIMIT 3;

category    | product_name                 | rnk
------------+------------------------------+----
Apparel     | Waterproof Trail Jacket      | 1
Apparel     | Breathable Training Shirt    | 2
Apparel     | Thermal Running Socks        | 3

WITH ranked AS (SELECT product_name, DENSE_RANK() OVER (ORDER BY price DESC) AS rnk FROM products) SELECT * FROM ranked WHERE rnk <= 2;

product_name                 | rnk
-----------------------------+----
Noise Canceling Headphones   | 1
Waterproof Trail Jacket      | 2

Architecture and data flow

The data

Product catalog table and transactional order line items table.

CREATE OR REPLACE TABLE products (product_id INT, category VARCHAR, product_name VARCHAR, price DOUBLE);
INSERT INTO products VALUES
  (1, 'Electronics', 'Noise Canceling Headphones', 150.0),
  (2, 'Electronics', 'Wireless Mechanical Keyboard', 100.0),
  (3, 'Electronics', 'Ergonomic Gaming Mouse', 50.0),
  (4, 'Electronics', 'Ultra-Wide Monitor Stand', 75.0),
  (5, 'Apparel', 'Waterproof Trail Jacket', 120.0),
  (6, 'Apparel', 'Thermal Running Socks', 25.0),
  (7, 'Apparel', 'Breathable Training Shirt', 45.0);

CREATE OR REPLACE TABLE order_items AS SELECT * FROM (VALUES
  (101, 1, 10),
  (102, 2, 8),
  (103, 3, 15),
  (104, 4, 4),
  (105, 5, 12),
  (106, 6, 40),
  (107, 7, 20)
) AS t(item_id, product_id, quantity);

Step by step

Read each step, run its code, then check your output against the one shown.

1. 01

   Aggregate sales revenue per product

   We start by joining products with order_items on product_id to calculate total revenue per product item. We compute total units sold with SUM(quantity) and total earned revenue by multiplying quantity by unit price: SUM(oi.quantity * p.price). Grouping by category, product name, and price gives us baseline sales figures. In Electronics, Headphones generated 1500.0, Keyboard generated 800.0, and Mouse generated 750.0. Joining transactional order line items with product catalog metadata allows us to evaluate true commercial performance based on realized cash revenue rather than raw sales unit volumes.

   SQLStep 1Copy

   SELECT
     p.category,
     p.product_name,
     p.price,
     SUM(oi.quantity) AS total_units_sold,
     SUM(oi.quantity * p.price) AS total_revenue
   FROM products p
   JOIN order_items oi ON p.product_id = oi.product_id
   GROUP BY p.category, p.product_name, p.price
   ORDER BY p.category, total_revenue DESC;

   Show outputGuess what it prints first

   category    | product_name                 | price | total_units_sold | total_revenue
   ------------+------------------------------+-------+------------------+--------------
   Apparel     | Waterproof Trail Jacket      | 120   | 12               | 1440
   Apparel     | Thermal Running Socks        | 25    | 40               | 1000
   Apparel     | Breathable Training Shirt    | 45    | 20               | 900
   Electronics | Noise Canceling Headphones   | 150   | 10               | 1500
   Electronics | Wireless Mechanical Keyboard | 100   | 8                | 800
   Electronics | Ergonomic Gaming Mouse       | 50    | 15               | 750
   Electronics | Ultra-Wide Monitor Stand     | 75    | 4                | 300

   Common mistake

   Multiplying price by quantity without an enclosing SUM function, which causes grouping errors in SQL.

   How you will know

   You will see all 7 products with their total units sold and calculated total revenue sorted within category.

2. 02

   Attempt naive direct window filtering in WHERE

   Wrong turn first: this step shows the mistake before the fix

   A frequent mistake when writing top-N queries is attempting to filter window functions directly inside the WHERE clause, such as writing WHERE DENSE_RANK() OVER (...) <= 2. Because database query planners execute WHERE filters before calculating window functions, this query is rejected with an execution error. To filter by window ranking, we must compute the rank in a preceding CTE or subquery first. SQL execution engines enforce strict logical processing stages where WHERE clauses filter rows before window functions calculate rankings, making nested subqueries or CTE wrappers mandatory for top-N analytical patterns.

   SQLStep 2Copy

   SELECT
     p.category,
     p.product_name,
     SUM(oi.quantity * p.price) AS total_revenue,
     DENSE_RANK() OVER (PARTITION BY p.category ORDER BY SUM(oi.quantity * p.price) DESC) AS rank_pos
   FROM products p
   JOIN order_items oi ON p.product_id = oi.product_id
   GROUP BY p.category, p.product_name
   ORDER BY p.category, rank_pos;

   Show outputGuess what it prints first

   category    | product_name                 | total_revenue | rank_pos
   ------------+------------------------------+---------------+---------
   Apparel     | Waterproof Trail Jacket      | 1440          | 1
   Apparel     | Thermal Running Socks        | 1000          | 2
   Apparel     | Breathable Training Shirt    | 900           | 3
   Electronics | Noise Canceling Headphones   | 1500          | 1
   Electronics | Wireless Mechanical Keyboard | 800           | 2
   Electronics | Ergonomic Gaming Mouse       | 750           | 3
   Electronics | Ultra-Wide Monitor Stand     | 300           | 4

   Common mistake

   Attempting to filter window rankings directly in WHERE rather than wrapping them in a CTE.

   How you will know

   Observe that without a CTE wrapper, we cannot restrict output rows to just the top 2 ranked items.

3. 03

   Isolate top-2 items per category via CTE

   We resolve the execution order restriction by placing the product revenue aggregation and DENSE_RANK calculation inside a Common Table Expression named product_rev. In the outer query, the ranking calculation is already materialized as category_rank. The output isolates the top 2 revenue earners in Apparel and the top 2 in Electronics. Using a Common Table Expression allows the query optimizer to materialize ranked categories cleanly.

   SQLStep 3Copy

   WITH product_rev AS (
     SELECT
       p.category,
       p.product_name,
       SUM(oi.quantity * p.price) AS total_revenue,
       DENSE_RANK() OVER (PARTITION BY p.category ORDER BY SUM(oi.quantity * p.price) DESC) AS category_rank
     FROM products p
     JOIN order_items oi ON p.product_id = oi.product_id
     GROUP BY p.category, p.product_name
   )
   SELECT category, category_rank, product_name, total_revenue
   FROM product_rev
   WHERE category_rank <= 2
   ORDER BY category, category_rank;

   Show outputGuess what it prints first

   category    | category_rank | product_name                 | total_revenue
   ------------+---------------+------------------------------+--------------
   Apparel     | 1             | Waterproof Trail Jacket      | 1440
   Apparel     | 2             | Thermal Running Socks        | 1000
   Electronics | 1             | Noise Canceling Headphones   | 1500
   Electronics | 2             | Wireless Mechanical Keyboard | 800

   Common mistake

   Partitioning by product_name instead of category, which gives every individual product rank 1.

   How you will know

   Output contains exactly 4 rows: ranks 1 and 2 in Apparel, and ranks 1 and 2 in Electronics.

4. 04

   Compare ROW_NUMBER vs DENSE_RANK behavior

   Understanding the difference between ranking functions is critical for inventory reorders. ROW_NUMBER assigns distinct sequential integers even when two products share identical revenue, arbitrarily choosing one product over another. In contrast, DENSE_RANK assigns identical rank numbers to tied items without skipping downstream numbers. We run both functions side-by-side to verify our replenishment logic treats equal revenue generators fairly. In warehouse fulfillment, selecting the appropriate ranking algorithm prevents arbitrary tie-breaking from dropping qualifying best-sellers.

   SQLStep 4Copy

   WITH product_rev AS (
     SELECT
       p.category,
       p.product_name,
       SUM(oi.quantity * p.price) AS total_revenue,
       ROW_NUMBER() OVER (PARTITION BY p.category ORDER BY SUM(oi.quantity * p.price) DESC) AS row_num,
       DENSE_RANK() OVER (PARTITION BY p.category ORDER BY SUM(oi.quantity * p.price) DESC) AS dense_rnk
     FROM products p
     JOIN order_items oi ON p.product_id = oi.product_id
     GROUP BY p.category, p.product_name
   )
   SELECT category, product_name, total_revenue, row_num, dense_rnk
   FROM product_rev
   ORDER BY category, total_revenue DESC;

   Show outputGuess what it prints first

   category    | product_name                 | total_revenue | row_num | dense_rnk
   ------------+------------------------------+---------------+---------+----------
   Apparel     | Waterproof Trail Jacket      | 1440          | 1       | 1
   Apparel     | Thermal Running Socks        | 1000          | 2       | 2
   Apparel     | Breathable Training Shirt    | 900           | 3       | 3
   Electronics | Noise Canceling Headphones   | 1500          | 1       | 1
   Electronics | Wireless Mechanical Keyboard | 800           | 2       | 2
   Electronics | Ergonomic Gaming Mouse       | 750           | 3       | 3
   Electronics | Ultra-Wide Monitor Stand     | 300           | 4       | 4

   Common mistake

   Using RANK() which skips rank numbers after ties (for example, 1, 1, 3), causing a filter of rank <= 2 to miss the third item.

   How you will know

   The output compares row_num and dense_rnk across all products in both categories.

5. 05

   Compute category revenue share percentage

   In the final query, we enhance our category top-2 leaderboard by calculating each product's percentage contribution to its parent category revenue. We use an additional window function SUM(total_revenue) OVER (PARTITION BY category) to compute category totals, then divide product revenue by category revenue multiplied by 100.0. In Apparel, the Trail Jacket accounts for 43.1 percent of category revenue. Computing partitioned window totals within the same query pass delivers instant visibility into category revenue concentration.

   SQLStep 5Copy

   WITH product_rev AS (
     SELECT
       p.category,
       p.product_name,
       SUM(oi.quantity * p.price) AS total_revenue,
       DENSE_RANK() OVER (PARTITION BY p.category ORDER BY SUM(oi.quantity * p.price) DESC) AS category_rank,
       SUM(SUM(oi.quantity * p.price)) OVER (PARTITION BY p.category) AS category_total_rev
     FROM products p
     JOIN order_items oi ON p.product_id = oi.product_id
     GROUP BY p.category, p.product_name
   )
   SELECT
     category,
     category_rank,
     product_name,
     total_revenue,
     ROUND(100.0 * total_revenue / category_total_rev, 1) AS category_share_pct
   FROM product_rev
   WHERE category_rank <= 2
   ORDER BY category, category_rank;

   Show outputGuess what it prints first

   category    | category_rank | product_name                 | total_revenue | category_share_pct
   ------------+---------------+------------------------------+---------------+-------------------
   Apparel     | 1             | Waterproof Trail Jacket      | 1440          | 43.1
   Apparel     | 2             | Thermal Running Socks        | 1000          | 29.9
   Electronics | 1             | Noise Canceling Headphones   | 1500          | 44.8
   Electronics | 2             | Wireless Mechanical Keyboard | 800           | 23.9

   Common mistake

   Dividing by global store revenue instead of partitioned category revenue when calculating category concentration.

   How you will know

   The final leaderboard displays category, category_rank, product_name, total_revenue, and category_share_pct.

Build it yourself

Write your solution below, run it in your browser, and pass every check.

What the result means

Interpretation

The top-N leaderboard highlights extreme revenue concentration in both categories. In Apparel, the Waterproof Trail Jacket (1440.0) and Thermal Running Socks (1000.0) account for over 73 percent of all apparel sales. In Electronics, Noise Canceling Headphones (1500.0) and Wireless Mechanical Keyboards (800.0) contribute over 68 percent of category revenue. Isolating these top 2 items enables fulfillment centers to safeguard stock levels on high-margin revenue drivers.

The message you would send

Hi Fulfillment Operations, the category top-N sales model is ready. The analysis reveals that the top 2 products in each category generate roughly 70% of total category revenue. We recommend adjusting warehouse reorder points immediately to maintain safety stock on these four flagship products.

Put it on your CV

Resume or LinkedIn bullet

Built an inventory replenishment ranking model in SQL using DENSE_RANK(), CTE window filtering, and revenue share math in DuckDB.

README for GitHub

# Top-N Selling Products per Category with Dense Rank

An enterprise inventory optimization query isolating the top-performing products within each catalog category using window ranking functions.

## Features
- Relational join across products catalog and order line items
- Product revenue aggregation via SUM(quantity * price)
- Partitioned ranking using DENSE_RANK() to handle tied revenue
- Common Table Expression filtering and category revenue contribution shares

Keep going

• LessonSQL window functions
• Case studyDashMart Basket Affinity Pairs
• Case studyShopKart Customer Frequency NTile