SQL8 min readJan 2025

SQL Window Functions Are the Most Underused Tool in Data Analytics

Stop writing subqueries for this

Window functions are the feature that separates analysts who write SQL from analysts who think in SQL.

Most people learn SELECT, WHERE, GROUP BY — and stop. Then they write nested subqueries 40 lines long to answer "what's each customer's most recent order?" Window functions answer that in 3 lines, run faster, and are far easier to read.

Here's the practical guide I wish I had when starting out.

The Core Concept: Partitions and Frames

A window function operates on a set of rows related to the current row — without collapsing them like GROUP BY does. You define the window with OVER().

SELECT
  customer_id,
  order_date,
  order_value,
  SUM(order_value) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total
FROM orders;

This gives you each individual row plus a running total per customer. GROUP BY can't do that — it would collapse all rows per customer into one.

ROW_NUMBER: The One You'll Use Every Day

The most common pattern: get the most recent record per group.

WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY created_at DESC
    ) AS rn
  FROM orders
)
SELECT * FROM ranked WHERE rn = 1;

That's "latest order per customer" — the most common analytics query in existence — in 10 clean lines. No subquery spaghetti.

LAG and LEAD: Period-over-Period in Seconds

SELECT
  month,
  revenue,
  LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_revenue,
  revenue - LAG(revenue, 1) OVER (ORDER BY month) AS mom_change
FROM monthly_revenue;

Month-over-month variance. No self-joins. No CTEs. No drama.

NTILE: Segment Your Customers Instantly

SELECT
  customer_id,
  total_spend,
  NTILE(4) OVER (ORDER BY total_spend DESC) AS spend_quartile
FROM customer_summary;

Quartile 1 = top 25% spenders. Instant customer segmentation. Perfect for targeting campaigns.

PERCENT_RANK: Where Does This Record Sit?

SELECT
  employee_id,
  salary,
  ROUND(PERCENT_RANK() OVER (
    PARTITION BY department ORDER BY salary
  ) * 100, 1) AS salary_percentile
FROM employees;

Salary benchmarking by department in 6 lines. HR teams love this.

Performance Note

Window functions process after WHERE and GROUP BY but before HAVING and SELECT — so they can't filter themselves. Wrap in a CTE or subquery to filter on window function results. Also: ensure your PARTITION BY columns are indexed if running on large tables.