14. CTES.sql

use ecom;

-- CTES  (Common Table Expressions)
-- Alternative for subqueries. CTES is also a temporary table similar to subquery.
-- We cannot put CTES in the where clause. We can only treat it as a table.

with cte_table as -- Encapsulate the temporary table result into cte_table
(select 
	*
from 
	dim_product
where
	unit_price > (select					
						avg(unit_price)  -- Here, we are using subquery in where clause, we cannot use CTEs here.
				  from
						dim_product))
select 
	* 
from 
	cte_table 
where 
	product_name in ('Figure Method','Film Finally', 'Huge Change');
    
-- Lets say we have to use this table for next query, ie, inherit this as cte table.

with cte_table as -- Encapsulate the temporary table result into cte_table
(select 
	*
from 
	dim_product
where
	unit_price > (select					
						avg(unit_price)
				  from
						dim_product)
),
cte_table_2 as -- We wirte with command only one time.
(
	select 
		* 
	from 
		cte_table 
	where 
		product_name in ('Figure Method','Film Finally', 'Huge Change') 
)
select * 
from cte_table_2
where category = 'Clothing';