C.T.E Common Table Expressions

Welcome People, Bots & Algo’s of THE INTERWEBS. This is the Monday Edition of The Analyst.

Here At the @analyst we don’t give the most in-depth explanation or tutorial, We give just enough so you can be dangerous enough.

?

What is a CTE in SQL?

A CTE (Common Table Expression) in SQL is a temporary result set defined within the execution scope of a SELECT, INSERT, UPDATE, or DELETE statement. It simplifies complex queries by allowing you to break them into reusable subqueries that improve readability and maintainability.

?

Why do We Need CTE in the SQL ?

CTEs are defined using the WITH clause and can be referenced multiple times within the main SQL query. This makes CTEs a great alternative to subqueries, especially in cases where we need to perform the same operation multiple times or create recursive queries.

?

Why Use CTEs in SQL?

CTEs simplify query writing and maintenance by:

• Breaking down complex queries into smaller, reusable components.

• Improving code readability and modularity.

• Enabling recursive operations for hierarchical data.

DISCLAIMER I introduced the @nalyst to CHAT GPT

This exam were create by CHATGPT and the Thumbnail created by Dall-E

Scenario: Calculate total sales amount per customer

We will use a CTE to first calculate the total sales for each customer, then query the CTE for further analysis.

Solution

-- Create a CTE to get the employee hierarchy
-- Define a simple CTE to calculate total sales per customer
WITH CustomerSales AS (
    SELECT 
        CustomerID,
        SUM(TotalDue) AS TotalSales
    FROM 
        Sales.SalesOrderHeader
    GROUP BY 
        CustomerID
)
-- Use the CTE to display the results
SELECT 
    CustomerID,
    TotalSales
FROM 
    CustomerSales
ORDER BY 
    TotalSales DESC;        

Explanation

1. CTE Definition: The WITH clause defines the CTE named CustomerSales. Inside the CTE, we calculate the total sales (SUM(TotalDue)) for each customer from the Sales.SalesOrderHeader table.
2. Final Query: The SELECT retrieves data from the CustomerSales CTE. We order the results by TotalSales in descending order to see the top customers.