Mastering SQL Common Table Expressions (CTEs): Simplify Your Queries

WSDA News | December 14, 2024?

Writing clean and efficient SQL queries is a cornerstone of effective data analysis. But as queries grow in complexity, they can become hard to read and maintain. That’s where Common Table Expressions (CTEs) shine. This article will explore the power of CTEs, how to create them, and practical examples of their use in simplifying and optimizing SQL workflows.

What Is a Common Table Expression (CTE)?

A CTE is a temporary result set that simplifies complex SQL queries by breaking them into manageable parts. Think of a CTE as a modular subquery that makes your code more readable and maintainable. Unlike views, which are stored permanently, CTEs exist only for the duration of the query.

Syntax Basics

Here’s the general structure for creating a CTE:

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

CTEs can also be used to select specific columns:

?WITH cte_name AS (  
    SELECT column1, column2  
    FROM table_name  
    WHERE condition  
)  
SELECT column1  
FROM cte_name;          

Why Use CTEs?

• Readability: Simplify lengthy and complex SQL statements.
• Reusability: Reference the same result set multiple times in a query.
• Modularity: Break a single query into logical steps.
• Recursion: Solve hierarchical problems, such as navigating tree-like data structures.

Real-World Examples

Example 1: Counting Customers by Year

Let’s say you want to analyze customer growth by year.

WITH NumberCustomerByYear AS (  
    SELECT  
        STRFTIME('%Y', join_date) AS Year,  
        COUNT(*) AS NumberCustomers  
    FROM  
        Customers  
    GROUP BY  
        STRFTIME('%Y', join_date)  
)  
SELECT *  
FROM NumberCustomerByYear  
ORDER BY  
    Year DESC;          

Output: Displays the number of customers joining each year in descending order.

Example 2: Identifying Popular Products

This example lists products sold more than three times by joining the Products and OrderDetails tables.

?WITH PopularProducts AS (  
    SELECT  
        p.name AS product_name,  
        SUM(od.quantity) AS total_quantity_sold  
    FROM  
        Products p  
    LEFT JOIN  
        OrderDetails od  
    ON  
        p.product_id = od.product_id  
    GROUP BY  
        p.name  
    HAVING  
        SUM(od.quantity) > 3  
)  
SELECT *  
FROM  
    PopularProducts  
ORDER BY  
    total_quantity_sold DESC;          

Output: A list of popular products ranked by total quantity sold.

Example 3: Comparing Monthly Orders with Previous Months

By using multiple CTEs, you can compare order volumes month over month.

WITH MonthlyOrders AS (  
    SELECT  
        STRFTIME('%Y', order_date) AS order_year,  
        CAST(STRFTIME('%m', order_date) AS INTEGER) AS order_month,  
        COUNT(order_id) AS total_orders  
    FROM  
        Orders  
    GROUP BY  
        STRFTIME('%Y', order_date),  
        STRFTIME('%m', order_date)  
),  
MonthlyComparison AS (  
    SELECT  
        mo1.order_year,  
        mo1.order_month,  
        mo1.total_orders AS current_month_orders,  
        COALESCE(mo2.total_orders, 0) AS previous_month_orders,  
        mo1.total_orders - COALESCE(mo2.total_orders, 0) AS order_difference  
    FROM  
        MonthlyOrders mo1  
    LEFT JOIN  
        MonthlyOrders mo2  
        ON  
            (mo1.order_year = mo2.order_year AND mo1.order_month = mo2.order_month + 1)  
            OR  
            (mo1.order_year = mo2.order_year + 1 AND mo1.order_month = 1 AND mo2.order_month = 12)  
)  
SELECT *  
FROM  
    MonthlyComparison  
ORDER BY  
    order_year DESC,  
    order_month DESC;          

Output: A detailed comparison of monthly order counts.

Key Functions Used

• COUNT(): Counts rows in a table or grouped dataset.
• SUM(): Adds up numeric values.
• STRFTIME(): Extracts date parts like year and month in SQLite.
• COALESCE(): Replaces NULL values with a default (e.g., 0).

Tools to Simplify SQL

To explore CTEs in a hands-on way, consider using tools like:

• DBeaver: A free, user-friendly database management tool.
• ChatGPT: To generate synthetic data for practice.

Final Thoughts

CTEs are a game-changer for anyone working with SQL. They make queries cleaner, more modular, and easier to debug. Whether you’re dealing with hierarchical data, simplifying complex joins, or comparing datasets, mastering CTEs will level up your SQL skills.

Data No Doubt! Check out WSDALearning.ai and start learning Data Analytics and Data Science Today!