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

  1. A CTE adds up revenue per category: Electronics 440, Home 150, Bags 120, Kitchen 48, Stationery 35.
  2. SUM(revenue) OVER () is 793 on every row.
  3. 100.0 × 440 ÷ 793 = 55.5% for Electronics, and so on.
  4. NTILE(4) over 8 employees sorted by salary gives buckets of 2: 1, 1, 2, 2, 3, 3, 4, 4.
  5. FIRST_VALUE(name), ordered by salary DESC within each department, is that department's top earner.

Key points

Common mistakes

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.