Rizqi Mulki

Backend Developer

Advanced Analytics in PostgreSQL: Window Functions for Complex Reports

—

by

Rizqi Mulki
in ,

In the world of data analysis and reporting, the ability to perform complex calculations across rows of data is invaluable. PostgreSQL’s window functions provide precisely this capability, enabling analysts and developers to create sophisticated reports without resorting to multiple queries or application-level processing. If you’re looking to take your SQL reporting capabilities to the next level, mastering window functions is an essential skill.

What Are Window Functions?

Window functions perform calculations across a set of table rows that are somehow related to the current row. Unlike regular aggregate functions that would collapse multiple rows into a single result row, window functions retain all rows in the result. This allows you to add calculated fields that incorporate values from other rows while still seeing all the original detail data.

The basic syntax looks like this:

function_name(expression) OVER (
   [PARTITION BY partition_expression, ... ]
   [ORDER BY sort_expression [ASC | DESC], ... ]
   [frame_clause]
)

Each component serves a specific purpose:

  • function_name(expression): The function to apply (e.g., SUM, AVG, RANK)
  • PARTITION BY: Divides the result set into partitions (like GROUP BY, but without collapsing rows)
  • ORDER BY: Defines the logical order of rows within each partition
  • frame_clause: Specifies which rows to include in the window frame relative to the current row

Practical Use Cases for Financial and Business Reporting

Let’s explore some common reporting scenarios where window functions shine.

1. Running Totals and Moving Averages

Imagine you need to track cumulative sales throughout the year or calculate a 7-day moving average for transaction volumes.

-- Running totals of daily sales
SELECT 
    transaction_date,
    daily_sales,
    SUM(daily_sales) OVER (ORDER BY transaction_date) AS running_total
FROM daily_sales_summary
WHERE transaction_date >= '2025-01-01' AND transaction_date <= '2025-03-31'
ORDER BY transaction_date;

-- 7-day moving average of transaction volumes
SELECT 
    transaction_date,
    transaction_count,
    AVG(transaction_count) OVER (
        ORDER BY transaction_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7day
FROM daily_transaction_counts
ORDER BY transaction_date;

2. Ranking and Percentiles

Identifying top performers or classifying items by percentile groups:

-- Ranking customers by total purchase amount
SELECT 
    customer_id,
    customer_name,
    total_purchases,
    RANK() OVER (ORDER BY total_purchases DESC) AS purchase_rank,
    DENSE_RANK() OVER (ORDER BY total_purchases DESC) AS dense_purchase_rank,
    ROW_NUMBER() OVER (ORDER BY total_purchases DESC) AS purchase_row_number
FROM customer_purchase_summary
LIMIT 20;

-- Creating quartiles for products by profit margin
SELECT 
    product_id,
    product_name,
    category,
    profit_margin,
    NTILE(4) OVER (ORDER BY profit_margin DESC) AS profit_quartile
FROM product_profitability;

The difference between RANK(), DENSE_RANK(), and ROW_NUMBER() is important:

  • RANK() leaves gaps in the sequence when there are ties
  • DENSE_RANK() doesn’t leave gaps for ties
  • ROW_NUMBER() assigns a unique sequential integer to each row (no ties)

3. Year-over-Year and Period-over-Period Comparisons

Comparing current performance to previous periods is a common business reporting requirement:

-- Year-over-year sales comparison by quarter
SELECT 
    extract(year from transaction_date) AS year,
    extract(quarter from transaction_date) AS quarter,
    SUM(sale_amount) AS quarterly_sales,
    LAG(SUM(sale_amount), 4) OVER (ORDER BY 
        extract(year from transaction_date),
        extract(quarter from transaction_date)
    ) AS sales_same_quarter_last_year,
    SUM(sale_amount) - LAG(SUM(sale_amount), 4) OVER (ORDER BY 
        extract(year from transaction_date),
        extract(quarter from transaction_date)
    ) AS yoy_change,
    ROUND(
        (SUM(sale_amount) - LAG(SUM(sale_amount), 4) OVER (ORDER BY 
            extract(year from transaction_date),
            extract(quarter from transaction_date)
        )) * 100.0 / NULLIF(LAG(SUM(sale_amount), 4) OVER (ORDER BY 
            extract(year from transaction_date),
            extract(quarter from transaction_date)
        ), 0), 2
    ) AS yoy_percent_change
FROM sales
GROUP BY 1, 2
ORDER BY 1, 2;

4. Market Share and Contribution Analysis

Understanding relative performance within categories:

-- Market share by product within each category
SELECT 
    category,
    product_name,
    annual_sales,
    annual_sales / SUM(annual_sales) OVER (PARTITION BY category) * 100 AS category_market_share,
    annual_sales / SUM(annual_sales) OVER () * 100 AS overall_market_share
FROM product_sales
ORDER BY category, annual_sales DESC;

-- Identifying over/under-performing branches compared to regional averages
SELECT 
    region,
    branch_id,
    branch_name,
    monthly_revenue,
    AVG(monthly_revenue) OVER (PARTITION BY region) AS region_avg_revenue,
    monthly_revenue - AVG(monthly_revenue) OVER (PARTITION BY region) AS deviation_from_region_avg,
    ROUND(
        (monthly_revenue - AVG(monthly_revenue) OVER (PARTITION BY region)) * 100.0 / 
        AVG(monthly_revenue) OVER (PARTITION BY region), 2
    ) AS percent_deviation
FROM branch_performance
ORDER BY region, percent_deviation DESC;

Advanced Window Function Techniques

Now let’s look at some more sophisticated applications that can transform your reporting capabilities.

1. Complex Frames and Boundaries

Window function frames can be defined using various boundary types:

-- Compare current month sales to average of previous 3 months
SELECT 
    sales_month,
    monthly_sales,
    AVG(monthly_sales) OVER (
        ORDER BY sales_month
        ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING
    ) AS prev_3month_avg,
    monthly_sales - AVG(monthly_sales) OVER (
        ORDER BY sales_month
        ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING
    ) AS deviation_from_prev_avg
FROM monthly_sales_data
ORDER BY sales_month;

-- Range-based frames (logical ranges rather than physical rows)
SELECT 
    transaction_date,
    daily_revenue,
    AVG(daily_revenue) OVER (
        ORDER BY transaction_date
        RANGE BETWEEN INTERVAL '30 days' PRECEDING AND CURRENT ROW
    ) AS rolling_30day_avg
FROM daily_revenue
ORDER BY transaction_date;

The RANGE option is particularly useful for time series data with irregular intervals, as it operates on the logical range of values rather than a fixed number of rows.

2. Multiple Window Functions in a Single Query

You can combine several window functions with different partitioning and ordering:

-- Comprehensive sales analysis
SELECT 
    region,
    product_category,
    sales_month,
    monthly_sales,
    -- Regional analysis
    SUM(monthly_sales) OVER (PARTITION BY region, sales_month) AS region_monthly_total,
    monthly_sales / SUM(monthly_sales) OVER (PARTITION BY region, sales_month) * 100 AS region_monthly_contribution,
    
    -- Product category analysis
    SUM(monthly_sales) OVER (PARTITION BY product_category, sales_month) AS category_monthly_total,
    monthly_sales / SUM(monthly_sales) OVER (PARTITION BY product_category, sales_month) * 100 AS category_monthly_contribution,
    
    -- Time series analysis
    LAG(monthly_sales) OVER (PARTITION BY region, product_category ORDER BY sales_month) AS prev_month_sales,
    monthly_sales - LAG(monthly_sales) OVER (PARTITION BY region, product_category ORDER BY sales_month) AS mom_change,
    
    -- Ranking
    DENSE_RANK() OVER (PARTITION BY sales_month ORDER BY monthly_sales DESC) AS overall_rank_this_month
FROM sales_summary
ORDER BY sales_month, region, product_category;

3. Named Windows for Better Readability

When using multiple window functions with the same window definition, you can use the WINDOW clause to avoid repetition:

SELECT 
    department,
    employee_name,
    salary,
    AVG(salary) OVER dept_window AS dept_avg_salary,
    MAX(salary) OVER dept_window AS dept_max_salary,
    MIN(salary) OVER dept_window AS dept_min_salary,
    salary - AVG(salary) OVER dept_window AS salary_deviation,
    RANK() OVER (WINDOW dept_window ORDER BY salary DESC) AS dept_salary_rank
FROM employee_compensation
WINDOW dept_window AS (PARTITION BY department)
ORDER BY department, salary DESC;

4. First Value, Last Value, and Nth Value

These functions let you access specific rows relative to the current row:

-- Find the first and last day of each month in the dataset
SELECT DISTINCT
    DATE_TRUNC('month', transaction_date) AS month,
    FIRST_VALUE(transaction_date) OVER (
        PARTITION BY DATE_TRUNC('month', transaction_date) 
        ORDER BY transaction_date
        RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS first_day_with_transactions,
    LAST_VALUE(transaction_date) OVER (
        PARTITION BY DATE_TRUNC('month', transaction_date) 
        ORDER BY transaction_date
        RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_day_with_transactions
FROM transactions
ORDER BY month;

-- Get the second highest sale in each region
SELECT
    region,
    store_id,
    store_name,
    annual_sales,
    NTH_VALUE(store_name, 2) OVER (
        PARTITION BY region
        ORDER BY annual_sales DESC
        RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS second_best_store,
    NTH_VALUE(annual_sales, 2) OVER (
        PARTITION BY region
        ORDER BY annual_sales DESC
        RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS second_best_sales
FROM store_performance
ORDER BY region, annual_sales DESC;

Note the RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING clause, which ensures the functions consider all rows in the partition.

Practical Examples for Financial Analysis

Let’s explore some window function applications that are particularly relevant for financial analysis.

1. Compound Annual Growth Rate (CAGR) Calculation

WITH yearly_revenue AS (
    SELECT 
        EXTRACT(YEAR FROM transaction_date) AS year,
        SUM(amount) AS annual_revenue
    FROM transactions
    GROUP BY 1
    ORDER BY 1
)
SELECT 
    year,
    annual_revenue,
    FIRST_VALUE(annual_revenue) OVER (ORDER BY year) AS baseline_revenue,
    FIRST_VALUE(year) OVER (ORDER BY year) AS baseline_year,
    POWER(
        (annual_revenue / FIRST_VALUE(annual_revenue) OVER (ORDER BY year)),
        1.0 / (year - FIRST_VALUE(year) OVER (ORDER BY year))
    ) - 1 AS cagr
FROM yearly_revenue;

2. Volatility Analysis

-- Calculate daily returns and rolling standard deviation (volatility)
WITH daily_prices AS (
    SELECT 
        trading_date,
        stock_symbol,
        closing_price,
        LAG(closing_price) OVER (PARTITION BY stock_symbol ORDER BY trading_date) AS prev_closing_price,
        (closing_price - LAG(closing_price) OVER (PARTITION BY stock_symbol ORDER BY trading_date)) / 
            NULLIF(LAG(closing_price) OVER (PARTITION BY stock_symbol ORDER BY trading_date), 0) AS daily_return
    FROM stock_prices
)
SELECT 
    trading_date,
    stock_symbol,
    closing_price,
    daily_return,
    STDDEV(daily_return) OVER (
        PARTITION BY stock_symbol 
        ORDER BY trading_date 
        ROWS BETWEEN 20 PRECEDING AND CURRENT ROW
    ) * SQRT(252) AS annualized_volatility_21d
FROM daily_prices
WHERE daily_return IS NOT NULL
ORDER BY stock_symbol, trading_date;

3. Identifying Double-Entry Accounting Imbalances

-- Find transactions where debits don't equal credits
WITH transaction_sums AS (
    SELECT 
        transaction_id,
        entry_date,
        SUM(CASE WHEN entry_type = 'DEBIT' THEN amount ELSE 0 END) AS total_debits,
        SUM(CASE WHEN entry_type = 'CREDIT' THEN amount ELSE 0 END) AS total_credits
    FROM accounting_entries
    GROUP BY transaction_id, entry_date
)
SELECT 
    transaction_id,
    entry_date,
    total_debits,
    total_credits,
    total_debits - total_credits AS imbalance,
    SUM(ABS(total_debits - total_credits)) OVER (
        PARTITION BY DATE_TRUNC('month', entry_date)
    ) AS monthly_total_imbalance,
    COUNT(*) FILTER (WHERE total_debits <> total_credits) OVER (
        PARTITION BY DATE_TRUNC('month', entry_date)
    ) AS monthly_imbalanced_transactions
FROM transaction_sums
WHERE total_debits <> total_credits
ORDER BY entry_date, transaction_id;

Performance Considerations

Window functions are powerful but can be resource-intensive. Here are some tips for optimizing performance:

  1. Limit Partition Sizes: Be careful with PARTITION BY on high-cardinality columns in large tables.
  2. Index Strategy: Ensure you have appropriate indexes on columns used in PARTITION BY and ORDER BY clauses.
  3. Frame Specification: Be specific about your window frame to avoid unnecessarily large computations: -- Less efficient (processes all rows in partition) SUM(amount) OVER (PARTITION BY customer_id) -- More efficient for large datasets (processes only relevant rows) SUM(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
  4. Pre-aggregation: For very large datasets, consider pre-aggregating data before applying window functions: WITH pre_aggregated AS ( SELECT date_trunc('day', transaction_timestamp) AS transaction_date, product_id, SUM(quantity) AS daily_quantity, SUM(amount) AS daily_amount FROM transactions GROUP BY 1, 2 ) SELECT transaction_date, product_id, daily_amount, SUM(daily_amount) OVER (PARTITION BY product_id ORDER BY transaction_date) AS running_total FROM pre_aggregated ORDER BY product_id, transaction_date;

Common Pitfalls and How to Avoid Them

  1. NULL Handling: Window functions can produce unexpected results with NULL values: -- Add appropriate COALESCE or handling of NULL values COALESCE( LAG(sales_amount) OVER (PARTITION BY region ORDER BY sales_month), 0 ) AS previous_month_sales
  2. Framing Issues: Default frames can lead to unexpected results: -- Without explicit frame, default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW -- This can cause confusion when doing calculations like running averages -- Be explicit about your frame: AVG(value) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
  3. Row Ordering: When ORDER BY isn’t specified in the OVER clause, all rows are considered peers: -- This might not give the expected result for running totals SUM(amount) OVER (PARTITION BY customer_id) -- No ordering! -- Make sure to include ORDER BY for running calculations SUM(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date)

Conclusion

PostgreSQL’s window functions provide a powerful toolset for performing advanced analytics directly in your database. They enable complex calculations across rows while maintaining the granularity of your data, making them perfect for sophisticated financial and business reporting.

By mastering window functions, you can create more efficient queries that perform calculations in a single pass through the data rather than requiring multiple queries or application-level processing. Whether you’re calculating running totals, performing time-series analysis, or constructing complex comparative metrics, window functions offer elegant solutions to challenging analytical problems.

As data volumes continue to grow and business intelligence requirements become more sophisticated, the ability to leverage PostgreSQL’s analytical capabilities becomes increasingly valuable. By incorporating these techniques into your reporting toolkit, you can deliver deeper insights while maintaining database performance and scalability.

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

CAPTCHA ImageChange Image