Lecture 49: Organizing Complex Queries with CTEsv

This lecture introduces Common Table Expressions (CTEs).

As your SQL queries become more complex, especially with multiple subqueries or joins, they can become difficult to read and understand. Common Table Expressions (CTEs), introduced with the `WITH` clause, help you break down complex queries into smaller, more readable, and manageable logical units.

Think of a CTE as a temporary, named result set that you can reference within a single SQL statement (SELECT, INSERT, UPDATE, or DELETE).

The `WITH` Clause Syntax

The basic syntax for a CTE is:

WITH cte_name AS (
    -- Your SELECT statement for the CTE
    SELECT column1, column2 FROM table_name WHERE condition
)
SELECT * FROM cte_name WHERE another_condition;

You can define multiple CTEs, separated by commas, before your final `SELECT` statement.

Example: Average Comments per Author (using CTE)

Let’s revisit a problem we might have solved with nested subqueries: finding the average number of comments per author. Using CTEs makes the logic much clearer.

WITH PostCommentCounts AS (
    SELECT
        p.Author_ID,
        COUNT(c.CommentID) AS CommentCount
    FROM Posts p
    LEFT JOIN Comments c ON p.PostID = c.Post_ID
    GROUP BY p.PostID
),
AuthorTotalComments AS (
    SELECT
        a.AuthorName,
        SUM(pcc.CommentCount) AS TotalComments
    FROM Authors a
    JOIN PostCommentCounts pcc ON a.AuthorID = pcc.Author_ID
    GROUP BY a.AuthorName
)
SELECT
    AuthorName,
    TotalComments,
    CAST(TotalComments AS REAL) / (SELECT COUNT(PostID) FROM Posts WHERE Author_ID = (SELECT AuthorID FROM Authors WHERE AuthorName = AuthorTotalComments.AuthorName)) AS AverageCommentsPerPost
FROM AuthorTotalComments;

This example is a bit complex, but it shows how you can chain CTEs. `AuthorTotalComments` uses the result of `PostCommentCounts`.

The final `SELECT` statement then uses `AuthorTotalComments` as if it were a regular table.

Benefits of CTEs

• Readability: Breaks down complex logic into smaller, named steps.
• Modularity: Each CTE can be developed and tested independently.
• Reusability: A CTE can be referenced multiple times within the same query.
• Recursion: CTEs are essential for recursive queries (though we won’t cover those in this series).

In the Next Lecture…

We will explore Window Functions. These are advanced SQL functions that perform calculations across a set of table rows that are related to the current row. They are incredibly powerful for ranking, running totals, and more complex analytical tasks.

Lecture 48: Working with Dates and Times