exec/java-exec/src/test/resources/queries/tpch/11.sql

-- tpch11 using 1395599672 as a seed to the RNG
select
  ps.ps_partkey,
  sum(ps.ps_supplycost * ps.ps_availqty) as `value`
from
  cp.`tpch/partsupp.parquet` ps,
  cp.`tpch/supplier.parquet` s,
  cp.`tpch/nation.parquet` n
where
  ps.ps_suppkey = s.s_suppkey
  and s.s_nationkey = n.n_nationkey
  and n.n_name = 'JAPAN'
group by
  ps.ps_partkey having
    sum(ps.ps_supplycost * ps.ps_availqty) > (
      select
        sum(ps.ps_supplycost * ps.ps_availqty) * 0.0001000000
      from
        cp.`tpch/partsupp.parquet` ps,
        cp.`tpch/supplier.parquet` s,
        cp.`tpch/nation.parquet` n
      where
        ps.ps_suppkey = s.s_suppkey
        and s.s_nationkey = n.n_nationkey
        and n.n_name = 'JAPAN'
    )
order by
  `value` desc;