Who this is for
You build dashboards or analyses and need reliable Top N lists, leaderboards, and per-group rankings. If you work with products, customers, campaigns, or KPIs and often ask "top per category/region," this is for you.
Prerequisites
- Comfort with SELECT, WHERE, GROUP BY, ORDER BY
- Basic understanding of tables, rows, and columns
Why this matters
As a Data Visualization Engineer you frequently need:
- Top 5 products per category for a dashboard card
- Best-performing campaign per channel (with ties visible)
- Latest order per customer for a detail view
- Weekly leaderboard of sales reps within each region
Window functions let you rank rows within groups without collapsing data, making your charts accurate, drillable, and fast to iterate.
Concept explained simply
A window function looks at a row and its "neighbors" defined by OVER (...). For ranking, we use:
- ROW_NUMBER() – unique sequence 1,2,3... (no ties)
- RANK() – same order, but ties share the same rank and create gaps (1,1,3)
- DENSE_RANK() – ties share rank without gaps (1,1,2)
- NTILE(n) – splits ordered rows into n buckets (quartiles/deciles)
The PARTITION BY defines groups (e.g., per category). The ORDER BY defines the ranking criteria (e.g., revenue DESC). Return only top N by filtering the computed rank in an outer query.
Mental model
Imagine your table sorted within each group. A tiny counter walks down the list:
- ROW_NUMBER: counter always +1
- RANK: counter skips after ties
- DENSE_RANK: counter increments only when value changes
Pick the one that matches your business rule for ties.
Worked examples
1) Top 3 orders per customer (keep ties)
-- Rank orders within each customer by revenue (ties kept)
SELECT
customer_id,
order_id,
revenue,
DENSE_RANK() OVER (PARTITION BY customer_id ORDER BY revenue DESC) AS revenue_rank
FROM sales;
-- Only top 3 per customer
WITH ranked AS (
SELECT *, DENSE_RANK() OVER (PARTITION BY customer_id ORDER BY revenue DESC) AS rnk
FROM sales
)
SELECT customer_id, order_id, revenue, rnk
FROM ranked
WHERE rnk <= 3
ORDER BY customer_id, rnk, revenue DESC, order_id;Use DENSE_RANK to include all tied orders at the cutoff. This is ideal for dashboards where you want to show all equally top-performing items.
2) Best product per category per week (deterministic pick)
-- Pick exactly one product per category+week
WITH rn AS (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY week, category
ORDER BY sales DESC, product_id ASC -- tie-breaker for stability
) AS rn
FROM sales_weekly
)
SELECT week, category, product_id, sales
FROM rn
WHERE rn = 1
ORDER BY week, category;ROW_NUMBER ensures a single winner per group. Add a clear tie-break to keep results stable between runs.
3) Global rank vs per-partition rank
-- Global top 10 products by lifetime sales
SELECT product_id, total_sales,
RANK() OVER (ORDER BY total_sales DESC) AS global_rank
FROM product_totals
ORDER BY global_rank
FETCH FIRST 10 ROWS ONLY;
-- Per-category top 3 products
WITH cat_rank AS (
SELECT *,
DENSE_RANK() OVER (
PARTITION BY category ORDER BY total_sales DESC
) AS cat_rank
FROM product_totals
)
SELECT category, product_id, total_sales
FROM cat_rank
WHERE cat_rank <= 3
ORDER BY category, cat_rank, total_sales DESC;Leaving out PARTITION BY ranks across the whole table. Adding PARTITION BY restarts the ranking within each group.
Practice: Exercises
Mirror of the exercises below. Do them in order. After finishing, tick the checklist and take the quick test.
- Exercise 1: Rank orders per customer, keep ties, show top 2
- Exercise 2: Pick top product per category per week with deterministic tie-breaks
Exercise 1 (ex1) — Top 2 orders per customer with ties
Table: sales(order_id INT, customer_id INT, order_date DATE, revenue INT)
-- Sample data
-- order_id | customer_id | order_date | revenue
-- 1 | 100 | 2023-01-01 | 120
-- 2 | 100 | 2023-01-10 | 300
-- 3 | 100 | 2023-02-01 | 300
-- 4 | 101 | 2023-01-05 | 50
-- 5 | 101 | 2023-01-15 | 120
-- 6 | 101 | 2023-02-01 | 120
-- Task: Return customer_id, order_id, revenue, and rank so that
-- only the top 2 revenues per customer are returned, including ties.
-- Sort by customer_id, rank, revenue DESC, order_id.- Use DENSE_RANK over (PARTITION BY customer_id ORDER BY revenue DESC)
- Filter rank <= 2 in an outer query
Exercise 2 (ex2) — One winner per category per week
Table: sales_weekly(week DATE, category TEXT, product_id TEXT, sales INT)
-- Sample data
-- week | category | product_id | sales
-- 2023-01-01 | A | p1 | 50
-- 2023-01-01 | A | p2 | 50
-- 2023-01-01 | A | p3 | 40
-- 2023-01-01 | B | p9 | 90
-- 2023-01-01 | B | p8 | 80
-- Task: Return week, category, product_id, sales for the single top product
-- per (week, category). Break ties by product_id (alphabetical ascending).- Use ROW_NUMBER with PARTITION BY week, category
- ORDER BY sales DESC, product_id ASC
- Filter rn = 1
Exercise checklist
- I used PARTITION BY to reset ranking per group
- I chose the correct ranking function for the tie behavior required
- I filtered ranks in an outer SELECT (not in the same SELECT alias)
- I added a deterministic tie-breaker when using ROW_NUMBER
Common mistakes and self-check
- Mistake: Filtering by a window function alias in the same SELECT. Fix: wrap in CTE/subquery, then filter.
- Mistake: Using ROW_NUMBER when you need to keep ties. Fix: Use DENSE_RANK or RANK.
- Mistake: Forgetting ORDER BY in OVER(). Fix: Add ORDER BY; otherwise ranking is arbitrary.
- Mistake: Unstable results with ROW_NUMBER. Fix: Add deterministic tie-break columns.
- Self-check: If two items tie, do you want both shown? If yes, DENSE_RANK; if one winner, ROW_NUMBER.
Practical projects
- Build a "Top N per category" dashboard component that expands to show ties.
- Create a weekly sales leaderboard per region with ROW_NUMBER for one clear winner and DENSE_RANK for a "show all winners" view.
- Design a product detail page snippet showing a product's global rank and category rank side by side.
Learning path
- Now: Master ROW_NUMBER, RANK, DENSE_RANK, NTILE
- Next: Moving averages and sums (window frames: ROWS/RANGE)
- Then: Percentiles (PERCENT_RANK, CUME_DIST), advanced partitioning
- Also: CTEs and subqueries for clean filtering of windowed results
Next steps
- Finish the two exercises above
- Run through the quick test below to check understanding
- Apply to a live dataset: pick one chart and power it with a window function
Mini challenge
Given orders(customer_id, order_id, order_date, revenue), show the latest order per customer, but if two orders share the same date, return the higher revenue one. Hint: one window with ROW_NUMBER and two ORDER BY columns.
Quick Test
Everyone can take this test for free. Log in to save your progress and resume later.