Where a row sits in the distribution in SQL

Not the rank but the fraction: how much of the table is behind this row?

SELECT
    name,
    score,
    ROUND(PERCENT_RANK() OVER by_score, 3) AS pct_below,
    ROUND(CUME_DIST() OVER by_score, 3) AS cumulative,
    NTILE(4) OVER by_score AS quartile
FROM players
WINDOW by_score AS (
    ORDER BY score
);

How it works

1. PERCENT_RANK runs 0 to 1 and answers what share ranks below.
2. CUME_DIST includes the current row, so it never returns 0.
3. NTILE(4) is the same idea rounded into four buckets.

Keywords and builtins used here

• AS
• BY
• FROM
• ORDER
• SELECT

The run, in numbers

Lines

10

Characters to type

210

Tokens

50

Three-star pace

95 tpm

At the three-star pace of 95 tokens a minute, this run takes about 32 seconds.

Type this snippet

Step 9 of 9 in Window functions, step 15 of 24 in Analytics & reporting.

← Previous Next →