SQL Common Table Expressions Refactoring a query to use Common Table Expressions
Example
Suppose we want to get all product categories with total sales greater than 20.
Here is a query without Common Table Expressions:
SELECT category.description, sum(product.price) as total_sales
FROM sale
LEFT JOIN product on sale.product_id = product.id
LEFT JOIN category on product.category_id = category.id
GROUP BY category.id, category.description
HAVING sum(product.price) > 20
And an equivalent query using Common Table Expressions:
WITH all_sales AS (
SELECT product.price, category.id as category_id, category.description as category_description
FROM sale
LEFT JOIN product on sale.product_id = product.id
LEFT JOIN category on product.category_id = category.id
)
, sales_by_category AS (
SELECT category_description, sum(price) as total_sales
FROM all_sales
GROUP BY category_id, category_description
)
SELECT * from sales_by_category WHERE total_sales > 20
