CTE | Common Table Expression | Real world scenarios examples

A Common Table Expression (CTE) -

Common Table Expression (CTE) is a temporary, named result set used to simplify complex queries and make code easier to read and manage. It’s defined within a WITH clause and can be referenced just like a regular table within the query.

Basic Syntax of a CTE

WITH CTE_Name AS (
-- CTE Query (subquery)
SELECT column1, column2
FROM Table
WHERE condition
)
-- Main query that uses the CTE
SELECT *
FROM CTE_Name;

Common Table Expressions (CTEs) are highly versatile and are used in various real-world scenarios, especially in production environments, to handle complex query requirements efficiently. Here are some common use cases where CTEs shine in real-time applications

1. Recursive Data Processing (Hierarchies and Tree Structures)

Example: Organization charts, bill of materials, or any hierarchical data structures.

Use Case: A company wants to create an organizational chart where each employee reports to a manager. Recursive CTEs can help retrieve hierarchical data in a single query by starting from the top-level manager and recursively fetching each level of employees.

WITH EmployeeHierarchy AS (
    SELECT EmployeeID, EmployeeName, ManagerID
    FROM Employees
    WHERE ManagerID IS NULL  -- Start from top-level manager

    UNION ALL

    SELECT e.EmployeeID, e.EmployeeName, e.ManagerID
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT * FROM EmployeeHierarchy;

2. Data Aggregation and Summarization

Example: Monthly or yearly sales reports, product sales aggregation, order summary.

Use Case: A CTE can be used to calculate aggregated metrics like total sales, revenue, or customer orders and then reused in further calculations or to filter out unnecessary data. This can optimize performance by avoiding complex aggregation in the main query.

WITH MonthlySales AS (
    SELECT ProductID, SUM(SalesAmount) AS TotalSales, MONTH(SaleDate) AS SaleMonth
    FROM Sales
    GROUP BY ProductID, MONTH(SaleDate)
)
SELECT * FROM MonthlySales
WHERE TotalSales > 10000;  -- Filter to show only high-sales months

3. Window Functions and Ranking

Example: Ranking products by sales, ordering employees by salary within departments.

Use Case: Often used in reporting where data needs to be ranked or partitioned by certain criteria. CTEs with window functions allow you to rank, row number, or calculate cumulative totals efficiently.

WITH ProductRanking AS (
    SELECT ProductID, ProductName, SalesAmount,
           RANK() OVER (ORDER BY SalesAmount DESC) AS SalesRank
    FROM Products
)
SELECT * FROM ProductRanking WHERE SalesRank <= 5;  -- Top 5 products by sales

4. Data Transformation for ETL Processes

Example: Data staging, data cleansing, and formatting.

Use Case: In ETL operations, CTEs can handle temporary transformations or formatting within the SQL query, such as converting date formats, cleaning data, or merging datasets before inserting into the final table.

WITH CleanedData AS (
    SELECT ProductID, TRIM(ProductName) AS ProductName, 
           FORMAT(SaleDate, 'yyyy-MM-dd') AS SaleDate
    FROM RawSalesData
    WHERE ProductName IS NOT NULL
)
INSERT INTO Sales (ProductID, ProductName, SaleDate)
SELECT * FROM CleanedData;

These examples demonstrate how CTEs can be a flexible and powerful tool in production SQL queries, especially when dealing with complex logic, recursive data, or performance optimization. They allow for cleaner and more manageable SQL code, making maintenance and debugging significantly easier.