Page 44 - SQL

-- all_sales: just a simple SELECT with all the needed JOINS
WITH all_sales AS (
SELECT
product.price as 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
)
-- Group by category
, sales_by_category AS (
SELECT category_id, category_description,
sum(product_price) as total_sales
FROM all_sales
GROUP BY category_id, category_description
)
-- Filtering total_sales > 20
, top_categories AS (
SELECT * from sales_by_category WHERE total_sales > 20
)
-- all_products: just a simple SELECT with all the needed JOINS
, all_products AS (
SELECT
product.id as product_id,
product.description as product_description,
product.price as product_price,
category.id as category_id,
category.description as category_description
FROM product
LEFT JOIN category on product.category_id = category.id
)
-- Order by product price
, cheapest_products AS (
SELECT * from all_products
ORDER by product_price ASC
)
-- Simple inner join
, cheapest_products_from_top_categories AS (
SELECT product_description, product_price
FROM cheapest_products
INNER JOIN top_categories ON cheapest_products.category_id = top_categories.category_id
)
--The main SELECT
SELECT * from cheapest_products_from_top_categories

Read Common Table Expressions online: https://riptutorial.com/sql/topic/747/common-table-
expressions

https://riptutorial.com/ 26