Buckets with NTILE in SQL

NTILE splits ordered rows into equal-sized buckets — quartiles, deciles, cohorts.

SELECT
    customer_id,
    lifetime_value,
    NTILE(4) OVER (
        ORDER BY lifetime_value DESC
    ) AS quartile,
    FIRST_VALUE(lifetime_value) OVER (
        ORDER BY lifetime_value DESC
    ) AS best_customer
FROM customer_totals;

How it works

NTILE(4) labels each row with its quartile.
FIRST_VALUE reads the top row of the window.
Filter on the bucket in an outer query, not in the same SELECT.

Keywords and builtins used here

AS
BY
DESC
FROM
ORDER
SELECT

The run, in numbers

Lines: 10
Characters to type: 200
Tokens: 35
Three-star pace: 95 tpm

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

Type this snippet

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

← Previous Next →