More Window Functions: NTILE, FIRST_VALUE, Frame Clauses

This builds on the Window Functions lesson — covering NTILE, FIRST_VALUE/LAST_VALUE, and the frame clause that controls exactly which rows a window function looks at.

NTILE — Split Rows Into N Equal Buckets

SELECT name, salary,
  NTILE(4) OVER (ORDER BY salary DESC) AS salary_quartile
FROM employees;
-- splits employees into 4 roughly equal groups by salary rank — quartile 1 is the top earners

FIRST_VALUE and LAST_VALUE

Return a value from the first/last row of the current window frame.

SELECT name, department, salary,
  FIRST_VALUE(name) OVER (PARTITION BY department ORDER BY salary DESC) AS top_earner
FROM employees;
-- every row in a department shows that department's highest earner's name

LAST_VALUE needs an explicit frame (see below) to behave as most people expect — by default it only sees up to the current row, not the whole partition.

The Frame Clause: ROWS / RANGE BETWEEN

The frame clause defines exactly which rows, relative to the current one, a window function considers — this is what makes moving averages and running totals possible.

SELECT month, revenue,
  AVG(revenue) OVER (
    ORDER BY month
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg_3mo
FROM monthly_sales;
-- a 3-month moving average: current row + the 2 rows before it

SELECT month, revenue,
  SUM(revenue) OVER (
    ORDER BY month
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM monthly_sales;
-- explicit version of the running total pattern from the earlier Window Functions lesson

Fixing LAST_VALUE

SELECT name, department, salary,
  LAST_VALUE(name) OVER (
    PARTITION BY department
    ORDER BY salary DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS lowest_earner
FROM employees;
-- extending the frame to the WHOLE partition makes LAST_VALUE see the true last row

Common Errors & Troubleshooting

• LAST_VALUE returning the same value as the current row — this is the default frame behavior (only sees up to the current row); you must widen the frame to ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to make it see the true last row of the partition.
• NTILE(4) producing unevenly-sized buckets — this is expected when the row count isn't evenly divisible by 4; PostgreSQL distributes the remainder across the first few buckets rather than erroring.
• Moving average including too many/too few rows — double-check the frame bounds (ROWS BETWEEN N PRECEDING AND CURRENT ROW) match the window size you actually intend — an off-by-one here is easy to make.

Key Takeaways

• NTILE(n) buckets ranked rows into n roughly equal-sized groups — handy for quartiles/percentile buckets.
• LAST_VALUE is commonly misunderstood: without widening the frame to UNBOUNDED FOLLOWING, it only sees up to the current row.
• The frame clause (ROWS BETWEEN ... AND ...) is what actually defines a moving average window — ORDER BY alone isn't enough.