What are CTEs used for?

Common Table Expressions (CTEs) are used in SQL for several purposes:

1. Improving Readability: CTEs allow you to break complex queries into simpler parts, making them easier to read and maintain.

2. Recursive Queries: CTEs can be recursive, which is useful for querying hierarchical data, such as organizational charts or tree structures.

3. Temporary Result Sets: CTEs can be used to define temporary result sets that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement.

4. Modularizing Queries: They help in modularizing queries by allowing you to define a CTE once and use it multiple times within the same query.

Here’s a simple example of a CTE:

WITH SalesCTE AS (
    SELECT SalesPerson, SUM(SalesAmount) AS TotalSales
    FROM Sales
    GROUP BY SalesPerson
)
SELECT SalesPerson, TotalSales
FROM SalesCTE
WHERE TotalSales > 10000;

In this example, SalesCTE calculates total sales per salesperson, which is then used in the main query to filter results.