Ranking Functions

Both express a row's position in the distribution as a fraction between 0 and 1, but they compute different things. PERCENT_RANK answers "what fraction of rows rank strictly below this row?" It is computed as (rank - 1) / (total_rows - 1). The lowest value always gets 0.0 (zero rows rank below it) and the highest always gets 1.0. Tied rows get the same PERCENT_RANK value. If two rows share the minimum value, both get PERCENT_RANK = 0.0.

CUME_DIST answers "what fraction of rows have a value less than or equal to this row's value?" It is computed as rows_with_value_leq_current / total_rows. CUME_DIST is always greater than zero — even the lowest-value rows have themselves counted, so the minimum is 1/total_rows. For the highest value, CUME_DIST = 1.0. All rows sharing the same value get the same CUME_DIST value, and it includes themselves in the count.

Practical implication: PERCENT_RANK is better for "is this row in the top X%?" comparisons — WHERE PERCENT_RANK() OVER (...) >= 0.8 finds the top 20% cleanly. CUME_DIST is useful when you want to know what proportion of the population has a value at or below a specific point — for example, "what fraction of orders are at or below ₹500?" Both are window functions requiring OVER() with ORDER BY — unlike PERCENTILE_CONT and PERCENTILE_DISC which are ordered-set aggregates that collapse rows like GROUP BY.

NTILE(n) divides rows into exactly n buckets with approximately equal row counts — it is a row-count-based division. NTILE(4) assigns each row to quartile 1, 2, 3, or 4, where each quartile contains roughly 25% of the rows. The bucket assignment depends on row position, not value — two rows with very different values could both be in quartile 3 if they are adjacent in the ordered sequence.

PERCENT_RANK is value-based — it computes the row's fractional rank relative to the distribution of values. Filtering WHERE PERCENT_RANK() OVER (...) >= 0.75 finds the top 25% by value, not by count. If the top 5% of customers account for 60% of revenue (a right-skewed distribution), NTILE splits 25% of customers per bucket regardless of value distribution, while PERCENT_RANK >= 0.75 identifies customers whose spend puts them in the top 25% by value rank.

Choose NTILE for equal-count segments — marketing campaigns that need to reach exactly 25% of the customer base per segment, A/B test splits where equal group sizes matter. Choose PERCENT_RANK for value-based thresholds — "find customers above the 75th percentile of spending", "identify products in the bottom 10% of revenue", "classify orders by where they fall in the distribution". NTILE can produce unintuitive results when the data is skewed — a product that just barely makes the top quartile may be far below the actual 75th percentile value if most revenue is concentrated in the top few products. For business reporting where "top 25%" means value-based, PERCENT_RANK is more meaningful.

When using ROW_NUMBER for deduplication (keeping exactly one row per group), ties in the ORDER BY column create non-deterministic behaviour — the database may assign rn = 1 to any of the tied rows, and the choice may change between query executions or database versions. This is a correctness problem: the same query run twice could return different rows, making results unreproducible.

The solution is always to add a tiebreaker column as the final ORDER BY key — a column that is guaranteed unique within the partition, typically the primary key. ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC, order_id DESC) — if two orders have the same order_date, the one with the higher order_id gets rn = 1. This ensures deterministic, reproducible results regardless of physical row order or internal optimiser decisions.

The tiebreaker column choice matters semantically: order_id DESC for the most recently inserted duplicate, created_at ASC for the original/oldest record. Document the tiebreaker logic — when someone asks "which record do you keep when there are duplicates?", the ORDER BY clause inside ROW_NUMBER should have a clear, business-meaningful answer. For RANK and DENSE_RANK (which are designed to express ties), no tiebreaker is needed — tied rows intentionally share the same rank. Only ROW_NUMBER requires a tiebreaker because its contract is to produce unique sequential integers.

Both compute the value at a specified percentile of a distribution, but they use different methods for non-integer positions. PERCENTILE_CONT uses linear interpolation — when the percentile falls between two actual data values, it returns the weighted average of those two values. PERCENTILE_DISC returns the nearest actual value in the dataset — no interpolation.

For the median (50th percentile) of [100, 200, 300, 400]: PERCENTILE_CONT(0.5) returns 250 (the midpoint of 200 and 300, interpolated). PERCENTILE_DISC(0.5) returns 200 or 300 (the actual data value nearest the 50th percentile position). For an odd number of values like [100, 200, 300]: both would return 200 since 200 is exactly at the 50th percentile.

Choose PERCENTILE_CONT for statistical analysis where mathematical precision matters — it follows the standard definition of percentile that most statisticians and data scientists expect. Choose PERCENTILE_DISC when you need the result to be an actual value from the dataset — for example, when selecting the median salary to identify a specific employee as the median earner. These are ordered-set aggregates (unlike the window function PERCENT_RANK) — they use WITHIN GROUP (ORDER BY column) syntax and collapse rows like GROUP BY. They do not have OVER() — they are called like standard aggregate functions: SELECT store_id, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total_amount) AS median FROM orders GROUP BY store_id.

The pattern requires computing ranks for two time periods independently, then comparing them. Use two CTEs — one per period — each aggregating the relevant metric (revenue, order count, score) per entity. Then a final SELECT joins the two CTEs and applies RANK() to each period's metric with separate OVER() clauses. The rank movement is the previous period rank minus the current period rank — positive means improved (moved up), negative means declined (moved down), zero means unchanged.

Implementation: WITH current_period AS (SELECT entity_id, SUM(metric) AS score FROM ... WHERE date IN current_range GROUP BY entity_id), previous_period AS (SELECT entity_id, SUM(metric) AS score FROM ... WHERE date IN previous_range GROUP BY entity_id), combined AS (SELECT COALESCE(c.entity_id, p.entity_id) AS entity_id, COALESCE(c.score, 0) AS current_score, COALESCE(p.score, 0) AS prev_score FROM current_period c FULL OUTER JOIN previous_period p ON c.entity_id = p.entity_id) SELECT *, RANK() OVER (ORDER BY current_score DESC) AS current_rank, RANK() OVER (ORDER BY prev_score DESC) AS prev_rank, RANK() OVER (ORDER BY prev_score DESC) - RANK() OVER (ORDER BY current_score DESC) AS rank_change FROM combined.

Two important details: use FULL OUTER JOIN between periods to include entities that appear in one period but not the other (new entrants have no previous rank; churned entities have no current rank). Use COALESCE(score, 0) so new entrants get rank assigned based on zero score rather than being excluded. For the movement indicator display, convert rank_change to a symbol: CASE WHEN rank_change > 0 THEN '▲ ' || rank_change WHEN rank_change < 0 THEN '▼ ' || ABS(rank_change) ELSE '—' END. For entities that did not exist in the previous period, show 'NEW' instead of a movement arrow. The FULL OUTER JOIN naturally handles this — entities with no previous record have prev_rank based on zero score.

Cause: The OVER() clause is missing ORDER BY or has an ORDER BY with all identical values. PERCENT_RANK requires ORDER BY to define the ranking — without it, all rows are considered tied at the same rank, making (rank - 1) / (total - 1) = 0 for all rows. Alternatively, all rows genuinely have the same value in the ORDER BY column, making all ranks equal.

Fix: Add ORDER BY to the OVER clause: PERCENT_RANK() OVER (ORDER BY total_amount). Verify that the ORDER BY column has distinct values — check SELECT COUNT(DISTINCT total_amount) FROM table. If all values truly are identical, PERCENT_RANK correctly returns 0.0 for all rows (all tied at rank 1). If ORDER BY is present and values vary but PERCENT_RANK still shows 0.0, check if PARTITION BY has created single-row partitions — a partition of one row always has PERCENT_RANK = 0.0.

Cause: NTILE distributes rows as evenly as possible, but when total rows is not evenly divisible by n, some buckets get one extra row. NTILE(4) on 10 rows produces buckets of sizes 3, 3, 2, 2 — not all size 2 or 3. This is correct behaviour — NTILE makes no guarantee of exact equal sizes, only approximately equal sizes.

Fix: This is expected and correct — document it to stakeholders. The standard definition of NTILE distributes the remainder rows to the first buckets. To get exact equal-count segments regardless of remainder, use PERCENT_RANK for value-based thresholds instead: WHERE PERCENT_RANK() OVER (ORDER BY col) >= 0.75 for the top 25%. This is value-based (not row-count-based) but conceptually matches what most people mean by 'top quartile'. If exact row counts per bucket are required, calculate the target rows per bucket (FLOOR(COUNT(*) / n)) and use ROW_NUMBER with arithmetic to assign buckets manually.

Cause: The ORDER BY inside ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) has ties — multiple rows share the same ORDER BY value. When two rows have identical sort values, the database can assign either one as rn = 1, and this choice may differ between executions, server restarts, or after updates to table statistics. The result is non-deterministic deduplication.

Fix: Add a unique column as the final tiebreaker in the ORDER BY: ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC, order_id DESC). The order_id is unique, so no two rows will have identical (order_date, order_id) combinations. The tiebreaker guarantees deterministic assignment — the row with the highest order_id among those with the same order_date always gets rn = 1. Document which tiebreaker was chosen and why — it represents a business decision about which duplicate to keep.

Cause: PERCENTILE_CONT and PERCENTILE_DISC are ordered-set aggregate functions, not window functions. They use a completely different syntax: PERCENTILE_CONT(fraction) WITHIN GROUP (ORDER BY column). Attempting to add OVER() to them (PERCENTILE_CONT(0.5) OVER (PARTITION BY ...)) is a syntax error — they do not support the window function OVER() clause.

Fix: Use the correct ordered-set aggregate syntax with GROUP BY for per-group percentiles: SELECT store_id, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total_amount) AS median FROM orders GROUP BY store_id. For a per-row percentile rank (window function equivalent), use PERCENT_RANK() OVER (ORDER BY col) instead — this is the window function version that works with OVER() and PARTITION BY. The two serve different purposes: PERCENTILE_CONT returns the VALUE at a percentile; PERCENT_RANK returns the RANK of a row as a fraction.

Cause: When computing ranks at multiple levels (store rank within city AND city rank overall), using a single PARTITION BY that mixes both levels can produce incorrect results. For example, RANK() OVER (PARTITION BY city ORDER BY city_total_revenue DESC) when applied to a row-level table assigns ranks based on repeated city_total_revenue values — every store in the same city gets city_total_revenue rank = 1 because they all share the same value.

Fix: Compute each ranking level independently in its own CTE. CTE 1: aggregate to store level and compute store_rank OVER (PARTITION BY city ORDER BY store_revenue DESC). CTE 2: aggregate to city level and compute city_rank OVER (ORDER BY city_revenue DESC). CTE 3: join the two CTEs to produce the combined result. Never attempt to compute city-level ranks in a query that still has store-level granularity — the duplicate city values in every store row will produce incorrect rank assignments.

Build a comprehensive product ranking report using multiple ranking functions. The report should show for each product: product_id, product_name, category, unit_price, margin_pct (rounded to 1dp), total revenue from delivered orders (0 if none), times_ordered (distinct order count), and the following rankings: (1) price_rank — RANK by unit_price DESC within category, (2) revenue_rank — RANK by total revenue DESC within category, (3) overall_revenue_rank — DENSE_RANK by total revenue DESC across all products, (4) revenue_percentile — PERCENT_RANK by revenue across all products (as a 0–100 value rounded to 1dp), (5) revenue_quartile — NTILE(4) by revenue across all products (Q4 = highest), (6) a performance_tier: 'Elite' if both price_rank and revenue_rank are <= 2, 'Revenue Leader' if revenue_rank = 1, 'Premium Priced' if price_rank = 1, 'Standard' otherwise. Sort by category then revenue_rank.