Window Functions: Ranking & Partitions — SQL Course

What you'll learn

Calculate ranks, row numbers, percentiles, and partition-level measures without collapsing detail rows. The lab uses PostgreSQL while identifying the semantics that transfer to other relational systems.

By the end of this lesson, you'll be able to:

Apply OVER to a realistic data question
Apply PARTITION BY to a realistic data question
Apply Window ORDER BY to a realistic data question
Apply ROW_NUMBER to a realistic data question

Core mental model

SQL is declarative: describe the result or invariant you need, then let the database choose a physical execution strategy. Use this table to connect syntax to design decisions.

Concept	What it means	Decision rule
Partition	Rows independently processed by a window function	Partition by the entity within which comparison is meaningful
Window order	Sequence used inside each partition	Include a deterministic tie-breaker
RANK	Equal values share rank and leave gaps	Choose ROW_NUMBER, RANK, or DENSE_RANK from tie semantics

Professional workflow

Work from a defined question and result grain, then verify correctness before performance.

State the partitioned ranking question and the exact grain of the expected result.
Inspect table definitions, keys, constraints, representative values, and row counts.
Write the smallest correct query with explicit columns, aliases, and predicates.
Test missing, duplicate, boundary, and NULL cases before trusting the result.
Inspect the execution plan or affected rows when cost or data change matters.
Save the query with its assumptions, parameters, verification, and recovery notes.

Make results explainable

Keep each query in a saved SQL file with a short statement of its purpose, expected grain, assumptions, and verification query.

Guided SQL lab

Rank products inside categories

The query retains product detail while calculating deterministic revenue rank per category.

category_ranking.sql

SELECT
  category_id,
  product_id,
  revenue,
  dense_rank() OVER (
    PARTITION BY category_id
    ORDER BY revenue DESC
  ) AS revenue_rank
FROM analytics.product_revenue
ORDER BY category_id, revenue_rank, product_id;

Production practice

Contract

Define the expected row grain, inputs, output columns, invariants, and failure or empty-result behavior before writing SQL.

Verification

Use representative fixtures and independent row-count, uniqueness, NULL, and boundary checks; compare plans when cost matters.

Operations

Save reviewed SQL with explicit schema names where appropriate, bounded scope, least privilege, observability, and a recovery path for changes.

Common failure mode

A query-level ORDER BY does not define a window's order. Ranking semantics live inside OVER.

Independent workshop

Build a review-ready partitioned ranking lab against the course commerce dataset.

Your finished workshop must include:

OVER
PARTITION BY
Window ORDER BY
ROW_NUMBER
RANK and DENSE_RANK
Verification notes and edge-case evidence

Definition of done

Run the expected case and at least two edge cases, verify row counts and grain, and add comments explaining any vendor-specific behavior.

Recap & quick check

Key takeaways

Partition: Partition by the entity within which comparison is meaningful
Window order: Include a deterministic tie-breaker
RANK: Choose ROW_NUMBER, RANK, or DENSE_RANK from tie semantics

Quick check

1. Which rule best applies to Partition?

2. Which rule best applies to Window order?

3. Which rule best applies to RANK?

Next: Window Frames, Running Totals & Time Analysis