Bucketing with NTILE & Running Totals

Two patterns show up in this module's quiz and coding exercises that the videos didn't explicitly walk through: the NTILE window function, and the mechanics behind a running total.

NTILE(n): splitting a partition into equal-ish buckets

RANK and DENSE_RANK number individual rows. NTILE(n) does something different: it divides the ordered partition into n groups of as-close-to-equal size as possible, and labels each row with its bucket number (1 through n).

WITH customer_spend AS (
  SELECT CustomerId, SUM(Total) AS TotalSpend
  FROM Invoice GROUP BY CustomerId
)
SELECT CustomerId, TotalSpend,
       NTILE(4) OVER (ORDER BY TotalSpend DESC) AS SpendQuartile
FROM customer_spend
ORDER BY TotalSpend DESC;

Ordering by TotalSpend DESC means bucket 1 holds the highest spenders. This is the classic use case for NTILE: quartiles, deciles, or any "split the population into N equal-sized groups" segmentation -- something neither RANK nor ROW_NUMBER can do on their own, since both number rows individually rather than grouping them into buckets.

Like any window function, NTILE can be combined with PARTITION BY to compute buckets independently per group (e.g. spend quartiles within each country, rather than across the whole customer base).

Running totals: what the frame default actually does

A window function operates over a "frame" of rows -- and so far, every example you've seen has either used the whole partition (no ORDER BY) or ranked every row against every other row in its partition. A running total relies on a third behavior: when you add an ORDER BY inside OVER(...) without an explicit frame clause, the default frame becomes:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

In plain terms: "every row from the start of the partition up to and including the current row." That's exactly what makes SUM() OVER (ORDER BY ...) behave as a running total instead of a whole-partition sum:

SELECT InvoiceId, CustomerId, InvoiceDate, Total,
       SUM(Total) OVER (PARTITION BY CustomerId ORDER BY InvoiceDate) AS Running_Total
FROM Invoice
ORDER BY CustomerId, InvoiceDate;

Without the ORDER BY, SUM(Total) OVER (PARTITION BY CustomerId) would just repeat the same full-partition total on every row -- exactly like the AVG/MIN/MAX examples from the first Window Functions video. Adding ORDER BY is what turns a flat aggregate into a row-by-row accumulation.

Recap

• NTILE(n) divides an ordered partition into n equal-ish buckets -- use it for quartile/decile-style segmentation, not individual row ranking.
• Adding ORDER BY inside OVER(...) changes the default frame to "start of partition through current row," which is what makes a running total possible -- without it, the aggregate applies to the whole partition uniformly.