decorrelation.sql

/*
-- Correlated Query
select sum(l_extendedprice) / 7.0 as avg_yearly
from lineitem, part 
where p_partkey = l_partkey 
and p_brand = 'Brand#23' 
and p_container = 'MED BOX' 
and l_quantity < (
  select 0.2 * avg(l_quantity) from lineitem where l_partkey = p_partkey
)
*/

with my_avg(avgForPartkey, avgValue) as (
  select l_partkey, 0.2 * avg(l_quantity) 
  from lineitem 
  group by l_partkey
)

select sum(l_extendedprice) / 7.0 as avg_yearly
from lineitem, part, my_avg
where p_brand = 'Brand#23'
  and p_container = 'MED BOX'
  and p_partkey = l_partkey
  and l_quantity < my_avg.avgValue
  and my_avg.avgForPartkey = p_partkey