PostgreSQL

Mastering PostgreSQL: Common Table Expressions (CTE) with Examples

Common Table Expressions (CTEs) in PostgreSQL are a powerful feature that simplifies complex queries by breaking them into smaller, more manageable components. A CTE is a temporary result set that can be referenced within the execution of a single SELECT, INSERT, UPDATE, or DELETE statement. CTEs make queries more readable, reusable, and easier to debug, especially when working with hierarchical or recursive data.

Below is a beginner-friendly guide to using CTEs with practical examples.

1. What is a Common Table Expression (CTE)?

A CTE begins with the WITH keyword, followed by a temporary table definition. It allows you to write modular queries that improve the structure of complex operations by splitting them into logical parts.

WITH cte_name AS (
    SELECT column1, column2
    FROM some_table
    WHERE condition
)
SELECT *
FROM cte_name;

2. Advantages of Using CTEs

• Improves Readability: Breaks down complex queries into logical steps.
• Encourages Reusability: CTEs can be referenced multiple times in the main query.
• Facilitates Recursion: Enables recursive queries, such as hierarchical relationships.

3. Examples of CTEs

3.1 Basic CTE: Listing Customers with High Balance

This example finds customers who have accounts with a balance greater than $10,000.

WITH high_balance_accounts AS (
    SELECT customer_id, account_id, balance
    FROM accounts
    WHERE balance > 10000
)
SELECT
    customers.customer_id,
    customers.name,
    high_balance_accounts.balance
FROM customers
JOIN high_balance_accounts ON customers.customer_id = high_balance_accounts.customer_id;

3.2 Chaining Multiple CTEs

This example uses two CTEs: one to calculate total transactions per customer and another to fetch customers with total transactions above a certain threshold.

WITH customer_transactions AS (
    SELECT customer_id, SUM(amount) AS total_transactions
    FROM transactions
    GROUP BY customer_id
),
high_value_customers AS (
    SELECT customer_id, total_transactions
    FROM customer_transactions
    WHERE total_transactions > 50000
)
SELECT
    customers.customer_id,
    customers.name,
    high_value_customers.total_transactions
FROM customers
JOIN high_value_customers ON customers.customer_id = high_value_customers.customer_id;

3.3 Recursive CTE: Finding Transaction Chains

This example demonstrates recursion by finding all related transactions starting from a specific transaction.

WITH RECURSIVE transaction_chain AS (
    SELECT transaction_id, account_id, amount, parent_transaction_id
    FROM transactions
    WHERE transaction_id = 1  -- Start from a specific transaction
    UNION ALL
    SELECT t.transaction_id, t.account_id, t.amount, t.parent_transaction_id
    FROM transactions t
    JOIN transaction_chain tc ON t.parent_transaction_id = tc.transaction_id
)
SELECT *
FROM transaction_chain;

4. Best Practices

• Keep CTEs Focused
  Each CTE should perform one specific task to improve readability and modularity.
• Avoid Excessive Nesting
  Overusing nested CTEs can lead to performance bottlenecks. Combine where practical.
• Optimize Query Performance
  Analyze and test CTEs separately to ensure efficient execution.
• Leverage Recursive CTEs with Caution
  Recursive CTEs can be resource-intensive. Always include a termination condition.
• Use Descriptive Names
  Choose meaningful names for CTEs and columns to make queries self-explanatory.

CTEs are an essential tool for structuring queries effectively in PostgreSQL. By mastering them, you can write cleaner, more maintainable, and scalable SQL queries for your banking applications and beyond.