-- Sample of TPC-H queries with EXPLAIN ANALYZE prefix. All in one file.
-- Rewritten variants of some queries.

-- Q1
-- -----------------------------------------------------------------------------

SET enable_nestloop = 'off'; -- Q21 needs it

\echo `date` "################ - Q01 - ################"
explain analyze 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
where l_shipdate <= date '1998-12-01' - interval '98' day
group by
	l_returnflag,
	l_linestatus
order by
	l_returnflag,
	l_linestatus;

-- Q2
-- -----------------------------------------------------------------------------

\echo `date` "################ - Q02 - ################ (rewritten)"
explain analyze WITH pre AS (
	select ps_partkey AS x1, min(ps_supplycost) AS x2 from partsupp, supplier, nation, region
		where s_suppkey = ps_suppkey and s_nationkey = n_nationkey and n_regionkey = r_regionkey and r_name = 'AFRICA'
		GROUP BY (ps_partkey)
)
select s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address, s_phone, s_comment
from
	part, supplier, partsupp, nation, region, pre
where p_partkey = ps_partkey
	and s_suppkey = ps_suppkey
	and p_size = 7
	and p_type like '%STEEL'
	and s_nationkey = n_nationkey
	and n_regionkey = r_regionkey
	and r_name = 'AFRICA'
	and ps_supplycost = pre.x2
	and p_partkey = pre.x1
order by s_acctbal desc, n_name, s_name, p_partkey limit 100;

-- Q3
-- -----------------------------------------------------------------------------

\echo `date` "################ - Q03 - ################"
explain analyze verbose select l_orderkey,
	sum(l_extendedprice * (1 - l_discount)) as revenue,
	o_orderdate,
	o_shippriority
from customer, orders, lineitem
where c_mktsegment = 'HOUSEHOLD'
	and c_custkey = o_custkey
	and l_orderkey = o_orderkey
	and o_orderdate < date '1995-03-05'
	and l_shipdate > date '1995-03-05'
group by l_orderkey, o_orderdate, o_shippriority
order by revenue desc, o_orderdate
limit 10;

-- Q4
-- -----------------------------------------------------------------------------

\echo `date` "################ - Q04 - ################"
explain analyze select
	o_orderpriority,
	count(*) as order_count
from orders
where o_orderdate >= date '1993-09-01'
	and o_orderdate < date '1993-09-01' + interval '3' month
	and exists (select * from lineitem where l_orderkey = o_orderkey and l_commitdate < l_receiptdate )
group by o_orderpriority
order by o_orderpriority;

-- Q5
-- -----------------------------------------------------------------------------

\echo `date` "################ - Q05 - ################"
explain analyze select
	n_name,
	sum(l_extendedprice * (1 - l_discount)) as revenue
from customer, orders, lineitem, supplier, nation, region
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 = 'AFRICA'
	and o_orderdate >= date '1997-01-01'
	and o_orderdate < date '1997-01-01' + interval '1' year
group by n_name
order by revenue desc;

-- Q6
-- -----------------------------------------------------------------------------

\echo `date` "################ - Q06 - ################"
explain analyze select sum(l_extendedprice * l_discount) as revenue
from lineitem
where l_shipdate >= date '1997-01-01'
	and l_shipdate < date '1997-01-01' + interval '1' year
	and l_discount between 0.04 - 0.01 and 0.04 + 0.01
	and l_quantity < 25;

-- Q7
-- -----------------------------------------------------------------------------

\echo `date` "################ - Q07 - ################"
explain analyze 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, lineitem, orders, customer, nation n1, nation 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 = 'ROMANIA')
				or (n1.n_name = 'ROMANIA' 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;

-- Q8
-- -----------------------------------------------------------------------------

\echo `date` "################ - Q08 - ################"
explain analyze select o_year,
	sum(case
		when nation = 'ROMANIA' 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,
			supplier,
			lineitem,
			orders,
			customer,
			nation n1,
			nation n2,
			region
		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 = 'EUROPE'
			and s_nationkey = n2.n_nationkey
			and o_orderdate between date '1995-01-01' and date '1996-12-31'
			and p_type = 'MEDIUM POLISHED COPPER'
	) as all_nations
group by o_year
order by o_year;

-- Q9
-- -----------------------------------------------------------------------------

\echo `date` "################ - Q09 - ################"
explain analyze 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,
			supplier,
			lineitem,
			partsupp,
			orders,
			nation
		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 '%lawn%'
	) as profit
group by nation, o_year
order by nation, o_year desc;

-- Q10
-- -----------------------------------------------------------------------------

\echo `date` "################ - Q10 - ################"
explain analyze
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, orders, lineitem, nation
where c_custkey = o_custkey
	and l_orderkey = o_orderkey
	and o_orderdate >= date '1994-07-01'
	and o_orderdate < date '1994-07-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;

-- Q11
-- -----------------------------------------------------------------------------

\echo `date` "################ - Q11 - ################"
explain analyze
select ps_partkey, sum(ps_supplycost * ps_availqty) as value
from partsupp, supplier, nation
where ps_suppkey = s_suppkey and s_nationkey = n_nationkey and n_name = 'RUSSIA'
group by ps_partkey having sum(ps_supplycost * ps_availqty) > (
			select sum(ps_supplycost * ps_availqty) * 0.0001000000
			from partsupp, supplier, nation
			where ps_suppkey = s_suppkey
				and s_nationkey = n_nationkey
				and n_name = 'RUSSIA'
		) order by value desc;

-- Q12
-- -----------------------------------------------------------------------------

\echo `date` "################ - Q12 - ################"
explain analyze
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, lineitem
where o_orderkey = l_orderkey
	and l_shipmode in ('MAIL', 'AIR')
	and l_commitdate < l_receiptdate
	and l_shipdate < l_commitdate
	and l_receiptdate >= date '1993-01-01'
	and l_receiptdate < date '1993-01-01' + interval '1' year
group by l_shipmode
order by l_shipmode;

-- Q13
-- -----------------------------------------------------------------------------

\echo `date` "################ - Q13 - ################"
explain analyze
select c_count, count(*) as custdist
from (
		select
			c_custkey,
			count(o_orderkey)
		from
			customer left outer join orders on
				c_custkey = o_custkey
				and o_comment not like '%unusual%packages%'
		group by
			c_custkey
	) as c_orders (c_custkey, c_count)
group by c_count
order by custdist desc, c_count desc;

-- Q14
-- -----------------------------------------------------------------------------

\echo `date` "################ - Q14 - ################"
explain analyze
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, part
where l_partkey = p_partkey
	and l_shipdate >= date '1994-03-01'
	and l_shipdate < date '1994-03-01' + interval '1' month;

-- Q15
-- -----------------------------------------------------------------------------

\echo `date` "################ - Q15 - ################"
create view revenue0 (supplier_no, total_revenue) as
	select l_suppkey, sum(l_extendedprice * (1 - l_discount))
	from lineitem
	where l_shipdate >= date '1993-12-01'
		and l_shipdate < date '1993-12-01' + interval '3' month
	group by l_suppkey;

explain analyze
select s_suppkey, s_name, s_address, s_phone, total_revenue
from supplier, revenue0
where s_suppkey = supplier_no
	and total_revenue = (select max(total_revenue) from revenue0)
order by s_suppkey;

drop view revenue0;

-- Q16
-- -----------------------------------------------------------------------------

\echo `date` "################ - Q16 - ################"
explain analyze
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#21'
	and p_type not like 'STANDARD ANODIZED%'
	and p_size in (39, 5, 49, 29, 27, 17, 25, 36)
	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;

-- Q17
-- -----------------------------------------------------------------------------

\echo `date` "################ - Q17 - ################ (rewritten)"
explain analyze WITH pre AS (
	SELECT l_partkey AS x1, 0.2 * avg(l_quantity) AS x2 FROM lineitem
	GROUP BY (l_partkey)
)
select sum(l_extendedprice) / 7.0 as avg_yearly
from lineitem, part, pre
where p_partkey = l_partkey
	and p_brand = 'Brand#11'
	and p_container = 'JUMBO CAN'
	and l_quantity < pre.x2
	and l_partkey = pre.x1;

-- Q18
-- -----------------------------------------------------------------------------

\echo `date` "################ - Q18 - ################"
explain analyze
select c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, sum(l_quantity)
from customer, orders, lineitem
where o_orderkey in (
		select l_orderkey
		from lineitem
		group by l_orderkey having sum(l_quantity) > 312
	)
	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;

-- Q19
-- -----------------------------------------------------------------------------

\echo `date` "################ - Q19 - ################"
explain analyze
select sum(l_extendedprice* (1 - l_discount)) as revenue
from lineitem, part
where
	(p_partkey = l_partkey
		and p_brand = 'Brand#41'
		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#53'
		and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
		and l_quantity >= 17 and l_quantity <= 17 + 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#32'
		and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
		and l_quantity >= 30 and l_quantity <= 30 + 10
		and p_size between 1 and 15
		and l_shipmode in ('AIR', 'AIR REG')
		and l_shipinstruct = 'DELIVER IN PERSON');

-- Q20
-- -----------------------------------------------------------------------------

\echo `date` "################ - Q20 - ################ (rewritten)"
explain analyze WITH pre AS (
	SELECT l_partkey AS x1, l_suppkey AS x2, 0.5 * sum(l_quantity) AS x3
	FROM lineitem
	WHERE l_shipdate >= date '1997-01-01' and l_shipdate < date '1997-01-01' + interval '1' year
	GROUP BY (l_partkey, l_suppkey)
)
select s_name, s_address
from supplier, nation
where s_suppkey in (
		select ps_suppkey
		from partsupp, pre
		where ps_partkey in (select p_partkey from part where p_name like 'black%')
			and ps_availqty > pre.x3
			and ps_partkey = pre.x1
			and ps_suppkey = pre.x2
	)
	and s_nationkey = n_nationkey
	and n_name = 'JORDAN'
order by s_name;

-- Q21
-- -----------------------------------------------------------------------------

\echo `date` "################ - Q21 - ################"
explain analyze
select s_name, count(*) as numwait
from supplier,
	lineitem l1,
	orders,
	nation
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
		where l2.l_orderkey = l1.l_orderkey
			and l2.l_suppkey <> l1.l_suppkey
	)
	and not exists (
		select * from lineitem l3
		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 = 'UNITED STATES'
group by s_name
order by numwait desc, s_name limit 100;

-- Q22
-- -----------------------------------------------------------------------------

\echo `date` "################ - Q22 - ################"
explain analyze
select cntrycode,
	count(*) as numcust,
	sum(c_acctbal) as totacctbal
from
	(
		select substring(c_phone from 1 for 2) as cntrycode, c_acctbal
		from customer
		where substring(c_phone from 1 for 2) in
				('23', '22', '18', '11', '27', '33', '12')
			and c_acctbal > (
				select avg(c_acctbal)
				from customer
				where c_acctbal > 0.00 and substring(c_phone from 1 for 2) in ('23', '22', '18', '11', '27', '33', '12')
			)
			and not exists (
				select * from orders where o_custkey = c_custkey
			)
	) as custsale
group by cntrycode
order by cntrycode;

