Very good. Look at another example of the CTE with INSERT statement.
WITH avg_prices AS (
SELECT
grocery_store_id,
avg(price) AS avg_price
FROM product
GROUP BY grocery_store_id
)
INSERT INTO grocery_avg_prices
SELECT
*
FROM avg_prices
WHERE grocery_store_id != 2;
In this case we created the CTE to retrieve the average price of products for each grocery store. We use the AVG aggregate function to compute average price of all products of the grocery store. In this query the records are grouped according to grocery_store_id using the GROUP BY clause.
In the outer query we insert rows into table grocery_avg_prices, but only for grocery stores with id different than 2.
We have created a new table called product_max_price. Let's insert data into this table: the names of products with their highest prices.
In the CTE select name and max price for every product.
In the outer statement insert into table product_max_price the name of products and their highest prices, but only for products 'Bread' and 'Milk'.
Select name and maximum price for every product in a CTE. Then, insert into table product_max_price the name and maximum price from CTE for products 'Milk' and 'Bread'.
