SQL Window Functions: The Complete Guide for Data Engineers

dTechStack Team

Apr 24, 2026

10 min read·SQL

SQLWindow FunctionsAnalyticsBigQuery

RANK, DENSE_RANK, ROW_NUMBER, LAG, LEAD, NTILE — each explained with real business use cases and query plans.

📬 Weekly Newsletter

Get one deep-dive article every Saturday. No spam. Unsubscribe anytime.

Window functions are the most powerful feature in analytical SQL and the most commonly misunderstood. Unlike GROUP BY which collapses rows into aggregations, window functions compute a value across related rows while keeping every row in the result.

The OVER Clause

Every window function uses OVER() to define the window — the set of rows it operates on:

**PARTITION BY** — divides rows into groups

**ORDER BY** — defines row order within each partition

**ROWS/RANGE BETWEEN** — defines the frame

sql

SELECT
    order_id, customer_id, amount,
    SUM(amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM orders;

Ranking Functions

Ranking functions assign a number to each row within its partition:

sql

SELECT
    customer_id, order_date, amount,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn,
    RANK()       OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rnk,
    DENSE_RANK() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS dense_rnk
FROM orders;

-- Get latest order per customer
SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
    FROM orders
) t WHERE rn = 1;

⚡dTechStack Pro

Get unlimited access to all SQL tutorials & animated diagrams

1,000+ animated tutorials · 100+ AI tools · Mock interviews · Certifications

Try Pro free →

LAG and LEAD: Adjacent Rows

LAG and LEAD access values from previous/next rows without a self-join:

sql

SELECT
    order_date, revenue,
    LAG(revenue, 1) OVER (ORDER BY order_date) AS prev_day_revenue,
    revenue - LAG(revenue, 1) OVER (ORDER BY order_date) AS daily_change
FROM daily_revenue ORDER BY order_date;

NTILE: Bucketing

NTILE(N) divides rows into N equal-sized buckets:

sql

SELECT
    customer_id, total_spend,
    NTILE(4) OVER (ORDER BY total_spend DESC) AS quartile,
    CASE NTILE(4) OVER (ORDER BY total_spend DESC)
        WHEN 1 THEN 'Top 25%' WHEN 2 THEN 'Upper Mid'
        WHEN 3 THEN 'Lower Mid' WHEN 4 THEN 'Bottom 25%'
    END AS segment
FROM customer_spending;