postMay 17, 2026

Top 3 products per category — SQL window function

#sql#window-functions#challenge

Medium⏱️ Logic Challenge

Given a "products" table with columns (id, category, name, revenue), return the top 3 products by revenue within each category.

Ver solución

WITH ranked AS (
  SELECT
    id,
    category,
    name,
    revenue,
    ROW_NUMBER() OVER (
      PARTITION BY category
      ORDER BY revenue DESC
    ) AS rnk
  FROM products
)
SELECT id, category, name, revenue
FROM ranked
WHERE rnk <= 3;

Ranking within groups is a query pattern you will see in every Data Engineer interview. Plain GROUP BY collapses groups into one row each, which is not what you want — you need the top N rows per group, keeping all the detail.

Window functions solve this. ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) numbers each row within its category, ranked by revenue. Then you filter to keep only ranks 1 through 3.