Can you explain what common table expressions (CTE) are in SQL and what they are used for?

Common Table Expressions (CTE) in SQL are temporary named result sets that are defined within the execution of a single SQL query. They allow structuring complex queries, making them more readable and maintainable.

CTEs are declared using the WITH keyword, followed by the CTE name and the query definition. CTEs can be used for:

• Breaking down complex queries into logical parts.
• Recursive queries (e.g., traversing hierarchies).
• Reusing the same subquery in the main query.

Example of using CTE:

WITH RecentOrders AS (
    SELECT OrderID, CustomerID, OrderDate
    FROM Orders
    WHERE OrderDate > '2024-01-01'
)
SELECT CustomerID, COUNT(OrderID) AS OrderCount
FROM RecentOrders
GROUP BY CustomerID;

Here, RecentOrders is a CTE that first selects orders after a certain date, and then the main query aggregates data by customers.