Decorrelated Query

query.sql

/*
select count(o_orderkey)
from orders o1
where o_totalprice < (
  select avg(o_totalprice) from orders o2 where o2.o_shippriority = o1.o_shippriority or o2.o_orderstatus = o1.o_orderstatus
)

Result: 788047
*/


with all_shippriorities as (
  select distinct o_shippriority from orders
),

all_orderstatus as (
  select distinct o_orderstatus from orders
),

all_combinations as (
  select all_shippriorities.o_shippriority, 
  	all_orderstatus.o_orderstatus,
  	avg(o_totalprice) calculated_avg
  from all_shippriorities, all_orderstatus, orders
  where all_shippriorities.o_shippriority = orders.o_shippriority or all_orderstatus.o_orderstatus = orders.o_orderstatus
  group by all_shippriorities.o_shippriority, all_orderstatus.o_orderstatus
)

select count(orders.o_orderkey)
from all_combinations, orders
where all_combinations.o_shippriority = orders.o_shippriority and all_combinations.o_orderstatus = orders.o_orderstatus
  and orders.o_totalprice < all_combinations.calculated_avg
  
/*
Result: 788047
*/