Advanced SQL Window Functions for Time-Series Analysis in PostgreSQL 17

Window functions in PostgreSQL are powerful tools for analyzing time-series data, enabling complex calculations across rows without the need for self-joins or complex subqueries. This guide explores advanced applications of window functions specifically for temporal analysis, focusing on revenue trends and customer retention patterns.

PostgreSQL 17 introduces performance optimizations for window functions, making them even more suitable for large-scale time-series analysis. We’ll demonstrate practical examples using real-world scenarios that data analysts and engineers commonly encounter.

Revenue Analysis with Window Functions

WITH MonthlySales AS (
  SELECT 
    date_trunc('month', order_date) AS sales_month,
    SUM(total_amount) AS monthly_revenue
  FROM orders
  GROUP BY 1
)
SELECT
  sales_month,
  monthly_revenue,
  RANK() OVER (ORDER BY monthly_revenue DESC) AS revenue_rank,
  LAG(monthly_revenue, 1) OVER (ORDER BY sales_month) AS prev_month_revenue,
  monthly_revenue - LAG(monthly_revenue, 1) OVER (ORDER BY sales_month) 
     AS revenue_growth,
  SUM(monthly_revenue) OVER (ORDER BY sales_month
     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM MonthlySales
ORDER BY sales_month;

Key features demonstrated:

• RANK(): Identifies top-performing months
• LAG(): Compares current vs previous month revenue
• Running total: Cumulative sales over time
• Month-over-month growth calculation

Sample output:

Customer Retention Analysis

WITH CustomerActivity AS (
  SELECT
    customer_id,
    date_trunc('month', order_date) AS activity_month,
    LAG(date_trunc('month', order_date)) OVER (
      PARTITION BY customer_id 
      ORDER BY order_date
    ) AS last_purchase_month
  FROM orders
)
SELECT
  activity_month,
  COUNT(DISTINCT customer_id) AS active_customers,
  COUNT(DISTINCT CASE
     WHEN activity_month - last_purchase_month = INTERVAL '1 month'
     THEN customer_id
   END) AS retained_customers,
  ROUND(
    COUNT(DISTINCT CASE
       WHEN activity_month - last_purchase_month = INTERVAL '1 month'
       THEN customer_id
     END) * 100.0 /
     COUNT(DISTINCT customer_id)
  ,2) AS retention_rate
FROM CustomerActivity
GROUP BY 1
ORDER BY 1;

Key insights:

• Tracks month-over-month customer retention
• Calculates retention rate percentage
• Identifies active vs retained customer cohorts

Visualization Recommendations

• Revenue Trends:
  • Line chart: Monthly revenue + running total
  • Bar chart: Month-over-month growth (green/red for positive/negative)
• Customer Retention:
  • Area chart: Active vs retained customers
  • Heatmap: Retention rate percentage over time

Implementation tip: Use BI tools like Metabase or Tableau to connect directly to PostgreSQL and visualize these query results. For temporal patterns, ensure date axes are properly formatted and consider 3-month moving averages to smooth short-term fluctuations.

Common Pitfalls to Avoid

• Always include ORDER BY in window functions for deterministic results
• Handle NULLs from LAG() using COALESCE(prev_month_revenue, 0)
• Use PARTITION BY judiciously to avoid unintended grouping

© 2025 MinervaDB Inc. All rights reserved. PostgreSQL is a registered trademark of the PostgreSQL Community Association of Canada. All SQL examples and code snippets are provided for educational purposes only. Other trademarks and product names mentioned are the property of their respective owners. This document and its contents may not be reproduced, distributed, or transmitted in any form or by any means without the prior written permission of MinervaDB Inc.

The information provided in this document is for general informational purposes only and should not be construed as professional advice. While we strive to keep the information up to date and correct, we make no representations or warranties of any kind, express or implied, about the completeness, accuracy, reliability, suitability, or availability of the information contained in this document.

Mastering Time-Series Analysis in PostgreSQL with the DATE_BUCKET Function

Improving Performance: PostgreSQL Query Optimization with Indexes

Looking under the hood of query transformation (done by CBO) in PostgreSQL with a simple real-life example