PostgreSQL Advanced: Window Functions and CTEs Explained

Table of Contents

• Introduction
• Why Basic SQL Is Not Always Enough
• What Are Window Functions?
• Common Window Function Examples
• What Are CTEs?
• Using CTEs to Make Queries Readable
• Combining CTEs and Window Functions
• Performance Considerations
• Common Mistakes
• Checklist
• FAQ
• Conclusion

Introduction

PostgreSQL is often used through familiar SQL patterns: SELECT, WHERE, JOIN, GROUP BY, and ORDER BY. Those tools solve many everyday problems, but some reporting and analytics questions need more expressive queries.

For example:

• Which customer placed the first order in each region?
• What is the running revenue total by day?
• How does each product rank within its category?
• Which events happened immediately before a failure?
• How can a complex query be split into readable steps?

Window functions and common table expressions, often called CTEs, are two PostgreSQL features that help answer these questions without turning SQL into a maze.

With window functions, you can calculate values from related rows without losing the detail of each row. CTEs help break complex queries into clearer named steps. Used together, they make advanced SQL easier to write, read, and maintain.

Why Basic SQL Is Not Always Enough

GROUP BY is useful when you want one result per group. For example, total sales per customer:

SELECT customer_id, SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id;

But sometimes you need both the grouped insight and the original row detail. Suppose you want every order, plus the customer’s running total over time. A plain GROUP BY collapses the rows, so it is not enough.

That is where window functions help. They calculate values from a set of related rows without collapsing those rows into a single result.

CTEs solve a different problem: query readability. As business logic grows, nested subqueries can become difficult to understand. A CTE lets you name intermediate results so the query reads more like a sequence of clear steps.

What Are Window Functions?

A window function calculates a value by looking at the current row together with other related rows. For example, rows for the same customer, the same category, or the same time sequence.

The basic shape looks like this:

function_name() OVER (
  PARTITION BY group_column
  ORDER BY sort_column
)

There are three important parts:

• OVER tells PostgreSQL that this calculation uses a window function.
• PARTITION BY divides rows into groups, similar to thinking “per customer” or “per category”.
• ORDER BY defines the order inside each group, such as oldest order to newest order.

Example:

SELECT
  customer_id,
  order_id,
  order_date,
  total_amount,
  SUM(total_amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
  ) AS running_total
FROM orders;

This still returns every order row, then adds a running_total column for each customer. You keep the order detail while also seeing the accumulated value.

Example output:

customer_id	order_id	order_date	total_amount	running_total
C01	O101	2026-01-01	100	100
C01	O102	2026-01-05	75	175
C02	O201	2026-01-03	60	60
C02	O202	2026-01-08	140	200

Common Window Function Examples

Ranking rows

Use ROW_NUMBER, RANK, or DENSE_RANK when you need to assign positions based on a specific order. For example, products with the highest revenue inside each category.

SELECT
  product_id,
  category_id,
  revenue,
  RANK() OVER (
    PARTITION BY category_id
    ORDER BY revenue DESC
  ) AS revenue_rank
FROM product_revenue;

This ranks products within each category from highest revenue to lowest revenue.

Example output:

product_id	category_id	revenue	revenue_rank
P01	C10	500	1
P02	C10	500	1
P03	C10	300	3
P04	C20	900	1

The difference between these ranking functions matters when values are tied:

• ROW_NUMBER always gives a unique number, even when values are the same.
• RANK gives the same rank for tied values, then skips the next rank.
• DENSE_RANK gives the same rank for tied values without leaving a gap.

Comparing with previous rows

Use LAG to read a value from the previous row and LEAD to read a value from the next row. This is useful when the order of events matters, not just each row by itself.

SELECT
  user_id,
  event_time,
  event_type,
  LAG(event_type) OVER (
    PARTITION BY user_id
    ORDER BY event_time
  ) AS previous_event
FROM user_events;

This shows the current event together with the previous event for the same user. Patterns like this are useful for funnels, audit trails, and debugging user flows.

Example output:

user_id	event_time	event_type	previous_event
U01	10:00	view_product	null
U01	10:03	add_to_cart	view_product
U01	10:05	checkout	add_to_cart

Running totals

Running totals are one of the most practical uses of window functions. You can see the value of each row and the accumulated total up to that row.

SELECT
  order_date,
  total_amount,
  SUM(total_amount) OVER (
    ORDER BY order_date
  ) AS running_revenue
FROM daily_orders;

Example output:

order_date	total_amount	running_revenue
2026-01-01	100	100
2026-01-02	80	180
2026-01-03	120	300

Moving averages

Window frames let you define how many rows are included in the calculation. In the next example, PostgreSQL calculates the average from the current row and the six previous rows.

SELECT
  day,
  revenue,
  AVG(revenue) OVER (
    ORDER BY day
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS seven_day_average
FROM daily_revenue;

This calculates a rolling seven-day average. It is often used to smooth daily fluctuations so the trend is easier to read.

Example output:

day	revenue	seven_day_average
2026-01-07	140	121.43
2026-01-08	160	130.00
2026-01-09	155	137.86

What Are CTEs?

A common table expression, or CTE, is a named temporary result that only exists inside one query. It starts with WITH.

WITH paid_orders AS (
  SELECT *
  FROM orders
  WHERE status = 'paid'
)
SELECT customer_id, COUNT(*) AS order_count
FROM paid_orders
GROUP BY customer_id;

The paid_orders CTE makes the query easier to read. Instead of placing status = 'paid' inside a larger query, you separate it into a named step.

Example output:

customer_id	order_count
C01	2
C02	1

CTEs are especially helpful when a query has multiple stages, such as:

• filter raw data
• aggregate results
• rank rows
• select final records

Using CTEs to Make Queries Readable

Imagine you need to find the top customers by revenue from paid orders only.

Without CTEs, filtering, aggregation, and sorting can quickly become one large nested block. With CTEs, each step can be separated:

WITH paid_orders AS (
  SELECT customer_id, total_amount
  FROM orders
  WHERE status = 'paid'
),
customer_totals AS (
  SELECT
    customer_id,
    SUM(total_amount) AS total_spent
  FROM paid_orders
  GROUP BY customer_id
)
SELECT *
FROM customer_totals
ORDER BY total_spent DESC
LIMIT 10;

Example output:

customer_id	total_spent
C01	175
C02	140
C03	95

Each CTE should represent one idea. In this example, paid_orders focuses on paid orders, while customer_totals focuses on total revenue per customer. That separation makes review and debugging easier.

Good CTE names describe the meaning of the data, not just the mechanics:

• paid_orders
• monthly_revenue
• latest_customer_events
• ranked_products

Weak names like data1 or temp make complex queries harder to maintain, especially when you read the query again weeks later.

Combining CTEs and Window Functions

CTEs and window functions become especially useful together. CTEs help break the query into steps, while window functions calculate values based on order or groups.

First example: find each customer’s first paid order.

WITH paid_orders AS (
  SELECT
    order_id,
    customer_id,
    order_date,
    total_amount
  FROM orders
  WHERE status = 'paid'
),
ranked_orders AS (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY order_date
    ) AS order_position
  FROM paid_orders
)
SELECT *
FROM ranked_orders
WHERE order_position = 1;

The first CTE filters paid orders. The second CTE assigns an order position for each customer. The final query keeps only the first order.

Example output:

order_id	customer_id	order_date	total_amount	order_position
O101	C01	2026-01-01	100	1
O201	C02	2026-01-03	60	1

Another example: find the top three products in each category.

WITH product_totals AS (
  SELECT
    category_id,
    product_id,
    SUM(quantity * price) AS revenue
  FROM order_items
  GROUP BY category_id, product_id
),
ranked_products AS (
  SELECT
    *,
    DENSE_RANK() OVER (
      PARTITION BY category_id
      ORDER BY revenue DESC
    ) AS category_rank
  FROM product_totals
)
SELECT *
FROM ranked_products
WHERE category_rank <= 3;

Example output:

category_id	product_id	revenue	category_rank
C10	P01	500	1
C10	P02	500	1
C10	P03	300	2
C20	P04	900	1

This pattern is common in dashboards and reporting queries. You calculate aggregate values first, rank rows within each group, then keep the ranks you need.

Performance Considerations

Advanced SQL can be readable and still expensive. A query that feels fast on a small dataset may not be safe on production data. Always test important queries with realistic data.

Use:

EXPLAIN ANALYZE

to see how PostgreSQL executes the query: whether it uses indexes, performs large sorts, or reads too many rows.

Watch for:

• large sorts caused by ORDER BY inside a window function
• missing indexes on filter, join, or ordering columns
• CTEs that process too many rows before filtering
• repeated scans of large tables
• expensive joins before reducing the dataset

Indexes that often help include:

• columns used in WHERE
• columns used in joins
• columns used in PARTITION BY and ORDER BY, depending on query shape

Do not add indexes blindly. Indexes can speed up certain reads, but they also add storage and make writes heavier. Use the query plan to decide which indexes are actually needed.

Common Mistakes

Using window functions when GROUP BY is enough

If you only need one row per group, GROUP BY is usually simpler. Use a window function when you still need row-level detail.

Forgetting ORDER BY

Some window functions depend on order. Without a clear ORDER BY, results can be unstable or misleading, especially for ranking, running totals, LAG, and LEAD.

Creating huge CTE chains

CTEs improve readability, but too many CTEs can also make a query hard to follow. If one query starts to feel like a long pipeline, consider a view, materialized view, or application-level simplification.

Ranking before filtering

Filter early when possible. Ranking rows you do not actually need makes PostgreSQL do extra work.

Ignoring ties

Choose ROW_NUMBER, RANK, or DENSE_RANK intentionally. Ties can change business results, such as which products or candidates count as “top 3”.

Checklist

• Use GROUP BY when you want one row per group.
• Use window functions when you need row detail plus group-aware calculations.
• Always define meaningful PARTITION BY and ORDER BY.
• Use CTEs to name logical query steps.
• Give CTEs descriptive names.
• Filter early when possible.
• Check ranking behavior with ties.
• Use EXPLAIN ANALYZE for important queries.
• Test with production-like data volume.
• Add indexes based on measured query plans, not guesses.

FAQ

Are CTEs the same as temporary tables?

No. A CTE is part of a single query and disappears after that query finishes. A temporary table is created separately and can be reused within a session.

Are window functions slower than GROUP BY?

Not always. They solve different problems. Window functions may require sorting and can be expensive on large datasets, so measure with real query plans before drawing conclusions.

Can window functions be used in WHERE?

Not directly in the same query level. Use a subquery or CTE, then filter the calculated window value in the outer query.

Should every complex query use CTEs?

No. Use CTEs when they improve readability or reuse within the query. Avoid adding layers if they do not make the logic clearer.

What is the best way to learn these features?

Start with small datasets. Write one query with GROUP BY, then rewrite it with a window function. Compare the results, then look at the query plan so you understand how PostgreSQL executes each version.

Conclusion

Window functions and CTEs make PostgreSQL much more expressive. Window functions help you build rankings, running totals, row-to-row comparisons, and moving averages without losing row-level detail. CTEs help organize query logic into steps that are easier to read.

Used together, they are powerful tools for reporting, analytics, debugging, and product insights. Start with a clear question, keep each query step meaningful, and measure performance before using a query in production.