drop table if exists hawqsql.q2_minimum_cost_supplier_tmp1;
create table hawqsql.q2_minimum_cost_supplier_tmp1 (s_acctbal double precision, s_name text, n_name text, p_partkey int, ps_supplycost double precision, p_mfgr text, s_address text, s_phone text, s_comment text);
drop table if exists hawqsql.q2_minimum_cost_supplier_tmp2;
create table hawqsql.q2_minimum_cost_supplier_tmp2 (p_partkey int, ps_min_supplycost double precision);
drop table if exists hawqsql.q2_minimum_cost_supplier;
create table hawqsql.q2_minimum_cost_supplier (s_acctbal double precision, s_name text, n_name text, p_partkey int, p_mfgr text, s_address text, s_phone text, s_comment text);

-- the query
insert into hawqsql.q2_minimum_cost_supplier_tmp1
select
  s.s_acctbal,
  s.s_name,
  n.n_name,
  p.p_partkey,
  ps.ps_supplycost,
  p.p_mfgr,
  s.s_address,
  s.s_phone,
  s.s_comment
from
  hawqsql.nation n join hawqsql.region r
  on
    n.n_regionkey = r.r_regionkey and r.r_name = 'EUROPE'
  join hawqsql.supplier s
  on
s.s_nationkey = n.n_nationkey
  join hawqsql.partsupp ps
  on
s.s_suppkey = ps.ps_suppkey
  join hawqsql.part p
  on
    p.p_partkey = ps.ps_partkey
and
p.p_size = 37 and p.p_type like '%COPPER' ;

insert overwrite table q2_minimum_cost_supplier_tmp2
select
  p_partkey, min(ps_supplycost)
from
  q2_minimum_cost_supplier_tmp1
group by p_partkey;

insert overwrite table q2_minimum_cost_supplier
select
  t1.s_acctbal,
  t1.s_name,
  t1.n_name,
  t1.p_partkey,
  t1.p_mfgr,
  t1.s_address,
  t1.s_phone,
  t1.s_comment
from
  hawqsql.q2_minimum_cost_supplier_tmp1 t1 join hawqsql.q2_minimum_cost_supplier_tmp2 t2
on
  t1.p_partkey = t2.p_partkey
and
t1.ps_supplycost=t2.ps_min_supplycost
order by
s_acctbal desc, n_name, s_name, p_partkey
limit 100;