Separate Before You Sort (Window Functions, Made Simple)

A top-performer list looks objective. It stops being objective the moment a premium photography course, a local hiking trip, and a cultural tour all share the same leaderboard.

They don’t share an audience, a price point, or an operating model. A single overall ranking hides the story inside each category.

The Separate Before You Sort Framework is the fix. Group meaningful peers first, then rank within each group.

Build a fair leaderboard

1. Pick the peer group first. Expedition type respects different products and price points. One company-wide ranking doesn’t.
2. Calculate the measure at the right level. Sum booked value for each expedition before you try to rank anything.
3. Rank inside the group. `PARTITION BY expedition_type` resets the leaderboard for every type.

Here’s the pattern on the live Summit Adventures database, the fake adventure tourism company I created to help people learn business analytics.

WITH expedition_value AS (
    SELECT
        e.expedition_type,
        e.expedition_name,
        SUM(b.total_amount) AS booked_value
    FROM expeditions AS e
    JOIN expedition_instances AS ei
        ON ei.expedition_id = e.expedition_id
    JOIN bookings AS b
        ON b.instance_id = ei.instance_id
    GROUP BY e.expedition_type, e.expedition_name
)
SELECT
    expedition_type,
    expedition_name,
    booked_value,
    RANK() OVER (
        PARTITION BY expedition_type
        ORDER BY booked_value DESC
    ) AS revenue_rank_in_type
FROM expedition_value
ORDER BY expedition_type, revenue_rank_in_type;

Results:

| Expedition type | Top expedition | Booked value | Rank in type |
| — | — | —: | —: |
| Hiking | Mountain Vista Trek | $212,602.22 | 1 |
| Climbing | Vertical Limits Climb | $146,919.29 | 1 |
| Safari | Wilderness Wildlife Tour | $130,988.35 | 1 |
| Photography | Light & Landscape Course | $252,938.25 | 1 |
| Cultural | Tradition & Culture Tour | $218,841.92 | 1 |

Each of those results is easier to act on than a company-wide top-ten list:

Category managers can ask what the leader in their own category is doing well.
Product teams can compare each category’s leader with its other offerings.
Marketing can build a relevant campaign without pretending every expedition is the same product.

How the SQL works

`PARTITION BY expedition_type` creates a separate ranking list for each type. `ORDER BY booked_value DESC` ranks the most valuable expedition first inside that list.

The business question comes first: what are the fair comparison groups? In another business, that could be salespeople by region, products by category, stores by market, or support teams by queue.

A ranking can still be unfair

Don’t mix unlike products. A premium offer and an entry-level offer can both be valuable without competing for the same rank.
Check the sample size. A leader with two bookings deserves a different conversation than a leader with two hundred.
Decide how to handle ties. `RANK()` leaves a gap after equal values. `DENSE_RANK()` doesn’t. Pick what your report needs.

The sentence that gives you the query

Before you write the window function, finish this: “Within each ___, which ___ should we compare?”

“Within each product category, which product has the highest booked value?”
“Within each sales region, which rep has the most closed deals?”
“Within each support queue, which issue type takes the longest to resolve?”

That sentence gives you the `PARTITION BY` field and the measure for `ORDER BY`. It also tells you when ranking is the wrong tool. A manager who needs to know whether a category is growing needs a time-series comparison, not a leaderboard.

This Week’s Action Item

Think of one ranking you use or receive. Ask whether it mixes groups with different conditions. If it does, choose the field that creates fairer peer groups and try a `PARTITION BY` ranking.