Share of total, NTILE and FIRST_VALUE

Step 1 Understand it

Why you need it. "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" — all common analytics asks that window functions answer in one line.

Watch it happen

Picture it

pie title Share of revenue by category
  "Electronics" : 440
  "Home" : 150
  "Bags" : 120
  "Kitchen" : 48
  "Stationery" : 35

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.

The shape of it

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)

Words in lower case are the parts you fill in.

How it works, step by step

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.

Analogy

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.

Where the analogy breaks

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.

Worked examples

WITH by_category AS (
  SELECT p.category, SUM(oi.quantity * p.price) AS revenue
  FROM order_items oi
  JOIN products p ON p.product_id = oi.product_id
  GROUP BY p.category
)
SELECT category, revenue,
       ROUND(100.0 * revenue / SUM(revenue) OVER (), 1) AS pct_of_total
FROM by_category
ORDER BY revenue DESC;

SELECT name, salary,
       NTILE(4) OVER (ORDER BY salary DESC, name) AS quartile
FROM employees
ORDER BY salary DESC, name;

SELECT dept_id, name, salary,
       FIRST_VALUE(name) OVER (PARTITION BY dept_id ORDER BY salary DESC) AS top_earner
FROM employees
WHERE dept_id IS NOT NULL
ORDER BY dept_id, salary DESC, name;

Must-know 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.

Step 2 Go further (free, optional)

Optional. The notes above are written to be enough on their own. These free resources give a second explanation and more practice.

• Read

  PostgreSQL docs — Window Functions (NTILE, FIRST_VALUE, LAST_VALUE)

  official documentation, free

• Read

  Mode SQL Tutorial — SQL Window Functions: NTILE

  Mode, free

• Practice

  DataLemur — percentage of total questions

  real interview questions, many free

Step 3 Practise

Try each one yourself first: out loud, or written and run in the practice box below, which saves every query you run. Then open the answer and compare.

1. ExplainSay what it does and when to use it, in plain words, with no notes.

   Explain why SUM(revenue) OVER () puts the same number on every row.

   Show answer

   OVER () is an empty window. There is no PARTITION BY, so every row is in one big window: the whole result. There is no ORDER BY, so there is no running frame: the window is always all of it. SUM of the same rows is the same number, so every row gets the grand total.

   That is exactly what share of total needs: each row divided by the same grand total.

2. Writefrom a blank page, then run it

   For each customer, show their total spend and their percentage of all spending, rounded to 1 decimal place.

   Show answer

   First add up each customer (the CTE), then divide each total by the sum of all totals. Multiply by 100.0, not 100, so the division keeps its decimals.

   The rounded shares add up to 100.1, not 100. That is normal when you round each part.

   sqlCopy

   WITH per_customer AS (
     SELECT customer_id, SUM(amount) AS total
     FROM orders
     GROUP BY customer_id
   )
   SELECT c.name, p.total,
          ROUND(100.0 * p.total / SUM(p.total) OVER (), 1) AS pct_of_all
   FROM per_customer p
   JOIN customers c ON c.customer_id = p.customer_id
   ORDER BY p.total DESC;

   Try it

   4 rows

   name	total	pct_of_all
   Priya	425	53.6
   Tom	275	34.7
   Lena	60	7.6
   Zoe	33	4.2

3. Predictsay it before you run it

   What quartile would each employee get with NTILE(3) instead of NTILE(4)?

   Show answer

   8 rows into 3 buckets don't divide evenly, so the first buckets get the extra rows: sizes 3, 3 and 2.

   • Bucket 1: Asha, Hana, Ben.
   • Bucket 2: Chen, Dara, Fatima.
   • Bucket 3: Eli, Gus.

   Notice Ben and Chen earn the same but land in different buckets. NTILE cuts by position, not by value; the name in the ORDER BY decides which side of the cut each one lands on.

   sqlCopy

   SELECT name, salary, NTILE(3) OVER (ORDER BY salary DESC, name) AS bucket
   FROM employees
   ORDER BY salary DESC, name;

   Try it

   8 rows

   name	salary	bucket
   Asha	95000	1
   Hana	80000	1
   Ben	70000	1
   Chen	70000	2
   Dara	60000	2
   Fatima	52000	2
   Eli	45000	3
   Gus	38000	3

4. Edge casesSay what happens with NULLs, ties, duplicates or no rows at all, and fix it.

   Write LAST_VALUE(name) for the lowest earner in each department and make it return the right name on every row.

   Show answer

   Sort each department from highest to lowest salary, so the lowest earner is the last row. Then set the frame to the whole partition: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

   Without that frame, the default ends at the current row, so on each row "the last row of the window" is the current row: every person is named as their own lowest earner.

   In department 1, Ben and Chen tie for lowest. The name in the ORDER BY makes Chen the last row, so the tie is broken the same way every time.

   sqlCopy

   SELECT dept_id, name, salary,
          LAST_VALUE(name) OVER (
            PARTITION BY dept_id
            ORDER BY salary DESC, name
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
          ) AS lowest_earner
   FROM employees
   WHERE dept_id IS NOT NULL
   ORDER BY dept_id, salary DESC, name;

   Try it

   7 rows

   dept_id	name	salary	lowest_earner
   1	Asha	95000	Chen
   1	Hana	80000	Chen
   1	Ben	70000	Chen
   1	Chen	70000	Chen
   2	Dara	60000	Eli
   2	Eli	45000	Eli
   3	Fatima	52000	Fatima

Step 4 Interview-ready, then prove it

Your interview answer

About 20 to 40 seconds, said out loud.

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, and 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.

Follow-up questions

Tick each check when you can do it without notes