github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/benchmark/tpch/04_CLEANUP/03_Snapshot_tpch.sql

use tpch;
-- tpch q1 --
select
	l_returnflag,
	l_linestatus,
	sum(l_quantity) as sum_qty,
	sum(l_extendedprice) as sum_base_price,
	sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
	sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
	avg(l_quantity) as avg_qty,
	avg(l_extendedprice) as avg_price,
	avg(l_discount) as avg_disc,
	count(*) as count_order
from
	lineitem {snapshot = 'tpch_snapshot'}
where
	l_shipdate <= date '1998-12-01' - interval '112' day
group by
	l_returnflag,
	l_linestatus
order by
	l_returnflag,
	l_linestatus
;

select
	l_returnflag,
	l_linestatus,
	sum(l_quantity) as sum_qty,
	sum(l_extendedprice) as sum_base_price,
	sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
	sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
	avg(l_quantity) as avg_qty,
	avg(l_extendedprice) as avg_price,
	avg(l_discount) as avg_disc,
	count(*) as count_order
from
	lineitem {snapshot = 'tpch_snapshot'}
where
	l_shipdate <= date '1998-12-01' - interval '112' day
group by
	l_returnflag,
	l_linestatus
order by
	l_returnflag,
	l_linestatus
;

select
	l_returnflag,
	l_linestatus,
	sum(l_quantity) as sum_qty,
	sum(l_extendedprice) as sum_base_price,
	sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
	sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
	avg(l_quantity) as avg_qty,
	avg(l_extendedprice) as avg_price,
	avg(l_discount) as avg_disc,
	count(*) as count_order
from
	lineitem {snapshot = 'tpch_snapshot'}
where
	l_shipdate <= date '1998-12-01' - interval '112' day
group by
	l_returnflag,
	l_linestatus
order by
	l_returnflag,
	l_linestatus
;

-- tpch q2 --
select
	s_acctbal,
	s_name,
	n_name,
	p_partkey,
	p_mfgr,
	s_address,
	s_phone,
	s_comment
from
	part {snapshot = 'tpch_snapshot'},
	supplier {snapshot = 'tpch_snapshot'},
	partsupp {snapshot = 'tpch_snapshot'},
	nation {snapshot = 'tpch_snapshot'},
	region {snapshot = 'tpch_snapshot'}
where
	p_partkey = ps_partkey
	and s_suppkey = ps_suppkey
	and p_size = 48
	and p_type like '%TIN'
	and s_nationkey = n_nationkey
	and n_regionkey = r_regionkey
	and r_name = 'MIDDLE EAST'
	and ps_supplycost = (
		select
			min(ps_supplycost)
		from
			partsupp {snapshot = 'tpch_snapshot'},
			supplier {snapshot = 'tpch_snapshot'},
			nation {snapshot = 'tpch_snapshot'},
			region {snapshot = 'tpch_snapshot'}
		where
			p_partkey = ps_partkey
			and s_suppkey = ps_suppkey
			and s_nationkey = n_nationkey
			and n_regionkey = r_regionkey
			and r_name = 'MIDDLE EAST'
	)
order by
	s_acctbal desc,
	n_name,
	s_name,
	p_partkey
limit 100
;

select
	s_acctbal,
	s_name,
	n_name,
	p_partkey,
	p_mfgr,
	s_address,
	s_phone,
	s_comment
from
	part {snapshot = 'tpch_snapshot'},
	supplier {snapshot = 'tpch_snapshot'},
	partsupp {snapshot = 'tpch_snapshot'},
	nation {snapshot = 'tpch_snapshot'},
	region {snapshot = 'tpch_snapshot'}
where
	p_partkey = ps_partkey
	and s_suppkey = ps_suppkey
	and p_size = 48
	and p_type like '%TIN'
	and s_nationkey = n_nationkey
	and n_regionkey = r_regionkey
	and r_name = 'MIDDLE EAST'
	and ps_supplycost = (
		select
			min(ps_supplycost)
		from
			partsupp {snapshot = 'tpch_snapshot'},
			supplier {snapshot = 'tpch_snapshot'},
			nation {snapshot = 'tpch_snapshot'},
			region {snapshot = 'tpch_snapshot'}
		where
			p_partkey = ps_partkey
			and s_suppkey = ps_suppkey
			and s_nationkey = n_nationkey
			and n_regionkey = r_regionkey
			and r_name = 'MIDDLE EAST'
	)
order by
	s_acctbal desc,
	n_name,
	s_name,
	p_partkey
limit 100
;

select
	s_acctbal,
	s_name,
	n_name,
	p_partkey,
	p_mfgr,
	s_address,
	s_phone,
	s_comment
from
	part {snapshot = 'tpch_snapshot'},
	supplier {snapshot = 'tpch_snapshot'},
	partsupp {snapshot = 'tpch_snapshot'},
	nation {snapshot = 'tpch_snapshot'},
	region {snapshot = 'tpch_snapshot'}
where
	p_partkey = ps_partkey
	and s_suppkey = ps_suppkey
	and p_size = 48
	and p_type like '%TIN'
	and s_nationkey = n_nationkey
	and n_regionkey = r_regionkey
	and r_name = 'MIDDLE EAST'
	and ps_supplycost = (
		select
			min(ps_supplycost)
		from
			partsupp {snapshot = 'tpch_snapshot'},
			supplier {snapshot = 'tpch_snapshot'},
			nation {snapshot = 'tpch_snapshot'},
			region {snapshot = 'tpch_snapshot'}
		where
			p_partkey = ps_partkey
			and s_suppkey = ps_suppkey
			and s_nationkey = n_nationkey
			and n_regionkey = r_regionkey
			and r_name = 'MIDDLE EAST'
	)
order by
	s_acctbal desc,
	n_name,
	s_name,
	p_partkey
limit 100
;

-- tpch q3 --
select
	l_orderkey,
	sum(l_extendedprice * (1 - l_discount)) as revenue,
	o_orderdate,
	o_shippriority
from
	customer {snapshot = 'tpch_snapshot'},
	orders {snapshot = 'tpch_snapshot'},
	lineitem {snapshot = 'tpch_snapshot'}
where
	c_mktsegment = 'HOUSEHOLD'
	and c_custkey = o_custkey
	and l_orderkey = o_orderkey
	and o_orderdate < date '1995-03-29'
	and l_shipdate > date '1995-03-29'
group by
	l_orderkey,
	o_orderdate,
	o_shippriority
order by
	revenue desc,
	o_orderdate
limit 10
;

select
	l_orderkey,
	sum(l_extendedprice * (1 - l_discount)) as revenue,
	o_orderdate,
	o_shippriority
from
	customer {snapshot = 'tpch_snapshot'},
	orders {snapshot = 'tpch_snapshot'},
	lineitem {snapshot = 'tpch_snapshot'}
where
	c_mktsegment = 'HOUSEHOLD'
	and c_custkey = o_custkey
	and l_orderkey = o_orderkey
	and o_orderdate < date '1995-03-29'
	and l_shipdate > date '1995-03-29'
group by
	l_orderkey,
	o_orderdate,
	o_shippriority
order by
	revenue desc,
	o_orderdate
limit 10
;

select
	l_orderkey,
	sum(l_extendedprice * (1 - l_discount)) as revenue,
	o_orderdate,
	o_shippriority
from
	customer {snapshot = 'tpch_snapshot'},
	orders {snapshot = 'tpch_snapshot'},
	lineitem {snapshot = 'tpch_snapshot'}
where
	c_mktsegment = 'HOUSEHOLD'
	and c_custkey = o_custkey
	and l_orderkey = o_orderkey
	and o_orderdate < date '1995-03-29'
	and l_shipdate > date '1995-03-29'
group by
	l_orderkey,
	o_orderdate,
	o_shippriority
order by
	revenue desc,
	o_orderdate
limit 10
;

-- tpch q4 --
select
    o_orderpriority,
    count(*) as order_count
from
    orders {snapshot = 'tpch_snapshot'}
where
    o_orderdate >= date '1997-07-01'
    and o_orderdate < date '1997-07-01' + interval '3' month
    and exists (
        select
            *
        from
            lineitem {snapshot = 'tpch_snapshot'}
        where
            l_orderkey = o_orderkey
            and l_commitdate < l_receiptdate
    )
group by
    o_orderpriority
order by
    o_orderpriority
;

select
    o_orderpriority,
    count(*) as order_count
from
    orders {snapshot = 'tpch_snapshot'}
where
    o_orderdate >= date '1997-07-01'
    and o_orderdate < date '1997-07-01' + interval '3' month
    and exists (
        select
            *
        from
            lineitem {snapshot = 'tpch_snapshot'}
        where
            l_orderkey = o_orderkey
            and l_commitdate < l_receiptdate
    )
group by
    o_orderpriority
order by
    o_orderpriority
;

select
    o_orderpriority,
    count(*) as order_count
from
    orders {snapshot = 'tpch_snapshot'}
where
    o_orderdate >= date '1997-07-01'
    and o_orderdate < date '1997-07-01' + interval '3' month
    and exists (
        select
            *
        from
            lineitem {snapshot = 'tpch_snapshot'}
        where
            l_orderkey = o_orderkey
            and l_commitdate < l_receiptdate
    )
group by
    o_orderpriority
order by
    o_orderpriority
;

-- tpch q5 --
select
	n_name,
	sum(l_extendedprice * (1 - l_discount)) as revenue
from
	customer {snapshot = 'tpch_snapshot'},
	orders {snapshot = 'tpch_snapshot'},
	lineitem {snapshot = 'tpch_snapshot'},
	supplier {snapshot = 'tpch_snapshot'},
	nation {snapshot = 'tpch_snapshot'},
	region {snapshot = 'tpch_snapshot'}
where
	c_custkey = o_custkey
	and l_orderkey = o_orderkey
	and l_suppkey = s_suppkey
	and c_nationkey = s_nationkey
	and s_nationkey = n_nationkey
	and n_regionkey = r_regionkey
	and r_name = 'AMERICA'
	and o_orderdate >= date '1994-01-01'
	and o_orderdate < date '1994-01-01' + interval '1' year
group by
	n_name
order by
	revenue desc
;

select
	n_name,
	sum(l_extendedprice * (1 - l_discount)) as revenue
from
	customer {snapshot = 'tpch_snapshot'},
	orders {snapshot = 'tpch_snapshot'},
	lineitem {snapshot = 'tpch_snapshot'},
	supplier {snapshot = 'tpch_snapshot'},
	nation {snapshot = 'tpch_snapshot'},
	region {snapshot = 'tpch_snapshot'}
where
	c_custkey = o_custkey
	and l_orderkey = o_orderkey
	and l_suppkey = s_suppkey
	and c_nationkey = s_nationkey
	and s_nationkey = n_nationkey
	and n_regionkey = r_regionkey
	and r_name = 'AMERICA'
	and o_orderdate >= date '1994-01-01'
	and o_orderdate < date '1994-01-01' + interval '1' year
group by
	n_name
order by
	revenue desc
;

select
	n_name,
	sum(l_extendedprice * (1 - l_discount)) as revenue
from
	customer {snapshot = 'tpch_snapshot'},
	orders {snapshot = 'tpch_snapshot'},
	lineitem {snapshot = 'tpch_snapshot'},
	supplier {snapshot = 'tpch_snapshot'},
	nation {snapshot = 'tpch_snapshot'},
	region {snapshot = 'tpch_snapshot'}
where
	c_custkey = o_custkey
	and l_orderkey = o_orderkey
	and l_suppkey = s_suppkey
	and c_nationkey = s_nationkey
	and s_nationkey = n_nationkey
	and n_regionkey = r_regionkey
	and r_name = 'AMERICA'
	and o_orderdate >= date '1994-01-01'
	and o_orderdate < date '1994-01-01' + interval '1' year
group by
	n_name
order by
	revenue desc
;

-- tpch q6 --
select
	sum(l_extendedprice * l_discount) as revenue
from
	lineitem {snapshot = 'tpch_snapshot'}
where
	l_shipdate >= date '1994-01-01'
	and l_shipdate < date '1994-01-01' + interval '1' year
	and l_discount between 0.03 - 0.01 and 0.03 + 0.01
	and l_quantity < 24;

select
	sum(l_extendedprice * l_discount) as revenue
from
	lineitem {snapshot = 'tpch_snapshot'}
where
	l_shipdate >= date '1994-01-01'
	and l_shipdate < date '1994-01-01' + interval '1' year
	and l_discount between 0.03 - 0.01 and 0.03 + 0.01
	and l_quantity < 24;

select
	sum(l_extendedprice * l_discount) as revenue
from
	lineitem {snapshot = 'tpch_snapshot'}
where
	l_shipdate >= date '1994-01-01'
	and l_shipdate < date '1994-01-01' + interval '1' year
	and l_discount between 0.03 - 0.01 and 0.03 + 0.01
	and l_quantity < 24;

-- tpch q7 --
select
    supp_nation,
    cust_nation,
    l_year,
    sum(volume) as revenue
from
    (
        select
            n1.n_name as supp_nation,
            n2.n_name as cust_nation,
            extract(year from l_shipdate) as l_year,
            l_extendedprice * (1 - l_discount) as volume
        from
            supplier {snapshot = 'tpch_snapshot'},
            lineitem {snapshot = 'tpch_snapshot'},
            orders {snapshot = 'tpch_snapshot'},
            customer {snapshot = 'tpch_snapshot'},
            nation {snapshot = 'tpch_snapshot'} n1 ,
            nation {snapshot = 'tpch_snapshot'} n2 
        where
            s_suppkey = l_suppkey
            and o_orderkey = l_orderkey
            and c_custkey = o_custkey
            and s_nationkey = n1.n_nationkey
            and c_nationkey = n2.n_nationkey
            and (
                (n1.n_name = 'FRANCE' and n2.n_name = 'ARGENTINA')
                or (n1.n_name = 'ARGENTINA' and n2.n_name = 'FRANCE')
            )
            and l_shipdate between date '1995-01-01' and date '1996-12-31'
    ) as shipping
group by
    supp_nation,
    cust_nation,
    l_year
order by
    supp_nation,
    cust_nation,
    l_year
;

select
    supp_nation,
    cust_nation,
    l_year,
    sum(volume) as revenue
from
    (
        select
            n1.n_name as supp_nation,
            n2.n_name as cust_nation,
            extract(year from l_shipdate) as l_year,
            l_extendedprice * (1 - l_discount) as volume
        from
            supplier {snapshot = 'tpch_snapshot'},
            lineitem {snapshot = 'tpch_snapshot'},
            orders {snapshot = 'tpch_snapshot'},
            customer {snapshot = 'tpch_snapshot'},
            nation {snapshot = 'tpch_snapshot'} n1 ,
            nation {snapshot = 'tpch_snapshot'} n2 
        where
            s_suppkey = l_suppkey
            and o_orderkey = l_orderkey
            and c_custkey = o_custkey
            and s_nationkey = n1.n_nationkey
            and c_nationkey = n2.n_nationkey
            and (
                (n1.n_name = 'FRANCE' and n2.n_name = 'ARGENTINA')
                or (n1.n_name = 'ARGENTINA' and n2.n_name = 'FRANCE')
            )
            and l_shipdate between date '1995-01-01' and date '1996-12-31'
    ) as shipping
group by
    supp_nation,
    cust_nation,
    l_year
order by
    supp_nation,
    cust_nation,
    l_year
;

select
    supp_nation,
    cust_nation,
    l_year,
    sum(volume) as revenue
from
    (
        select
            n1.n_name as supp_nation,
            n2.n_name as cust_nation,
            extract(year from l_shipdate) as l_year,
            l_extendedprice * (1 - l_discount) as volume
        from
            supplier {snapshot = 'tpch_snapshot'},
            lineitem {snapshot = 'tpch_snapshot'},
            orders {snapshot = 'tpch_snapshot'},
            customer {snapshot = 'tpch_snapshot'},
            nation {snapshot = 'tpch_snapshot'} n1 ,
            nation {snapshot = 'tpch_snapshot'} n2 
        where
            s_suppkey = l_suppkey
            and o_orderkey = l_orderkey
            and c_custkey = o_custkey
            and s_nationkey = n1.n_nationkey
            and c_nationkey = n2.n_nationkey
            and (
                (n1.n_name = 'FRANCE' and n2.n_name = 'ARGENTINA')
                or (n1.n_name = 'ARGENTINA' and n2.n_name = 'FRANCE')
            )
            and l_shipdate between date '1995-01-01' and date '1996-12-31'
    ) as shipping
group by
    supp_nation,
    cust_nation,
    l_year
order by
    supp_nation,
    cust_nation,
    l_year
;
-- tpch q8 --
select
    o_year,
    (sum(case
        when nation = 'ARGENTINA' then volume
        else 0
    end) / sum(volume)) as mkt_share
from
    (
        select
            extract(year from o_orderdate) as o_year,
            l_extendedprice * (1 - l_discount) as volume,
            n2.n_name as nation
        from
            part {snapshot = 'tpch_snapshot'},
            supplier {snapshot = 'tpch_snapshot'},
            lineitem {snapshot = 'tpch_snapshot'},
            orders {snapshot = 'tpch_snapshot'},
            customer {snapshot = 'tpch_snapshot'},
            nation {snapshot = 'tpch_snapshot'} n1,
            nation {snapshot = 'tpch_snapshot'} n2,
            region {snapshot = 'tpch_snapshot'}
        where
            p_partkey = l_partkey
            and s_suppkey = l_suppkey
            and l_orderkey = o_orderkey
            and o_custkey = c_custkey
            and c_nationkey = n1.n_nationkey
            and n1.n_regionkey = r_regionkey
            and r_name = 'AMERICA'
            and s_nationkey = n2.n_nationkey
            and o_orderdate between date '1995-01-01' and date '1996-12-31'
            and p_type = 'ECONOMY BURNISHED TIN'
    ) as all_nations
group by
    o_year
order by
    o_year
;

select
    o_year,
    (sum(case
        when nation = 'ARGENTINA' then volume
        else 0
    end) / sum(volume)) as mkt_share
from
    (
        select
            extract(year from o_orderdate) as o_year,
            l_extendedprice * (1 - l_discount) as volume,
            n2.n_name as nation
        from
            part {snapshot = 'tpch_snapshot'},
            supplier {snapshot = 'tpch_snapshot'},
            lineitem {snapshot = 'tpch_snapshot'},
            orders {snapshot = 'tpch_snapshot'},
            customer {snapshot = 'tpch_snapshot'},
            nation {snapshot = 'tpch_snapshot'} n1,
            nation {snapshot = 'tpch_snapshot'} n2,
            region {snapshot = 'tpch_snapshot'}
        where
            p_partkey = l_partkey
            and s_suppkey = l_suppkey
            and l_orderkey = o_orderkey
            and o_custkey = c_custkey
            and c_nationkey = n1.n_nationkey
            and n1.n_regionkey = r_regionkey
            and r_name = 'AMERICA'
            and s_nationkey = n2.n_nationkey
            and o_orderdate between date '1995-01-01' and date '1996-12-31'
            and p_type = 'ECONOMY BURNISHED TIN'
    ) as all_nations
group by
    o_year
order by
    o_year
;

select
    o_year,
    (sum(case
        when nation = 'ARGENTINA' then volume
        else 0
    end) / sum(volume)) as mkt_share
from
    (
        select
            extract(year from o_orderdate) as o_year,
            l_extendedprice * (1 - l_discount) as volume,
            n2.n_name as nation
        from
            part {snapshot = 'tpch_snapshot'},
            supplier {snapshot = 'tpch_snapshot'},
            lineitem {snapshot = 'tpch_snapshot'},
            orders {snapshot = 'tpch_snapshot'},
            customer {snapshot = 'tpch_snapshot'},
            nation {snapshot = 'tpch_snapshot'} n1,
            nation {snapshot = 'tpch_snapshot'} n2,
            region {snapshot = 'tpch_snapshot'}
        where
            p_partkey = l_partkey
            and s_suppkey = l_suppkey
            and l_orderkey = o_orderkey
            and o_custkey = c_custkey
            and c_nationkey = n1.n_nationkey
            and n1.n_regionkey = r_regionkey
            and r_name = 'AMERICA'
            and s_nationkey = n2.n_nationkey
            and o_orderdate between date '1995-01-01' and date '1996-12-31'
            and p_type = 'ECONOMY BURNISHED TIN'
    ) as all_nations
group by
    o_year
order by
    o_year
;

-- tpch q9 --
select
    nation,
    o_year,
    sum(amount) as sum_profit
from
    (
        select
            n_name as nation,
            extract(year from o_orderdate) as o_year,
            l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
        from
            part {snapshot = 'tpch_snapshot'},
            supplier {snapshot = 'tpch_snapshot'},
            lineitem {snapshot = 'tpch_snapshot'},
            partsupp {snapshot = 'tpch_snapshot'},
            orders {snapshot = 'tpch_snapshot'},
            nation {snapshot = 'tpch_snapshot'}
        where
            s_suppkey = l_suppkey
            and ps_suppkey = l_suppkey
            and ps_partkey = l_partkey
            and p_partkey = l_partkey
            and o_orderkey = l_orderkey
            and s_nationkey = n_nationkey
            and p_name like '%pink%'
    ) as profit
group by
    nation,
    o_year
order by
    nation,
    o_year desc
;

select
    nation,
    o_year,
    sum(amount) as sum_profit
from
    (
        select
            n_name as nation,
            extract(year from o_orderdate) as o_year,
            l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
        from
            part {snapshot = 'tpch_snapshot'},
            supplier {snapshot = 'tpch_snapshot'},
            lineitem {snapshot = 'tpch_snapshot'},
            partsupp {snapshot = 'tpch_snapshot'},
            orders {snapshot = 'tpch_snapshot'},
            nation {snapshot = 'tpch_snapshot'}
        where
            s_suppkey = l_suppkey
            and ps_suppkey = l_suppkey
            and ps_partkey = l_partkey
            and p_partkey = l_partkey
            and o_orderkey = l_orderkey
            and s_nationkey = n_nationkey
            and p_name like '%pink%'
    ) as profit
group by
    nation,
    o_year
order by
    nation,
    o_year desc
;

select
    nation,
    o_year,
    sum(amount) as sum_profit
from
    (
        select
            n_name as nation,
            extract(year from o_orderdate) as o_year,
            l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
        from
            part {snapshot = 'tpch_snapshot'},
            supplier {snapshot = 'tpch_snapshot'},
            lineitem {snapshot = 'tpch_snapshot'},
            partsupp {snapshot = 'tpch_snapshot'},
            orders {snapshot = 'tpch_snapshot'},
            nation {snapshot = 'tpch_snapshot'}
        where
            s_suppkey = l_suppkey
            and ps_suppkey = l_suppkey
            and ps_partkey = l_partkey
            and p_partkey = l_partkey
            and o_orderkey = l_orderkey
            and s_nationkey = n_nationkey
            and p_name like '%pink%'
    ) as profit
group by
    nation,
    o_year
order by
    nation,
    o_year desc
;

-- tpch q10 --
select
	c_custkey,
	c_name,
	sum(l_extendedprice * (1 - l_discount)) as revenue,
	c_acctbal,
	n_name,
	c_address,
	c_phone,
	c_comment
from
	customer {snapshot = 'tpch_snapshot'},
	orders {snapshot = 'tpch_snapshot'},
	lineitem {snapshot = 'tpch_snapshot'},
	nation {snapshot = 'tpch_snapshot'}
where
	c_custkey = o_custkey
	and l_orderkey = o_orderkey
	and o_orderdate >= date '1993-03-01'
	and o_orderdate < date '1993-03-01' + interval '3' month
	and l_returnflag = 'R'
	and c_nationkey = n_nationkey
group by
	c_custkey,
	c_name,
	c_acctbal,
	c_phone,
	n_name,
	c_address,
	c_comment
order by
	revenue desc
limit 20
;

select
	c_custkey,
	c_name,
	sum(l_extendedprice * (1 - l_discount)) as revenue,
	c_acctbal,
	n_name,
	c_address,
	c_phone,
	c_comment
from
	customer {snapshot = 'tpch_snapshot'},
	orders {snapshot = 'tpch_snapshot'},
	lineitem {snapshot = 'tpch_snapshot'},
	nation {snapshot = 'tpch_snapshot'}
where
	c_custkey = o_custkey
	and l_orderkey = o_orderkey
	and o_orderdate >= date '1993-03-01'
	and o_orderdate < date '1993-03-01' + interval '3' month
	and l_returnflag = 'R'
	and c_nationkey = n_nationkey
group by
	c_custkey,
	c_name,
	c_acctbal,
	c_phone,
	n_name,
	c_address,
	c_comment
order by
	revenue desc
limit 20
;


select
	c_custkey,
	c_name,
	sum(l_extendedprice * (1 - l_discount)) as revenue,
	c_acctbal,
	n_name,
	c_address,
	c_phone,
	c_comment
from
	customer {snapshot = 'tpch_snapshot'},
	orders {snapshot = 'tpch_snapshot'},
	lineitem {snapshot = 'tpch_snapshot'},
	nation {snapshot = 'tpch_snapshot'}
where
	c_custkey = o_custkey
	and l_orderkey = o_orderkey
	and o_orderdate >= date '1993-03-01'
	and o_orderdate < date '1993-03-01' + interval '3' month
	and l_returnflag = 'R'
	and c_nationkey = n_nationkey
group by
	c_custkey,
	c_name,
	c_acctbal,
	c_phone,
	n_name,
	c_address,
	c_comment
order by
	revenue desc
limit 20
;

-- tpch q11 --
select
    ps_partkey,
    sum(ps_supplycost * ps_availqty) as value
from
    partsupp {snapshot = 'tpch_snapshot'},
    supplier {snapshot = 'tpch_snapshot'},
    nation {snapshot = 'tpch_snapshot'}
where
    ps_suppkey = s_suppkey
    and s_nationkey = n_nationkey
    and n_name = 'JAPAN'
group by
    ps_partkey having
        sum(ps_supplycost * ps_availqty) > (
        select
            sum(ps_supplycost * ps_availqty) * 0.0001000000
                from
                partsupp {snapshot = 'tpch_snapshot'},
                supplier {snapshot = 'tpch_snapshot'},
                nation {snapshot = 'tpch_snapshot'}
            where
                ps_suppkey = s_suppkey
                and s_nationkey = n_nationkey
                and n_name = 'JAPAN'
        )
order by
    value desc
;

select
    ps_partkey,
    sum(ps_supplycost * ps_availqty) as value
from
    partsupp {snapshot = 'tpch_snapshot'},
    supplier {snapshot = 'tpch_snapshot'},
    nation {snapshot = 'tpch_snapshot'}
where
    ps_suppkey = s_suppkey
    and s_nationkey = n_nationkey
    and n_name = 'JAPAN'
group by
    ps_partkey having
        sum(ps_supplycost * ps_availqty) > (
        select
            sum(ps_supplycost * ps_availqty) * 0.0001000000
                from
                partsupp {snapshot = 'tpch_snapshot'},
                supplier {snapshot = 'tpch_snapshot'},
                nation {snapshot = 'tpch_snapshot'}
            where
                ps_suppkey = s_suppkey
                and s_nationkey = n_nationkey
                and n_name = 'JAPAN'
        )
order by
    value desc
;

select
    ps_partkey,
    sum(ps_supplycost * ps_availqty) as value
from
    partsupp {snapshot = 'tpch_snapshot'},
    supplier {snapshot = 'tpch_snapshot'},
    nation {snapshot = 'tpch_snapshot'}
where
    ps_suppkey = s_suppkey
    and s_nationkey = n_nationkey
    and n_name = 'JAPAN'
group by
    ps_partkey having
        sum(ps_supplycost * ps_availqty) > (
        select
            sum(ps_supplycost * ps_availqty) * 0.0001000000
                from
                partsupp {snapshot = 'tpch_snapshot'},
                supplier {snapshot = 'tpch_snapshot'},
                nation {snapshot = 'tpch_snapshot'}
            where
                ps_suppkey = s_suppkey
                and s_nationkey = n_nationkey
                and n_name = 'JAPAN'
        )
order by
    value desc
;

-- tpch q12 --
select
        l_shipmode,
        sum(case
                when o_orderpriority = '1-URGENT'
                        or o_orderpriority = '2-HIGH'
                        then 1
                else 0
        end) as high_line_count,
        sum(case
                when o_orderpriority <> '1-URGENT'
                        and o_orderpriority <> '2-HIGH'
                        then 1
                else 0
        end) as low_line_count
from
        orders {snapshot = 'tpch_snapshot'},
        lineitem {snapshot = 'tpch_snapshot'}
where
        o_orderkey = l_orderkey
        and l_shipmode in ('FOB', 'TRUCK')
        and l_commitdate < l_receiptdate
        and l_shipdate < l_commitdate
        and l_receiptdate >= date '1996-01-01'
        and l_receiptdate < date '1996-01-01' + interval '1' year
group by
        l_shipmode
order by
        l_shipmode
;

select
        l_shipmode,
        sum(case
                when o_orderpriority = '1-URGENT'
                        or o_orderpriority = '2-HIGH'
                        then 1
                else 0
        end) as high_line_count,
        sum(case
                when o_orderpriority <> '1-URGENT'
                        and o_orderpriority <> '2-HIGH'
                        then 1
                else 0
        end) as low_line_count
from
        orders {snapshot = 'tpch_snapshot'},
        lineitem {snapshot = 'tpch_snapshot'}
where
        o_orderkey = l_orderkey
        and l_shipmode in ('FOB', 'TRUCK')
        and l_commitdate < l_receiptdate
        and l_shipdate < l_commitdate
        and l_receiptdate >= date '1996-01-01'
        and l_receiptdate < date '1996-01-01' + interval '1' year
group by
        l_shipmode
order by
        l_shipmode
;

select
        l_shipmode,
        sum(case
                when o_orderpriority = '1-URGENT'
                        or o_orderpriority = '2-HIGH'
                        then 1
                else 0
        end) as high_line_count,
        sum(case
                when o_orderpriority <> '1-URGENT'
                        and o_orderpriority <> '2-HIGH'
                        then 1
                else 0
        end) as low_line_count
from
        orders {snapshot = 'tpch_snapshot'},
        lineitem {snapshot = 'tpch_snapshot'}
where
        o_orderkey = l_orderkey
        and l_shipmode in ('FOB', 'TRUCK')
        and l_commitdate < l_receiptdate
        and l_shipdate < l_commitdate
        and l_receiptdate >= date '1996-01-01'
        and l_receiptdate < date '1996-01-01' + interval '1' year
group by
        l_shipmode
order by
        l_shipmode
;

-- tpch q13 --
select
	c_count,
	count(*) as custdist
from
	(
		select
			c_custkey,
			count(o_orderkey)
		from
			customer {snapshot = 'tpch_snapshot'} left outer join orders {snapshot = 'tpch_snapshot'} on
				c_custkey = o_custkey
				and o_comment not like '%pending%accounts%'
		group by
			c_custkey
	) as c_orders (c_custkey, c_count)
group by
	c_count
order by
	custdist desc,
	c_count desc
;

select
	c_count,
	count(*) as custdist
from
	(
		select
			c_custkey,
			count(o_orderkey)
		from
			customer {snapshot = 'tpch_snapshot'} left outer join orders {snapshot = 'tpch_snapshot'} on
				c_custkey = o_custkey
				and o_comment not like '%pending%accounts%'
		group by
			c_custkey
	) as c_orders (c_custkey, c_count)
group by
	c_count
order by
	custdist desc,
	c_count desc
;

select
	c_count,
	count(*) as custdist
from
	(
		select
			c_custkey,
			count(o_orderkey)
		from
			customer {snapshot = 'tpch_snapshot'} left outer join orders {snapshot = 'tpch_snapshot'} on
				c_custkey = o_custkey
				and o_comment not like '%pending%accounts%'
		group by
			c_custkey
	) as c_orders (c_custkey, c_count)
group by
	c_count
order by
	custdist desc,
	c_count desc
;

-- tpch q14 --
select
	100.00 * sum(case
		when p_type like 'PROMO%'
			then l_extendedprice * (1 - l_discount)
		else 0
	end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue
from
	lineitem {snapshot = 'tpch_snapshot'},
	part {snapshot = 'tpch_snapshot'}
where
	l_partkey = p_partkey
	and l_shipdate >= date '1996-04-01'
	and l_shipdate < date '1996-04-01' + interval '1' month;

select
	100.00 * sum(case
		when p_type like 'PROMO%'
			then l_extendedprice * (1 - l_discount)
		else 0
	end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue
from
	lineitem {snapshot = 'tpch_snapshot'},
	part {snapshot = 'tpch_snapshot'}
where
	l_partkey = p_partkey
	and l_shipdate >= date '1996-04-01'
	and l_shipdate < date '1996-04-01' + interval '1' month;

select
	100.00 * sum(case
		when p_type like 'PROMO%'
			then l_extendedprice * (1 - l_discount)
		else 0
	end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue
from
	lineitem {snapshot = 'tpch_snapshot'},
	part {snapshot = 'tpch_snapshot'}
where
	l_partkey = p_partkey
	and l_shipdate >= date '1996-04-01'
	and l_shipdate < date '1996-04-01' + interval '1' month;

-- tpch q15 --
with q15_revenue0 as (
    select
        l_suppkey as supplier_no,
        sum(l_extendedprice * (1 - l_discount)) as total_revenue
    from
        lineitem {snapshot = 'tpch_snapshot'}
    where
        l_shipdate >= date '1995-12-01'
        and l_shipdate < date '1995-12-01' + interval '3' month
    group by
        l_suppkey
    )
select
    s_suppkey,
    s_name,
    s_address,
    s_phone,
    total_revenue
from
    supplier {snapshot = 'tpch_snapshot'},
    q15_revenue0 {snapshot = 'tpch_snapshot'}
where
    s_suppkey = supplier_no
    and total_revenue = (
        select
            max(total_revenue)
        from
            q15_revenue0 {snapshot = 'tpch_snapshot'}
    )
order by
    s_suppkey
;

with q15_revenue0 as (
    select
        l_suppkey as supplier_no,
        sum(l_extendedprice * (1 - l_discount)) as total_revenue
    from
        lineitem {snapshot = 'tpch_snapshot'}
    where
        l_shipdate >= date '1995-12-01'
        and l_shipdate < date '1995-12-01' + interval '3' month
    group by
        l_suppkey
    )
select
    s_suppkey,
    s_name,
    s_address,
    s_phone,
    total_revenue
from
    supplier {snapshot = 'tpch_snapshot'},
    q15_revenue0 {snapshot = 'tpch_snapshot'}
where
    s_suppkey = supplier_no
    and total_revenue = (
        select
            max(total_revenue)
        from
            q15_revenue0 {snapshot = 'tpch_snapshot'}
    )
order by
    s_suppkey
;

with q15_revenue0 as (
    select
        l_suppkey as supplier_no,
        sum(l_extendedprice * (1 - l_discount)) as total_revenue
    from
        lineitem {snapshot = 'tpch_snapshot'}
    where
        l_shipdate >= date '1995-12-01'
        and l_shipdate < date '1995-12-01' + interval '3' month
    group by
        l_suppkey
    )
select
    s_suppkey,
    s_name,
    s_address,
    s_phone,
    total_revenue
from
    supplier {snapshot = 'tpch_snapshot'},
    q15_revenue0 {snapshot = 'tpch_snapshot'}
where
    s_suppkey = supplier_no
    and total_revenue = (
        select
            max(total_revenue)
        from
            q15_revenue0 {snapshot = 'tpch_snapshot'}
    )
order by
    s_suppkey
;

-- tpch q16 --
select
    p_brand,
    p_type,
    p_size,
    count(distinct ps_suppkey) as supplier_cnt
from
    partsupp {snapshot = 'tpch_snapshot'},
    part {snapshot = 'tpch_snapshot'}
where
    p_partkey = ps_partkey
    and p_brand <> 'Brand#35'
    and p_type not like 'ECONOMY BURNISHED%'
    and p_size in (14, 7, 21, 24, 35, 33, 2, 20)
    and ps_suppkey not in (
        select
            s_suppkey
        from
            supplier {snapshot = 'tpch_snapshot'}
        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
;

select
    p_brand,
    p_type,
    p_size,
    count(distinct ps_suppkey) as supplier_cnt
from
    partsupp {snapshot = 'tpch_snapshot'},
    part {snapshot = 'tpch_snapshot'}
where
    p_partkey = ps_partkey
    and p_brand <> 'Brand#35'
    and p_type not like 'ECONOMY BURNISHED%'
    and p_size in (14, 7, 21, 24, 35, 33, 2, 20)
    and ps_suppkey not in (
        select
            s_suppkey
        from
            supplier {snapshot = 'tpch_snapshot'}
        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
;

select
    p_brand,
    p_type,
    p_size,
    count(distinct ps_suppkey) as supplier_cnt
from
    partsupp {snapshot = 'tpch_snapshot'},
    part {snapshot = 'tpch_snapshot'}
where
    p_partkey = ps_partkey
    and p_brand <> 'Brand#35'
    and p_type not like 'ECONOMY BURNISHED%'
    and p_size in (14, 7, 21, 24, 35, 33, 2, 20)
    and ps_suppkey not in (
        select
            s_suppkey
        from
            supplier {snapshot = 'tpch_snapshot'}
        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
;

-- tpch q17 --
select
    sum(l_extendedprice) / 7.0 as avg_yearly
from
    lineitem {snapshot = 'tpch_snapshot'},
    part {snapshot = 'tpch_snapshot'}
where
    p_partkey = l_partkey
    and p_brand = 'Brand#54'
    and p_container = 'LG BAG'
    and l_quantity < (
        select
            0.2 * avg(l_quantity)
        from
            lineitem {snapshot = 'tpch_snapshot'}
        where
            l_partkey = p_partkey
    );

select
    sum(l_extendedprice) / 7.0 as avg_yearly
from
    lineitem {snapshot = 'tpch_snapshot'},
    part {snapshot = 'tpch_snapshot'}
where
    p_partkey = l_partkey
    and p_brand = 'Brand#54'
    and p_container = 'LG BAG'
    and l_quantity < (
        select
            0.2 * avg(l_quantity)
        from
            lineitem {snapshot = 'tpch_snapshot'}
        where
            l_partkey = p_partkey
    );

select
    sum(l_extendedprice) / 7.0 as avg_yearly
from
    lineitem {snapshot = 'tpch_snapshot'},
    part {snapshot = 'tpch_snapshot'}
where
    p_partkey = l_partkey
    and p_brand = 'Brand#54'
    and p_container = 'LG BAG'
    and l_quantity < (
        select
            0.2 * avg(l_quantity)
        from
            lineitem {snapshot = 'tpch_snapshot'}
        where
            l_partkey = p_partkey
    );

-- tpch q18 --
select
    c_name,
    c_custkey,
    o_orderkey,
    o_orderdate,
    o_totalprice,
    sum(l_quantity)
from
    customer {snapshot = 'tpch_snapshot'},
    orders {snapshot = 'tpch_snapshot'},
    lineitem {snapshot = 'tpch_snapshot'}
where
    o_orderkey in (
        select
            l_orderkey
        from
            lineitem {snapshot = 'tpch_snapshot'}
        group by
            l_orderkey having
                sum(l_quantity) > 314
    )
    and c_custkey = o_custkey
    and o_orderkey = l_orderkey
group by
    c_name,
    c_custkey,
    o_orderkey,
    o_orderdate,
    o_totalprice
order by
    o_totalprice desc,
    o_orderdate
limit 100
;

select
    c_name,
    c_custkey,
    o_orderkey,
    o_orderdate,
    o_totalprice,
    sum(l_quantity)
from
    customer {snapshot = 'tpch_snapshot'},
    orders {snapshot = 'tpch_snapshot'},
    lineitem {snapshot = 'tpch_snapshot'}
where
    o_orderkey in (
        select
            l_orderkey
        from
            lineitem {snapshot = 'tpch_snapshot'}
        group by
            l_orderkey having
                sum(l_quantity) > 314
    )
    and c_custkey = o_custkey
    and o_orderkey = l_orderkey
group by
    c_name,
    c_custkey,
    o_orderkey,
    o_orderdate,
    o_totalprice
order by
    o_totalprice desc,
    o_orderdate
limit 100
;

select
    c_name,
    c_custkey,
    o_orderkey,
    o_orderdate,
    o_totalprice,
    sum(l_quantity)
from
    customer {snapshot = 'tpch_snapshot'},
    orders {snapshot = 'tpch_snapshot'},
    lineitem {snapshot = 'tpch_snapshot'}
where
    o_orderkey in (
        select
            l_orderkey
        from
            lineitem {snapshot = 'tpch_snapshot'}
        group by
            l_orderkey having
                sum(l_quantity) > 314
    )
    and c_custkey = o_custkey
    and o_orderkey = l_orderkey
group by
    c_name,
    c_custkey,
    o_orderkey,
    o_orderdate,
    o_totalprice
order by
    o_totalprice desc,
    o_orderdate
limit 100
;

-- tpch q19 --
select
    sum(l_extendedprice* (1 - l_discount)) as revenue
from
    lineitem {snapshot = 'tpch_snapshot'},
    part {snapshot = 'tpch_snapshot'}
where
    (
        p_partkey = l_partkey
        and p_brand = 'Brand#23'
        and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
        and l_quantity >= 5 and l_quantity <= 5 + 10
        and p_size between 1 and 5
        and l_shipmode in ('AIR', 'AIR REG')
        and l_shipinstruct = 'DELIVER IN PERSON'
    )
    or
    (
        p_partkey = l_partkey
        and p_brand = 'Brand#15'
        and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
        and l_quantity >= 14 and l_quantity <= 14 + 10
        and p_size between 1 and 10
        and l_shipmode in ('AIR', 'AIR REG')
        and l_shipinstruct = 'DELIVER IN PERSON'
    )
    or
    (
        p_partkey = l_partkey
        and p_brand = 'Brand#44'
        and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
        and l_quantity >= 28 and l_quantity <= 28 + 10
        and p_size between 1 and 15
        and l_shipmode in ('AIR', 'AIR REG')
        and l_shipinstruct = 'DELIVER IN PERSON'
    );

select
    sum(l_extendedprice* (1 - l_discount)) as revenue
from
    lineitem {snapshot = 'tpch_snapshot'},
    part {snapshot = 'tpch_snapshot'}
where
    (
        p_partkey = l_partkey
        and p_brand = 'Brand#23'
        and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
        and l_quantity >= 5 and l_quantity <= 5 + 10
        and p_size between 1 and 5
        and l_shipmode in ('AIR', 'AIR REG')
        and l_shipinstruct = 'DELIVER IN PERSON'
    )
    or
    (
        p_partkey = l_partkey
        and p_brand = 'Brand#15'
        and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
        and l_quantity >= 14 and l_quantity <= 14 + 10
        and p_size between 1 and 10
        and l_shipmode in ('AIR', 'AIR REG')
        and l_shipinstruct = 'DELIVER IN PERSON'
    )
    or
    (
        p_partkey = l_partkey
        and p_brand = 'Brand#44'
        and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
        and l_quantity >= 28 and l_quantity <= 28 + 10
        and p_size between 1 and 15
        and l_shipmode in ('AIR', 'AIR REG')
        and l_shipinstruct = 'DELIVER IN PERSON'
    );

select
    sum(l_extendedprice* (1 - l_discount)) as revenue
from
    lineitem {snapshot = 'tpch_snapshot'},
    part {snapshot = 'tpch_snapshot'}
where
    (
        p_partkey = l_partkey
        and p_brand = 'Brand#23'
        and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
        and l_quantity >= 5 and l_quantity <= 5 + 10
        and p_size between 1 and 5
        and l_shipmode in ('AIR', 'AIR REG')
        and l_shipinstruct = 'DELIVER IN PERSON'
    )
    or
    (
        p_partkey = l_partkey
        and p_brand = 'Brand#15'
        and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
        and l_quantity >= 14 and l_quantity <= 14 + 10
        and p_size between 1 and 10
        and l_shipmode in ('AIR', 'AIR REG')
        and l_shipinstruct = 'DELIVER IN PERSON'
    )
    or
    (
        p_partkey = l_partkey
        and p_brand = 'Brand#44'
        and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
        and l_quantity >= 28 and l_quantity <= 28 + 10
        and p_size between 1 and 15
        and l_shipmode in ('AIR', 'AIR REG')
        and l_shipinstruct = 'DELIVER IN PERSON'
    );

-- tpch q20 --
select
    s_name,
    s_address
from
    supplier {snapshot = 'tpch_snapshot'},
    nation {snapshot = 'tpch_snapshot'}
where
    s_suppkey in (
        select
            ps_suppkey
        from
            partsupp {snapshot = 'tpch_snapshot'}
        where
            ps_partkey in (
                select
                    p_partkey
                from
                    part {snapshot = 'tpch_snapshot'}
                where
                    p_name like 'lime%'
            )
            and ps_availqty > (
                select
                    0.5 * sum(l_quantity)
                from
                    lineitem {snapshot = 'tpch_snapshot'}
                where
                    l_partkey = ps_partkey
                    and l_suppkey = ps_suppkey
                    and l_shipdate >= date '1993-01-01'
                    and l_shipdate < date '1993-01-01' + interval '1' year
            )
    )
    and s_nationkey = n_nationkey
    and n_name = 'VIETNAM'
order by s_name
;

select
    s_name,
    s_address
from
    supplier {snapshot = 'tpch_snapshot'},
    nation {snapshot = 'tpch_snapshot'}
where
    s_suppkey in (
        select
            ps_suppkey
        from
            partsupp {snapshot = 'tpch_snapshot'}
        where
            ps_partkey in (
                select
                    p_partkey
                from
                    part {snapshot = 'tpch_snapshot'}
                where
                    p_name like 'lime%'
            )
            and ps_availqty > (
                select
                    0.5 * sum(l_quantity)
                from
                    lineitem {snapshot = 'tpch_snapshot'}
                where
                    l_partkey = ps_partkey
                    and l_suppkey = ps_suppkey
                    and l_shipdate >= date '1993-01-01'
                    and l_shipdate < date '1993-01-01' + interval '1' year
            )
    )
    and s_nationkey = n_nationkey
    and n_name = 'VIETNAM'
order by s_name
;

select
    s_name,
    s_address
from
    supplier {snapshot = 'tpch_snapshot'},
    nation {snapshot = 'tpch_snapshot'}
where
    s_suppkey in (
        select
            ps_suppkey
        from
            partsupp {snapshot = 'tpch_snapshot'}
        where
            ps_partkey in (
                select
                    p_partkey
                from
                    part {snapshot = 'tpch_snapshot'}
                where
                    p_name like 'lime%'
            )
            and ps_availqty > (
                select
                    0.5 * sum(l_quantity)
                from
                    lineitem {snapshot = 'tpch_snapshot'}
                where
                    l_partkey = ps_partkey
                    and l_suppkey = ps_suppkey
                    and l_shipdate >= date '1993-01-01'
                    and l_shipdate < date '1993-01-01' + interval '1' year
            )
    )
    and s_nationkey = n_nationkey
    and n_name = 'VIETNAM'
order by s_name
;

-- tpch q21 --
select
    s_name,
    count(*) as numwait
from
    supplier {snapshot = 'tpch_snapshot'},
    lineitem l1 {snapshot = 'tpch_snapshot'},
    orders {snapshot = 'tpch_snapshot'},
    nation {snapshot = 'tpch_snapshot'}
where
    s_suppkey = l1.l_suppkey
    and o_orderkey = l1.l_orderkey
    and o_orderstatus = 'F'
    and l1.l_receiptdate > l1.l_commitdate
    and exists (
        select
            *
        from
            lineitem l2 {snapshot = 'tpch_snapshot'}
        where
            l2.l_orderkey = l1.l_orderkey
            and l2.l_suppkey <> l1.l_suppkey
    )
    and not exists (
        select
            *
        from
            lineitem l3 {snapshot = 'tpch_snapshot'}
        where
            l3.l_orderkey = l1.l_orderkey
            and l3.l_suppkey <> l1.l_suppkey
            and l3.l_receiptdate > l3.l_commitdate
    )
    and s_nationkey = n_nationkey
    and n_name = 'BRAZIL'
group by
    s_name
order by
    numwait desc,
    s_name
limit 100
;

select
    s_name,
    count(*) as numwait
from
    supplier {snapshot = 'tpch_snapshot'},
    lineitem l1 {snapshot = 'tpch_snapshot'},
    orders {snapshot = 'tpch_snapshot'},
    nation {snapshot = 'tpch_snapshot'}
where
    s_suppkey = l1.l_suppkey
    and o_orderkey = l1.l_orderkey
    and o_orderstatus = 'F'
    and l1.l_receiptdate > l1.l_commitdate
    and exists (
        select
            *
        from
            lineitem l2 {snapshot = 'tpch_snapshot'}
        where
            l2.l_orderkey = l1.l_orderkey
            and l2.l_suppkey <> l1.l_suppkey
    )
    and not exists (
        select
            *
        from
            lineitem l3 {snapshot = 'tpch_snapshot'}
        where
            l3.l_orderkey = l1.l_orderkey
            and l3.l_suppkey <> l1.l_suppkey
            and l3.l_receiptdate > l3.l_commitdate
    )
    and s_nationkey = n_nationkey
    and n_name = 'BRAZIL'
group by
    s_name
order by
    numwait desc,
    s_name
limit 100
;

select
    s_name,
    count(*) as numwait
from
    supplier {snapshot = 'tpch_snapshot'},
    lineitem l1 {snapshot = 'tpch_snapshot'},
    orders {snapshot = 'tpch_snapshot'},
    nation {snapshot = 'tpch_snapshot'}
where
    s_suppkey = l1.l_suppkey
    and o_orderkey = l1.l_orderkey
    and o_orderstatus = 'F'
    and l1.l_receiptdate > l1.l_commitdate
    and exists (
        select
            *
        from
            lineitem l2 {snapshot = 'tpch_snapshot'}
        where
            l2.l_orderkey = l1.l_orderkey
            and l2.l_suppkey <> l1.l_suppkey
    )
    and not exists (
        select
            *
        from
            lineitem l3 {snapshot = 'tpch_snapshot'}
        where
            l3.l_orderkey = l1.l_orderkey
            and l3.l_suppkey <> l1.l_suppkey
            and l3.l_receiptdate > l3.l_commitdate
    )
    and s_nationkey = n_nationkey
    and n_name = 'BRAZIL'
group by
    s_name
order by
    numwait desc,
    s_name
limit 100
;

drop database if exists TPCH;
drop snapshot tpch_snapshot;
drop snapshot tpch_cluster;