How to Rank Items Within Categories (Without Grouping Hell)

Hello ,

Imagine this scenario: Your CMO walks into Monday’s meeting with a question:

“I need to see our top 3 highest-revenue expeditions in EACH category – hiking, climbing, safari, cultural, and photography.”

Most analysts would write 5 separate queries, one per category. Or export to Excel and manually filter. An hour of work, minimum.

Confident analysts write one query using window functions. Takes 3 minutes.

Here’s the skill that separates intermediate analysts from advanced ones.

The Business Problem

You want rankings WITHIN groups, not across the entire dataset.

Not “the top 3 expeditions overall.”

But “the top 3 hiking trips, top 3 climbing trips, top 3 safari trips,” etc.

This is the “best within category” question that comes up constantly in business:
Top salespeople in each region
Best-selling products in each department
Highest-rated employees in each team
Most profitable customers in each segment

GROUP BY can’t do this. You need window functions.

The Query That Solves It

I ran this against the Summit Adventures database (the fake adventure tourism company I created to help people learn business analytics):

-- Top 3 revenue-generating expeditions per category
WITH expedition_revenue AS (
    SELECT 
        e.expedition_name,
        e.expedition_type,
        e.difficulty_level,
        SUM(p.amount) AS total_revenue,
        COUNT(DISTINCT b.booking_id) AS total_bookings
    FROM expeditions e
        INNER JOIN expedition_instances ei ON e.expedition_id = ei.expedition_id
        INNER JOIN bookings b ON ei.instance_id = b.instance_id
        INNER JOIN payments p ON b.booking_id = p.booking_id
    WHERE p.payment_status = 'completed'
        AND b.status IN ('completed', 'confirmed')
    GROUP BY e.expedition_id, e.expedition_name, e.expedition_type, e.difficulty_level
),
ranked_expeditions AS (
    SELECT 
        expedition_name,
        expedition_type,
        difficulty_level,
        total_revenue,
        total_bookings,
        ROW_NUMBER() OVER (
            PARTITION BY expedition_type 
            ORDER BY total_revenue DESC
        ) AS revenue_rank
    FROM expedition_revenue
)
SELECT 
    expedition_type,
    expedition_name,
    difficulty_level,
    total_revenue,
    total_bookings,
    revenue_rank
FROM ranked_expeditions
WHERE revenue_rank <= 3
ORDER BY expedition_type, revenue_rank;

Here’s what we found for Summit Adventures:

Hiking (Top 3):
1. Mountain Vista Trek – Expert, $63,566 revenue, 19 bookings
2. Scenic Highlands Walk – Advanced, $46,504 revenue, 9 bookings
3. Alpine Meadows Hike – Advanced, $43,284 revenue, 9 bookings

Climbing (Top 3):
1. Vertical Limits Climb – Advanced, $61,883 revenue, 10 bookings
2. Rock Mastery Course – Expert, $44,609 revenue, 15 bookings
3. Summit Push Challenge – Beginner, $30,148 revenue, 9 bookings

Cultural (Top 3):
1. Heritage Discovery Walk – Beginner, $82,895 revenue, 17 bookings
2. Tradition & Culture Tour – Beginner, $48,275 revenue, 11 bookings
3. Tradition & Culture Tour – Advanced, $44,672 revenue, 18 bookings

Safari (Top 3):
1. Fauna Discovery Journey – Advanced, $51,235 revenue, 16 bookings
2. Wilderness Wildlife Tour – Advanced, $37,676 revenue, 11 bookings
3. Nature Discovery Expedition – Intermediate, $30,386 revenue, 10 bookings

Photography (Top 3):
1. Light & Landscape Course – Expert, $101,320 revenue, 24 bookings
2. Landscape Lens Workshop – Advanced, $37,454 revenue, 7 bookings
3. Wildlife Photo Safari – Beginner, $28,010 revenue, 11 bookings

The insight that matters:

Photography’s top performer (Light & Landscape Course) generates the most revenue of any single expedition ($101K from 24 bookings). Cultural expeditions dominate with beginner-friendly trips, while hiking’s top performer (Mountain Vista Trek) has the highest booking volume (19 bookings) in the hiking category.

This tells marketing exactly where to focus promotion dollars within each category. For example: invest heavily in the Light & Landscape photography course (proven revenue leader), expand beginner cultural offerings (Heritage Discovery Walk generates $83K), and promote the Mountain Vista Trek for volume-based campaigns.

Breaking Down The Window Function

Lines 18-22: The Window Function Magic

ROW_NUMBER() OVER (
    PARTITION BY expedition_type 
    ORDER BY total_revenue DESC
) AS revenue_rank

Let me break down each piece:

ROW_NUMBER() – Assigns a sequential number (1, 2, 3…) to each row

OVER – Signals “this is a window function”

PARTITION BY expedition_type – Create separate ranking windows for each category (hiking gets its own ranking, climbing gets its own, etc.)

ORDER BY total_revenue DESC – Within each category, rank by revenue (highest first)

Think of PARTITION BY like creating invisible groups. Each group gets its own independent ranking starting from 1.

Why This Is Powerful

Without window functions, you’d have to:

1. Filter for hiking only, get top 3
2. Filter for climbing only, get top 3
3. Filter for safari only, get top 3
4. Filter for cultural only, get top 3
5. Filter for photography only, get top 3
6. Manually combine results

5 queries, 5 copy-paste operations, high chance of mistakes.

With window functions: One query. All categories. All rankings. Done.

The CTE Pattern (Bonus Readability)

Notice we used CTEs (Common Table Expressions) with the WITH keyword:

WITH expedition_revenue AS (
    -- First: Calculate revenue per expedition
),
ranked_expeditions AS (
    -- Second: Apply rankings
)
SELECT ... FROM ranked_expeditions

This breaks complex logic into readable steps:
1. Calculate revenue totals (expedition_revenue)
2. Apply rankings within categories (ranked_expeditions)
3. Filter to top 3 (final SELECT)

You could write this as one massive nested query, but nobody would understand it. CTEs make your query tell a story.

Window Functions vs GROUP BY

GROUP BY – Collapses rows into summary groups
“Show me total revenue PER category” → One row per category
You lose individual expedition details

Window Functions – Add calculations WITHOUT collapsing rows
“Show me each expedition PLUS its rank within category” → Keep all expedition details
Every row stays, but now has ranking context

This is why window functions are more powerful for comparative analysis.

Common Window Functions

ROW_NUMBER() – Sequential numbering (1, 2, 3, 4…)
Use when you want unique rankings even for ties
Ties get different numbers (arbitrary order)

RANK() – Ranking with gaps for ties (1, 2, 2, 4…)
Use when ties should have the same rank
Next rank skips numbers (no rank 3 if two items tied for 2)

DENSE_RANK() – Ranking without gaps (1, 2, 2, 3…)
Use when ties should have same rank but next rank continues
No gaps in sequence

LAG() / LEAD() – Access previous/next row values
Compare current month to last month
Calculate change from previous period

For “top N within category” questions, ROW_NUMBER() is usually what you want.

Try This At Your Job

Next time someone asks for “best performers within each category”:

Pattern:

WITH ranked_data AS (
    SELECT 
        your_columns,
        ROW_NUMBER() OVER (
            PARTITION BY category_column
            ORDER BY metric_column DESC
        ) AS ranking
    FROM your_table
)
SELECT * 
FROM ranked_data
WHERE ranking <= N  -- Replace N with how many you want
ORDER BY category_column, ranking;

Replace:
`category_column` with your grouping (region, department, product_type)
`metric_column` with what you’re ranking by (revenue, sales, score)
`N` with how many top items you want (3, 5, 10)

This pattern works for any “best within category” business question.

Real-World Applications

Sales analysis:
“Show me top 5 salespeople in each region by revenue”

Product performance:
“Find the 10 best-selling products in each category”

Customer segmentation:
“Identify highest-value customers in each market segment”

Employee performance:
“Rank employees within each department by performance score”

All of these use the same window function pattern.

When to Level Up

You’re ready for window functions when you find yourself:
Writing multiple similar queries for different categories
Exporting to Excel to manually rank within groups
Wanting to compare items “within context” not overall
Needing to keep detail rows while adding comparative metrics

That’s when window functions save you hours of work.

The Learning Curve

Window functions have different syntax than what you’re used to, but the concept is straightforward once you see it in action.

Here’s what makes it click: Once you understand PARTITION BY (separate ranking windows) and ORDER BY (how to sort within each window), the rest falls into place.

Write 3-4 window function queries and it becomes natural. After that, you’ll wonder how you ever worked without them.

Start with ROW_NUMBER() for ranking. Get comfortable with the pattern. Then explore RANK(), LAG(), LEAD(), and the others as you need them.

Before long, you’ll be the analyst who delivers complex rankings in minutes while others are still fighting with Excel.

Until next time,
Brian ([say hi on twitter!](https://twitter.com/briangraves))

—

P.S. Window functions are covered in depth in Module 7 of SQL for Business Impact, where we explore 12 different window function patterns for comparative analysis. If you’re ready to level up from intermediate to advanced SQL skills, check out the course at [sqlforbusinessimpact.com](https://sqlforbusinessimpact.com).

P.P.S. What “top N within category” questions do you need to answer at work? Reply and let me know – I might use your scenario in a future deep dive. I read every response.