SQL Window Functions Every Finance Analyst Should Know

⚡ Key Takeaways

Window functions perform calculations across rows related to the current row — without collapsing them like GROUP BY does
ROW_NUMBER, RANK, DENSE_RANK — essential for customer and product ranking reports
LAG and LEAD — the fastest way to build period-over-period comparisons in SQL
SUM() OVER() — running totals without self-joins or subqueries
NTILE — instantly segment customers into quartiles for LTV analysis

Why Finance Analysts Need Window Functions

Most SQL analysts learn SELECT, WHERE, GROUP BY, and JOIN — and stop there. That covers 80% of queries. But the remaining 20% — the queries that produce genuinely insightful finance reports — almost always require window functions.

Window functions let you perform calculations across a set of rows that are related to the current row, while keeping all the individual rows in your result. The key difference from GROUP BY: GROUP BY collapses rows into one per group. Window functions add a calculated column without collapsing anything.

Every example below uses a real finance scenario you can adapt immediately.

The Syntax — Understanding OVER()

All window functions share the same structure:

Window Function Syntax

FUNCTION_NAME() OVER (
    PARTITION BY column_name    -- optional: reset per group
    ORDER BY     column_name    -- optional: defines row order
    ROWS BETWEEN ...           -- optional: window frame
)

The PARTITION BY clause resets the calculation for each group — similar to GROUP BY but without collapsing. The ORDER BY clause defines the order within the partition. The ROWS BETWEEN clause defines which rows to include in the calculation window.

1. ROW_NUMBER — Ranking Your Best Customers

ROW_NUMBER assigns a unique sequential number to each row within a partition. Classic use case: rank customers by total revenue to find your top 10.

PostgreSQL — Customer Revenue Ranking

SELECT
    customer_id,
    customer_name,
    segment,
    total_revenue,
    ROW_NUMBER() OVER (
        ORDER BY total_revenue DESC
    ) AS revenue_rank,
    ROW_NUMBER() OVER (
        PARTITION BY segment
        ORDER BY total_revenue DESC
    ) AS rank_within_segment
FROM customer_revenue_summary
ORDER BY revenue_rank;

-- Result: every customer ranked globally AND within their segment
-- customer_id | name     | segment    | revenue | rank | seg_rank
-- C-00247     | Acme Ltd | Enterprise | 284,420 |  1   |    1
-- C-00891     | Beta Co  | Mid-Market | 142,810 |  2   |    1
-- C-01204     | Gamma Inc| Enterprise |  98,340 |  3   |    2

✅

Finance use case

Wrap this in a CTE and filter WHERE revenue_rank <= 10 to get your top 10 customers. Add PARTITION BY region to get top 10 per region simultaneously — one query, no UNION.

2. LAG and LEAD — Period-Over-Period Comparisons

LAG() accesses the value from a previous row. LEAD() accesses the value from a following row. This is the cleanest way to build month-over-month or year-over-year variance reports without self-joins.

PostgreSQL — Monthly Revenue MOM Variance

SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,

    revenue - LAG(revenue) OVER (ORDER BY month)
        AS mom_variance,

    ROUND(
        (revenue - LAG(revenue) OVER (ORDER BY month))
        / NULLIF(LAG(revenue) OVER (ORDER BY month), 0) * 100, 1
    ) AS mom_pct_change

FROM monthly_revenue
ORDER BY month;

-- month    | revenue | prev_revenue | variance | pct_change
-- 2025-01  | 38,200  |     NULL     |   NULL   |    NULL
-- 2025-02  | 41,800  |    38,200    |   3,600  |    +9.4%
-- 2025-03  | 39,100  |    41,800    |  -2,700  |    -6.5%
-- 2025-04  | 48,200  |    39,100    |   9,100  |   +23.3%

3. SUM() OVER() — Running Totals

Running totals — the cumulative sum up to and including the current row — are one of the most requested finance reports. With a window function, it's a single line of DAX.

PostgreSQL — YTD Running Total

SELECT
    month,
    revenue,
    SUM(revenue) OVER (
        ORDER BY month
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS ytd_revenue,

    SUM(expenses) OVER (
        ORDER BY month
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS ytd_expenses,

    AVG(revenue) OVER (
        ORDER BY month
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS rolling_3m_avg

FROM monthly_financials
WHERE EXTRACT(YEAR FROM month) = 2025
ORDER BY month;

💡

ROWS BETWEEN explained

UNBOUNDED PRECEDING means "from the very first row". CURRENT ROW means "up to and including this row". 2 PRECEDING means "the 2 rows before this one". So ROWS BETWEEN 2 PRECEDING AND CURRENT ROW gives you a 3-month rolling window.

4. NTILE — Customer Segmentation into Quartiles

NTILE(n) divides rows into n roughly equal buckets. This is the foundation of RFM (Recency, Frequency, Monetary) segmentation — the industry-standard customer segmentation model.

PostgreSQL — LTV Quartile Segmentation

WITH customer_ltv AS (
    SELECT
        customer_id,
        SUM(order_value) AS lifetime_value,
        COUNT(order_id)  AS total_orders,
        MAX(order_date)  AS last_order_date
    FROM orders
    GROUP BY customer_id
)
SELECT
    customer_id,
    lifetime_value,
    total_orders,
    NTILE(4) OVER (ORDER BY lifetime_value DESC) AS ltv_quartile,
    CASE
        WHEN NTILE(4) OVER (ORDER BY lifetime_value DESC) = 1
            THEN 'Champions'
        WHEN NTILE(4) OVER (ORDER BY lifetime_value DESC) = 2
            THEN 'Loyal'
        WHEN NTILE(4) OVER (ORDER BY lifetime_value DESC) = 3
            THEN 'At Risk'
        ELSE 'Lost'
    END AS segment
FROM customer_ltv
ORDER BY lifetime_value DESC;

5. RANK vs DENSE_RANK — When Ties Matter

ROW_NUMBER gives every row a unique number even if values are identical. RANK skips numbers after a tie. DENSE_RANK never skips numbers. For finance reports, the right choice depends on what you're ranking:

Use ROW_NUMBER when you need exactly N rows — e.g. top 10 products. Ties are broken arbitrarily.
Use RANK for competition-style ranking — if two products tie for 3rd, both get rank 3, the next gets rank 5.
Use DENSE_RANK when you want no gaps — if two products tie for 3rd, the next gets rank 4, not 5.

⚠️

Performance note

Window functions can be expensive on large tables. Always ensure the columns in your PARTITION BY and ORDER BY clauses are indexed. On tables over 1M rows, test your query execution plan with EXPLAIN ANALYZE before running in production.

Putting It All Together — A Complete Finance Report Query

PostgreSQL — Full Monthly Finance Dashboard Query

SELECT
    month,
    revenue,
    expenses,
    revenue - expenses                          AS net_profit,

    -- Running totals
    SUM(revenue)  OVER (ORDER BY month)        AS ytd_revenue,
    SUM(expenses) OVER (ORDER BY month)        AS ytd_expenses,

    -- MOM variance
    revenue - LAG(revenue) OVER (ORDER BY month) AS mom_variance,

    -- Rolling 3-month average
    AVG(revenue) OVER (
        ORDER BY month
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    )                                           AS rolling_3m_avg,

    -- Month rank by revenue
    RANK() OVER (ORDER BY revenue DESC)       AS revenue_rank

FROM monthly_financials
WHERE EXTRACT(YEAR FROM month) = 2025
ORDER BY month;

SQL Window Functions Every Finance Analyst Should Know — With Real Financial Examples

Why Finance Analysts Need Window Functions

The Syntax — Understanding OVER()

1. ROW_NUMBER — Ranking Your Best Customers

2. LAG and LEAD — Period-Over-Period Comparisons

3. SUM() OVER() — Running Totals

4. NTILE — Customer Segmentation into Quartiles

5. RANK vs DENSE_RANK — When Ties Matter

Putting It All Together — A Complete Finance Report Query

Need SQL Analysis for Your Business?

SQL Window Functions Every Finance Analyst Should Know — With Real Financial Examples

Why Finance Analysts Need Window Functions

The Syntax — Understanding OVER()

1. ROW_NUMBER — Ranking Your Best Customers

2. LAG and LEAD — Period-Over-Period Comparisons

3. SUM() OVER() — Running Totals

4. NTILE — Customer Segmentation into Quartiles

5. RANK vs DENSE_RANK — When Ties Matter

Putting It All Together — A Complete Finance Report Query

Need SQL Analysis for Your Business?

More Articles