Вивчайте Top-N per Group Reports | Analytical SQL Techniques

Секція 1. Розділ 8

single

Свайпніть щоб показати меню

When you need to produce a report showing the top N items within each group—such as the top 5 products by revenue in each category or the top 2 performing stores in every region—SQL window functions become your most powerful tool. A classic challenge in analytics is to answer questions like: "Who are the best-performing employees in every department?" or "Which products lead sales within each category?" These reports help businesses focus on their most successful people, products, or stores within each logical segment, guiding data-driven decisions.

To generate a Top-N-per-group report, you use a window function to assign a ranking within each group, then filter to keep only the top-ranked rows. The most common window functions for this are ROW_NUMBER(), RANK(), and DENSE_RANK(). You partition the data by the grouping column (such as category, region, or department) and order by the metric of interest (like revenue or sales). After assigning a row number within each group, you filter the results to include only the rows where the row number is less than or equal to N.

Definition

In Top-N-per-group queries, partitioning means dividing your data into groups (using PARTITION BY, such as by category or region). Ordering means sorting rows within each group based on a metric (such as ORDER BY revenue DESC), so the ranking window function can assign the correct order for each group.


              123456789101112131415161718
            
SELECT
  category,
  product_name,
  total_revenue
FROM (
  SELECT
    category,
    product_name,
    SUM(total_sales) AS total_revenue,
    ROW_NUMBER() OVER (
      PARTITION BY category
      ORDER BY SUM(total_sales) DESC
    ) AS rn
  FROM product_sales
  GROUP BY category, product_name
) ranked
WHERE rn <= 5
ORDER BY category, total_revenue DESC;

Use case: The retail chain wants a report of the top 2 performing stores in each region.


              1234567891011121314151617
            
SELECT
  region,
  total_sales,
  rn
FROM (
  SELECT
    region,
    SUM(total_sales) AS total_sales,
    ROW_NUMBER() OVER (
      PARTITION BY region
      ORDER BY SUM(total_sales) DESC
    ) AS rn
  FROM product_sales
  GROUP BY region
) ranked
WHERE rn <= 2
ORDER BY region, total_sales DESC;

Завдання

Проведіть, щоб почати кодувати

Write a query to display the top 3 salespeople by total sales value in each department.

Use the hr_employees and conversions tables.
Match salespeople by comparing the sales_rep field in conversions to the concatenated first_name and last_name in hr_employees.
For each department, calculate the total sales value for each salesperson.
Assign a row number within each department, ordered by total sales value descending.
Select only the top 3 salespeople per department.

Рішення

Перейдіть на комп'ютер для реальної практикиПродовжуйте з того місця, де ви зупинились, використовуючи один з наведених нижче варіантів

Все було зрозуміло?

Дякуємо за ваш відгук!

Секція 1. Розділ 8

single

Запитати АІ

Запитайте про що завгодно або спробуйте одне із запропонованих запитань, щоб почати наш чат