Navigating PostgreSQL - CTEs

Introduction

PostgreSQL is a powerful, open-source object-relational database system with over 35 years of active development. It has earned its reputation through:

• Reliability: Rock-solid data integrity
• Feature Set: Advanced SQL capabilities
• Performance: Optimized query execution
• Extensibility: Custom functions and data types

Why PostgreSQL?

Let's look at the numbers that make PostgreSQL a compelling choice:

Metric	Value	Context
Global Deployments	1.7M+	Enterprise-scale adoption
Developer Preference	45.55%	Most preferred RDBMS
Market Share	17.4%	Among relational databases
User Satisfaction	80.6%	Reported happiness rate
Language Support	50+	Programming languages
Community Size	6,800+	GitHub forks
Production Usage	5M+	Active websites

De Facto goto relational database for most people for most use cases

Prerequisites

What you need to know before diving in

To get the most out of this tutorial, you should have:

• Basic understanding of SQL tables and columns
• Familiarity with SQL query syntax
• Access to a PostgreSQL database (v13 or later recommended)

WITH Queries (Common Table Expressions)

CTEs are like temporary views that exist only for the duration of your query. They help you:

• Transform complex queries into named, manageable steps
• Create reusable result sets within a single query
• Significantly improve query readability
• Make complex logic easier to understand

Example: Sales Analysis

Let's analyze product sales in top-performing regions. We'll compare traditional vs. CTE approaches:

-- Traditional Approach
SELECT
    o.region,
    o.product_id,
    SUM(o.qty) AS product_units,
    SUM(o.amount) AS product_sales
FROM
    orders o
    JOIN (
        SELECT
            region
        FROM (
            SELECT
                region,
                SUM(amount) AS total_sales
            FROM
                orders
            GROUP BY
                region

            ) AS regional_sales
        WHERE
            total_sales > 10000
        ORDER BY
            total_sales DESC
        LIMIT
            10
    ) AS top_regions ON top_regions.region = o.region
GROUP BY
    o.region,
    o.product_id;

-- Modern CTE Approach
WITH
    regional_sales AS (
        SELECT
            region,
            SUM(amount) AS total_sales
        FROM
            orders
        GROUP BY
            region
    ),
    top_regions AS (
        SELECT
            region,
            total_sales
        FROM
            regional_sales
        WHERE
            total_sales > 10000
        ORDER BY
            total_sales DESC
        LIMIT
            10
    ),
    product_wise_data AS (
        SELECT
            o.region,
            o.product_id,
            SUM(o.qty) AS product_units,
            SUM(o.amount) AS product_sales
        FROM
            orders o
            JOIN top_regions tr ON tr.region = o.region
        GROUP BY
            o.region,
            o.product_id
    )
SELECT
    *
FROM
    product_wise_data;

Key Benefits

1. Modularity: Break complex queries into manageable pieces
2. Reusability: Reference results multiple times
3. Readability: Self-documenting query structure
4. Maintainability: Easier debugging and testing

Why CTEs Win

1. ✨ Clear Structure: Each step has a meaningful name
2. 🔍 Debuggable: Test intermediate results easily
3. 📝 Self-Documenting: Logic flow is immediately apparent
4. 🔄 Reusable: Reference results multiple times

References

• PostgreSQL CTEs
• Draxlr:Common Table ExpressionsL

---

Next Steps

In the next parts, we will explore Views and Materialized Views in PostgreSQL.

---

Originally published at https://www.adiagr.com