benchmarks/sql_benchmarks/tpch/benchmarks/q16.benchmark

name Q16
group tpch
subgroup sf${BENCH_SIZE:-1}

init sql_benchmarks/tpch/init/set_config.sql

load sql_benchmarks/tpch/init/load_${TPCH_FILE_TYPE:-parquet}.sql

assert I
SELECT COUNT(*) > 0 from lineitem;
----
true

run
select
    p_brand,
    p_type,
    p_size,
    count(distinct ps_suppkey) as supplier_cnt
from
    partsupp,
    part
where
        p_partkey = ps_partkey
  and p_brand <> 'Brand#45'
  and p_type not like 'MEDIUM POLISHED%'
  and p_size in (49, 14, 23, 45, 19, 3, 36, 9)
  and ps_suppkey not in (
    select
        s_suppkey
    from
        supplier
    where
            s_comment like '%Customer%Complaints%'
)
group by
    p_brand,
    p_type,
    p_size
order by
    supplier_cnt desc,
    p_brand,
    p_type,
    p_size;

result sql_benchmarks/tpch/results/sf${BENCH_SIZE:-1}/q16.csv

cleanup sql_benchmarks/tpch/init/cleanup.sql