NTILE and PERCENT_RANK, for a report that wants quartiles

A report asking for the top quartile of customers by spend was a subquery computing a count, a second computing a threshold, and arithmetic in PHP.

SELECT
  customer_id,
  spend,
  NTILE(4)      OVER w AS quartile,
  PERCENT_RANK() OVER w AS pct,
  CUME_DIST()    OVER w AS cume
FROM customer_totals
WINDOW w AS (ORDER BY spend DESC);

NTILE divides into buckets of as equal a size as possible, which means with 10 rows and 4 buckets the first two get three each — that is defined behaviour and surprises people expecting equal ranges rather than equal counts. PERCENT_RANK is zero-based and CUME_DIST is not, which is the distinction that matters when the number ends up on a slide. All three need the ordering to be deterministic or the buckets shift between runs.