PostgreSQL Window Functions: ROW_NUMBER, RANK, LAG/LEAD, Running Totals, and Percentile

Window functions compute a value across a set of rows related to the current row — without collapsing them into a GROUP BY aggregate. They're the SQL power tool for analytics: rankings, period-over-period comparisons, running totals, moving averages, and percentile buckets. Problems that require a subquery or multiple CTEs often collapse to a single window function expression.

This post covers the window functions you'll actually use in production analytics: ROW_NUMBER, RANK/DENSE_RANK, LAG/LEAD, SUM/AVG OVER for running calculations, NTILE for percentile buckets, and frame clauses for moving windows.

Window Function Anatomy

function_name(args) OVER (
  PARTITION BY column        -- Reset for each partition (like GROUP BY but keeps rows)
  ORDER BY column            -- Determines row order within partition
  ROWS/RANGE BETWEEN ...     -- Frame: which rows to include in calculation
)

---

1. ROW_NUMBER, RANK, DENSE_RANK

-- Sample: orders per customer
SELECT
  customer_id,
  order_id,
  amount_cents,
  created_at,

  -- Unique sequential number per customer (no ties)
  ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS order_num,

  -- Rank with gaps on ties (two orders same amount → both rank 1, next is rank 3)
  RANK() OVER (PARTITION BY customer_id ORDER BY amount_cents DESC) AS amount_rank,

  -- Rank without gaps (two ties → both rank 1, next is rank 2)
  DENSE_RANK() OVER (PARTITION BY customer_id ORDER BY amount_cents DESC) AS dense_rank

FROM orders
WHERE created_at >= NOW() - INTERVAL '90 days';

-- Common use case: get FIRST or LAST N rows per group
-- (replaces LATERAL JOIN for simple cases)
SELECT customer_id, order_id, amount_cents, created_at
FROM (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
  FROM orders
) ranked
WHERE rn = 1;  -- Most recent order per customer

-- Top 3 products by revenue per category
SELECT category, product_id, name, revenue
FROM (
  SELECT
    p.category,
    p.id AS product_id,
    p.name,
    SUM(oi.quantity * oi.price_cents) / 100.0 AS revenue,
    RANK() OVER (
      PARTITION BY p.category
      ORDER BY SUM(oi.quantity * oi.price_cents) DESC
    ) AS rnk
  FROM products p
  JOIN order_items oi ON p.id = oi.product_id
  JOIN orders o ON oi.order_id = o.id
  WHERE o.status = 'completed'
    AND o.created_at >= NOW() - INTERVAL '30 days'
  GROUP BY p.category, p.id, p.name
) ranked
WHERE rnk <= 3
ORDER BY category, rnk;

---

2. LAG and LEAD: Period-over-Period Comparisons

-- Month-over-month revenue with growth calculation
WITH monthly_revenue AS (
  SELECT
    DATE_TRUNC('month', created_at) AS month,
    SUM(amount_cents) / 100.0 AS revenue
  FROM orders
  WHERE status = 'completed'
    AND created_at >= NOW() - INTERVAL '13 months'
  GROUP BY 1
)
SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
  ROUND(
    100.0 * (revenue - LAG(revenue) OVER (ORDER BY month)) /
    NULLIF(LAG(revenue) OVER (ORDER BY month), 0),
    1
  ) AS mom_growth_pct,
  LEAD(revenue) OVER (ORDER BY month) AS next_month_revenue  -- (for forecasting context)
FROM monthly_revenue
ORDER BY month DESC;

-- LAG with offset: compare to same month last year (YoY)
WITH monthly_revenue AS (
  SELECT
    DATE_TRUNC('month', created_at) AS month,
    SUM(amount_cents) / 100.0 AS revenue
  FROM orders
  WHERE status = 'completed'
  GROUP BY 1
)
SELECT
  month,
  revenue,
  LAG(revenue, 12) OVER (ORDER BY month) AS same_month_last_year,
  ROUND(
    100.0 * (revenue - LAG(revenue, 12) OVER (ORDER BY month)) /
    NULLIF(LAG(revenue, 12) OVER (ORDER BY month), 0), 1
  ) AS yoy_growth_pct
FROM monthly_revenue
WHERE month >= NOW() - INTERVAL '2 years'
ORDER BY month DESC;

-- Find consecutive day streaks
WITH daily_activity AS (
  SELECT DISTINCT
    user_id,
    DATE(created_at) AS activity_date
  FROM user_events
),
with_gaps AS (
  SELECT
    user_id,
    activity_date,
    activity_date - LAG(activity_date) OVER (PARTITION BY user_id ORDER BY activity_date) AS gap_days
  FROM daily_activity
),
streaks AS (
  SELECT
    user_id,
    activity_date,
    SUM(CASE WHEN gap_days IS NULL OR gap_days > 1 THEN 1 ELSE 0 END)
      OVER (PARTITION BY user_id ORDER BY activity_date) AS streak_id
  FROM with_gaps
)
SELECT
  user_id,
  streak_id,
  MIN(activity_date) AS streak_start,
  MAX(activity_date) AS streak_end,
  COUNT(*) AS streak_length_days
FROM streaks
GROUP BY user_id, streak_id
ORDER BY streak_length_days DESC;

---

3. Running Totals and Cumulative Aggregates

-- Running total revenue (cumulative sum)
SELECT
  DATE_TRUNC('day', created_at) AS day,
  SUM(amount_cents) / 100.0 AS daily_revenue,
  SUM(SUM(amount_cents)) OVER (ORDER BY DATE_TRUNC('day', created_at)) / 100.0 AS running_total
FROM orders
WHERE status = 'completed'
  AND created_at >= DATE_TRUNC('month', NOW())
GROUP BY 1
ORDER BY 1;

-- 7-day moving average
SELECT
  DATE_TRUNC('day', created_at) AS day,
  SUM(amount_cents) / 100.0 AS daily_revenue,
  AVG(SUM(amount_cents)) OVER (
    ORDER BY DATE_TRUNC('day', created_at)
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW  -- 7-day window
  ) / 100.0 AS moving_avg_7d
FROM orders
WHERE status = 'completed'
  AND created_at >= NOW() - INTERVAL '60 days'
GROUP BY 1
ORDER BY 1;

-- Cumulative percentage of total
SELECT
  product_name,
  revenue,
  SUM(revenue) OVER (ORDER BY revenue DESC) AS cumulative_revenue,
  ROUND(
    100.0 * SUM(revenue) OVER (ORDER BY revenue DESC) /
    SUM(revenue) OVER (),  -- Total (no ORDER BY = entire partition)
    1
  ) AS cumulative_pct,
  -- Pareto analysis: which products make up 80% of revenue?
  CASE
    WHEN SUM(revenue) OVER (ORDER BY revenue DESC) /
         SUM(revenue) OVER () <= 0.8 THEN 'Top 80%'
    ELSE 'Bottom 20%'
  END AS pareto_group
FROM (
  SELECT
    p.name AS product_name,
    SUM(oi.quantity * oi.price_cents) / 100.0 AS revenue
  FROM products p
  JOIN order_items oi ON p.id = oi.product_id
  GROUP BY p.name
) revenue_by_product
ORDER BY revenue DESC;

---

4. NTILE and Percentile Buckets

-- Segment customers into 4 value quartiles
SELECT
  customer_id,
  total_spent,
  NTILE(4) OVER (ORDER BY total_spent DESC) AS quartile,
  CASE NTILE(4) OVER (ORDER BY total_spent DESC)
    WHEN 1 THEN 'Platinum'
    WHEN 2 THEN 'Gold'
    WHEN 3 THEN 'Silver'
    WHEN 4 THEN 'Bronze'
  END AS segment
FROM (
  SELECT
    customer_id,
    SUM(amount_cents) / 100.0 AS total_spent
  FROM orders
  WHERE status = 'completed'
    AND created_at >= NOW() - INTERVAL '365 days'
  GROUP BY customer_id
) customer_value;

-- PERCENTILE_CONT: find the value at a given percentile (continuous)
SELECT
  PERCENTILE_CONT(0.5)  WITHIN GROUP (ORDER BY response_time_ms) AS p50_ms,
  PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY response_time_ms) AS p75_ms,
  PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY response_time_ms) AS p95_ms,
  PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY response_time_ms) AS p99_ms,
  MAX(response_time_ms) AS max_ms
FROM api_request_log
WHERE created_at >= NOW() - INTERVAL '24 hours'
  AND path = '/api/orders';

-- PERCENT_RANK and CUME_DIST: where does a customer rank?
SELECT
  customer_id,
  total_spent,
  ROUND(100 * PERCENT_RANK() OVER (ORDER BY total_spent), 1) AS percentile_rank,
  ROUND(100 * CUME_DIST()   OVER (ORDER BY total_spent), 1) AS cumulative_pct
FROM customer_value_cte
ORDER BY total_spent DESC
LIMIT 20;

---

5. Frame Clauses

-- ROWS vs RANGE:
-- ROWS: physical rows (use for running totals, moving averages)
-- RANGE: value-based (use when ties should be treated the same)

-- ROWS BETWEEN: explicit physical row range
SUM(amount) OVER (
  ORDER BY date
  ROWS BETWEEN 29 PRECEDING AND CURRENT ROW  -- 30-day rolling window
)

-- UNBOUNDED PRECEDING to CURRENT ROW (default when ORDER BY specified):
SUM(amount) OVER (ORDER BY date)  -- Running total from first row

-- UNBOUNDED PRECEDING to UNBOUNDED FOLLOWING (entire partition):
AVG(amount) OVER (PARTITION BY category)  -- No ORDER BY = entire partition

-- CURRENT ROW to UNBOUNDED FOLLOWING (remaining rows):
SUM(amount) OVER (
  ORDER BY date
  ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
) AS remaining_total

-- EXCLUDE CURRENT ROW (exclude self from window):
AVG(rating) OVER (
  PARTITION BY product_id
  ORDER BY created_at
  ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
  EXCLUDE CURRENT ROW  -- PostgreSQL 14+
) AS avg_rating_excluding_self

---

6. Performance Optimization

-- Window functions need to process lots of rows — optimize inputs
-- Bad: window function on entire unfiltered table
SELECT customer_id, order_id, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at)
FROM orders;  -- Full table scan

-- Good: pre-filter with CTE, then window
WITH recent_orders AS (
  SELECT customer_id, order_id, created_at, amount_cents
  FROM orders
  WHERE created_at >= NOW() - INTERVAL '90 days'
    AND tenant_id = $1  -- Index-driven filter first
)
SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
FROM recent_orders;

-- Index on PARTITION BY + ORDER BY columns speeds up window functions
CREATE INDEX idx_orders_customer_date ON orders (customer_id, created_at DESC)
  WHERE status = 'completed';

-- EXPLAIN ANALYZE: check for WindowAgg nodes and their cost
EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at)
FROM orders WHERE created_at >= NOW() - INTERVAL '30 days';
-- Look for: Sort + WindowAgg — sort is usually the expensive part

---

Cost Reference (Query Performance)

---

See Also

• PostgreSQL Full-Text Search: tsvector, tsquery, and Ranking
• PostgreSQL JSONB Patterns: Operators, Indexing, and Schema-on-Read
• PostgreSQL Row-Level Security: Multi-Tenant Isolation and Audit Policies
• PostgreSQL Partitioning: Range, List, and Hash Strategies
• SaaS MRR Tracking: Subscription Revenue Metrics and Analytics

---

Working With Viprasol

Analytics queries taking 30 seconds because you're using correlated subqueries where a window function would take 100ms? We refactor your analytics layer to use window functions correctly — with proper PARTITION BY indexes, CTE pre-filtering, and frame clause selection — turning slow reports into sub-second dashboards.

Talk to our team → | Explore our cloud solutions →