By Prakhar Shrivastava · October 16, 2026

With ascending spend, CUME_DIST answers “What share of customers spent this amount or less?” PERCENT_RANK gives a relative rank based on the first position of the tied group. Neither function measures a customer’s share of revenue. Choose the question before labeling a dashboard column “percentile.”

Define the population before ranking

Our fictional extract has one row per customer for one complete reporting month. US customers spent $50, $100, $100, and $200. Two UK customers each spent $80, and one German customer spent $120. All figures are already converted to USD under an assumed reporting policy; there is no currency conversion in the query.

We compare customers within each market. The example excludes missing amounts and assumes customer keys are valid and unique within a market. A production report should state whether refunds, zero-spend customers, and inactive customers belong in this population. Changing that inclusion rule changes the result even if an individual customer’s spend stays constant.

Run the complete SQL example

WITH spend(market, customer_id, spend_usd) AS (
 VALUES ('US',1,50),('US',2,100),('US',3,100),('US',4,200),
        ('UK',5,80),('UK',6,80),('DE',7,120)
)
SELECT market, customer_id, spend_usd,
 PERCENT_RANK() OVER (PARTITION BY market ORDER BY spend_usd) AS relative_rank,
 CUME_DIST() OVER (PARTITION BY market ORDER BY spend_usd) AS cumulative_share
FROM spend
ORDER BY market, spend_usd, customer_id;

Read the US result without confusing two denominators

The $50 customer has relative rank 0 and cumulative share 0.25. Both $100 customers have relative rank 1/3 (approximately 0.3333) and cumulative share 0.75. The $200 customer has 1 for both measures.

For either $100 customer, rank is 2. PERCENT_RANK calculates (2 – 1) / (4 – 1) = 1/3. CUME_DIST counts three customers at or below $100 and divides by four, giving 3/4. The $100 tie is included in full in that cumulative count.

If a stakeholder asks what fraction spent no more than $100, the answer in this population is 75%. Calling the 33.33% relative rank “the fraction below $100” would also be wrong: only one of four customers spent strictly less, which is 25%. These are three different statements about the same records.

Test all-equal and one-customer markets

Both UK customers receive PERCENT_RANK 0 and CUME_DIST 1. All customers share the first rank, and all are at or below the shared $80 value. This does not mean the UK customers spent nothing or contributed all company revenue. Their combined spend is $160; the rank outputs describe positions within the UK population.

The German customer receives 0 and 1 respectively in this tested SQLite example. A one-row group has no meaningful separation into high and low spenders. Display the population size alongside a relative measure so a user can distinguish one customer from a large, varied market.

Do not break meaningful ties just to make output stable

The window orders only by spend_usd. The final ORDER BY also includes customer_id to make the displayed rows stable. Adding customer_id inside the window would make the two $100 customers different ordered values and change their ranks. A display-order requirement should not silently redefine equality in the metric.

Descending order changes the interpretation too: the cumulative share then counts customers preceding or tied with the current customer under descending spend. State the direction in the metric definition. If you need equal-sized row buckets instead of a distribution measure, read why NTILE can split equal-spend customers.

Validate a dashboard threshold before using it

Suppose someone proposes selecting rows where cumulative_share is at most 0.5. In the US example that selects only the $50 customer, or 25% of the four customers, because the next tied group takes the cumulative share straight to 75%. A threshold on a tied distribution does not promise an exact number of selected people. Decide whether keeping ties together matters more than filling an exact quota.

Three partition assertions passed in SQLite 3.50.4: the complete US result, the two all-equal UK results, and the one-customer German result. Keep these edge cases when adapting the query to your warehouse. For fundamentals, see the SQL window-functions guide. Before calculating ranks, audit repeated business keys so duplicate records do not inflate the population.

Primary references: PostgreSQL window functions for definitions and peer behavior, and SQLite window functions for the tested engine. A useful report names the population, direction, tie rule, and business interpretation alongside the number.