Ranking and Offset Windows

ROW_NUMBER, RANK, and DENSE_RANK

Ranking functions assign a position to each row within a partition. The three functions — ROW_NUMBER, RANK, and DENSE_RANK — differ only in how they handle ties, and choosing the wrong one is one of the most common bugs in analytical SQL.

SELECT
    student_name,
    class,
    score,
    ROW_NUMBER() OVER (PARTITION BY class ORDER BY score DESC) AS row_num,
    RANK()       OVER (PARTITION BY class ORDER BY score DESC) AS rank_pos,
    DENSE_RANK() OVER (PARTITION BY class ORDER BY score DESC) AS dense_pos
FROM exam_scores;

Consider a class with scores 100, 100, 90, 80. The three functions produce:

ROW_NUMBER assigns a unique sequential integer to every row, regardless of ties. Alice and Bob both scored 100, but ROW_NUMBER arbitrarily assigns 1 and 2. The assignment is non-deterministic unless you add a tiebreaker to the ORDER BY. Use ROW_NUMBER when you need exactly N rows per group — for example, the top 3 products per category.

RANK assigns the same position to tied rows, then skips the next positions. Alice and Bob both get rank 1. The next rank is 3, not 2, because two rows occupy rank 1. Use RANK when you want competition ranking — "tied for first, then third place."

DENSE_RANK assigns the same position to tied rows but does not skip. Alice and Bob both get 1. The next rank is 2. Use DENSE_RANK when you want a continuous sequence — "tied for first, then second place."

The choice depends on the business question. "Show me the top 3 students by score" — if two students tie for first, do you want 2 rows (RANK), 3 rows (DENSE_RANK), or exactly 3 rows (ROW_NUMBER)? The answer determines the function.

Top-N per group. The most common ranking pattern is extracting the top N rows per partition. Combine ROW_NUMBER with a CTE:

WITH ranked AS (
    SELECT
        product_name,
        category,
        revenue,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) AS rn
    FROM product_sales
)
SELECT product_name, category, revenue
FROM ranked
WHERE rn <= 3;

This returns exactly 3 products per category, even if there are ties at the boundary. If you want all tied rows at the boundary, use RANK instead of ROW_NUMBER.

LAG and LEAD: Accessing Adjacent Rows

LAG and LEAD are offset functions that access data from a previous or next row within the partition, without a self-join. LAG looks backward; LEAD looks forward. Both require an ORDER BY in the OVER clause because "previous" and "next" are meaningless without a defined order.

SELECT
    order_id,
    customer_id,
    order_date,
    total,
    LAG(total, 1)  OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_order_total,
    LEAD(total, 1) OVER (PARTITION BY customer_id ORDER BY order_date) AS next_order_total
FROM orders;

For each order, prev_order_total shows the customer's previous order amount, and next_order_total shows the next one. The first order for each customer has no previous order, so LAG returns NULL. The last order has no next order, so LEAD returns NULL.

The second argument is the offset — how many rows back or forward to look. The default is 1. A third argument provides a default value instead of NULL:

LAG(total, 1, 0) OVER (PARTITION BY customer_id ORDER BY order_date)

This returns 0 instead of NULL for the first row, which is useful when you are about to do arithmetic on the result.

Period-over-period comparison. The most common LAG use case is comparing a metric to its previous period:

WITH monthly AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(total) AS revenue
    FROM orders
    GROUP BY DATE_TRUNC('month', order_date)
)
SELECT
    month,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_revenue,
    revenue - LAG(revenue, 1) OVER (ORDER BY month) AS revenue_change,
    ROUND(
        100.0 * (revenue - LAG(revenue, 1) OVER (ORDER BY month))
        / NULLIF(LAG(revenue, 1) OVER (ORDER BY month), 0),
    2) AS pct_change
FROM monthly;

This produces a month-over-month growth report in a single query. The LAG function retrieves the previous month's revenue, and the outer query computes the absolute and percentage change. Without LAG, this would require a self-join on the CTE — more code, same result.

First and last value in a partition. LAG with a large offset is fragile. For the first value in a partition, use FIRST_VALUE with a frame clause:

SELECT
    customer_id,
    order_date,
    total,
    FIRST_VALUE(total) OVER (PARTITION BY customer_id ORDER BY order_date) AS first_order_total,
    LAST_VALUE(total) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_order_total
FROM orders;

The LAST_VALUE frame clause is essential. Without it, LAST_VALUE defaults to the current row (because the default frame ends at CURRENT ROW), which makes it useless. The explicit frame ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING extends the window to the entire partition, so LAST_VALUE actually returns the last row's value.

Deduplication and First-Touch Patterns

Ranking functions are the foundation of deduplication — keeping one row per entity when the table contains duplicates. The pattern is always the same: rank rows within each entity partition, then filter to the top row.

Suppose you have an events table where each user action is logged, but duplicate events exist due to retries or tracking bugs. You want the earliest event per user:

WITH ranked_events AS (
    SELECT
        event_id,
        user_id,
        event_type,
        event_timestamp,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_timestamp) AS rn
    FROM events
)
SELECT event_id, user_id, event_type, event_timestamp
FROM ranked_events
WHERE rn = 1;

The PARTITION BY user_id creates one window per user. The ORDER BY event_timestamp orders events chronologically within each user. ROW_NUMBER assigns 1 to the earliest event. The outer query keeps only that row.

Choosing the tiebreaker. When multiple rows have the same timestamp, ROW_NUMBER picks one arbitrarily. To make the choice deterministic, add a secondary sort column:

ROW_NUMBER() OVER (
    PARTITION BY user_id
    ORDER BY event_timestamp, event_id
) AS rn

The event_id tiebreaker ensures the same row is selected every time, which matters for reproducible reports.

First-touch attribution. In marketing analytics, "first touch" means the first interaction a user had with each channel. The pattern is identical to deduplication:

WITH first_touch AS (
    SELECT
        user_id,
        channel,
        touch_date,
        ROW_NUMBER() OVER (PARTITION BY user_id, channel ORDER BY touch_date) AS rn
    FROM marketing_touches
)
SELECT user_id, channel, touch_date
FROM first_touch
WHERE rn = 1;

This gives the first date each user interacted with each channel. Partitioning by both user and channel ensures the ranking restarts for each combination.

Keeping the latest instead of the earliest. Flip the ORDER BY direction:

ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_timestamp DESC) AS rn

Now rn = 1 is the most recent event. This is the "latest state" pattern — useful for finding a user's current subscription status, most recent address, or last login date from a history table.

Deduplication with priority. Sometimes you do not want the earliest or latest row — you want the row with the highest priority. Add a priority column and sort by it:

ROW_NUMBER() OVER (
    PARTITION BY user_id
    ORDER BY priority DESC, event_timestamp DESC
) AS rn

This keeps the highest-priority event per user, breaking ties by recency. The flexibility of the ORDER BY clause is what makes the ranking pattern so widely applicable.

NTILE and Bucket Analysis

NTILE distributes rows into a specified number of roughly equal-sized buckets within each partition. It answers the question: "which quartile, decile, or percentile bucket does this row fall into?"

SELECT
    customer_id,
    total_spend,
    NTILE(4) OVER (ORDER BY total_spend DESC) AS spend_quartile
FROM customer_spend;

With 100 customers and NTILE(4), customers 1–25 get quartile 1 (highest spenders), 26–50 get quartile 2, and so on. The buckets are as equal as possible — if the row count is not divisible by the bucket count, the earlier buckets get one extra row.

Use in customer segmentation. Quartiles and deciles are the foundation of tiered customer analysis:

WITH segmented AS (
    SELECT
        customer_id,
        total_spend,
        NTILE(10) OVER (ORDER BY total_spend DESC) AS spend_decile
    FROM customer_spend
)
SELECT
    spend_decile,
    COUNT(*) AS customers,
    MIN(total_spend) AS min_spend,
    MAX(total_spend) AS max_spend,
    AVG(total_spend) AS avg_spend
FROM segmented
GROUP BY spend_decile
ORDER BY spend_decile;

This produces a decile analysis table: how many customers fall into each spending tier, and the spend range within each tier. This is a standard output in marketing and finance reporting.

NTILE with PARTITION BY. You can partition before bucketing:

SELECT
    product_name,
    category,
    price,
    NTILE(3) OVER (PARTITION BY category ORDER BY price DESC) AS price_tier
FROM products;

This splits each category into three price tiers (high, medium, low) independently. A $500 product might be high-tier in one category and mid-tier in another, because the bucketing is relative to the partition.

Limitations. NTILE does not handle ties specially — if two rows have the same value, they may end up in different buckets. If you need value-based bucketing (all rows with the same value in the same bucket), use a CASE expression on the value itself, or use PERCENT_RANK with a threshold:

CASE
    WHEN PERCENT_RANK() OVER (ORDER BY total_spend DESC) <= 0.25 THEN 'Q1'
    WHEN PERCENT_RANK() OVER (ORDER BY total_spend DESC) <= 0.50 THEN 'Q2'
    WHEN PERCENT_RANK() OVER (ORDER BY total_spend DESC) <= 0.75 THEN 'Q3'
    ELSE 'Q4'
END AS spend_quartile

This approach guarantees that rows with identical spend values land in the same quartile, which NTILE does not.