Share of total, NTILE and FIRST_VALUE
x / SUM(x) OVER () gives each row's share of the total. NTILE(n) splits the ordered rows into n roughly equal buckets. FIRST_VALUE and LAST_VALUE pull a value from the first or last row of the window.
Why it matters
"What percentage of revenue does each category bring?", "put customers into spending quartiles", "show the top earner's name next to everyone in their department". These are all common analytics requests, and window functions answer each one in a single line.
The idea
SUM(revenue) OVER () is the grand total, written on every row, so 100.0 * revenue / SUM(revenue) OVER () is the percentage of the total. With PARTITION BY you get the share within each group instead.
NTILE(4) OVER (ORDER BY salary DESC) deals the sorted rows into 4 buckets, like dealing cards: 1 is the top quarter.
FIRST_VALUE(name) OVER (PARTITION BY dept_id ORDER BY salary DESC) is the name on the first row of each department's window — the top earner.
A pie chart, a league split into divisions, and a team captain. Share of total is each slice of the pie; NTILE puts teams into divisions by points; FIRST_VALUE is the captain's name printed on every player's shirt.
Divisions in a league can be unequal by design. NTILE always makes buckets as equal as possible, so tied values can land in different buckets — fine for "roughly top 25%", wrong if ties must stay together.
Syntax
100.0 * x / SUM(x) OVER (PARTITION BY g)
NTILE(4) OVER (ORDER BY x DESC)
FIRST_VALUE(col) OVER (PARTITION BY g ORDER BY x DESC)How it works
- A CTE adds up revenue per category: Electronics 440, Home 150, Bags 120, Kitchen 48, Stationery 35.
SUM(revenue) OVER ()is 793 on every row.- 100.0 × 440 ÷ 793 = 55.5% for Electronics, and so on.
NTILE(4)over 8 employees sorted by salary gives buckets of 2: 1, 1, 2, 2, 3, 3, 4, 4.FIRST_VALUE(name), ordered by salary DESC within each department, is that department's top earner.
Key points
SUM(x) OVER ()= grand total on every row;SUM(x) OVER (PARTITION BY g)= group total.- Share of total:
100.0 * x / SUM(x) OVER (…)— the 100.0 avoids integer division. NTILE(n)makes n buckets as equal in size as possible, in the window's order.- FIRST_VALUE is safe with the default frame; LAST_VALUE needs
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. - You can use a window function over an aggregate (
SUM(SUM(x)) OVER ()) in a GROUP BY query, but a CTE is easier to read.
Common mistakes
- Integer division in a share:
revenue / SUM(revenue) OVER ()gives 0 for every row. - Using LAST_VALUE with the default frame and getting the current row's value.
- Expecting NTILE to keep tied values in the same bucket.
Not true: NTILE(4): rows with the same value land in the same bucket.
In fact: NTILE only makes the buckets as equal in size as possible, so tied values can be split between two buckets. Use a rank when ties must stay together.
In the interview
For share-of-total I divide each row by SUM over an empty or partitioned window, multiplying by 100.0 to avoid integer division. NTILE buckets ordered rows into n near-equal groups, for quartiles or deciles. FIRST_VALUE picks the first row's value in each window. For LAST_VALUE I widen the frame to unbounded following, because the default frame stops at the current row.
Part of Unit 5: Window functions: ranking, running totals and comparing rows in the free SQL course.