Bucketing with NTILE & Running Totals

# 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`). ```sql 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: ```sql 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.