# tables
create table part (p_partkey int, p_name varchar(55), p_mfgr char(25),
                   p_brand char(10), p_type varchar(25), p_size int,
                   p_container char(10), p_retailprice real,
                   p_comment varchar(23), primary key(p_partkey));
create table supplier (s_suppkey int, s_name char(25),
                       s_address varchar(40), s_nation_key int,
                       s_phone char(15), s_acctbal real,
                       s_comment varchar(128), primary key(s_suppkey));
create table partsupp (ps_partkey int, ps_suppkey int,
                       ps_availqty int, ps_supplycost real,
                       ps_comment varchar(199),
                       primary key(ps_partkey, ps_suppkey));
create table customer (c_custkey int, c_name varchar(25),
                       c_address varchar(40), c_nationkey int,
                       c_phone char(15), c_acctbal real,
                       c_mktsegment char(10), c_comment varchar(117),
                       primary key(c_custkey));
create table orders (o_orderkey int, o_custkey int,
                     o_orderstatus char(1), o_totalprice real,
                     o_orderdate date, o_orderpriority char(15),
                     o_clerk char(15), o_shippriority int,
                     o_comment varchar(79), primary key(o_orderkey));
create table lineitem (l_orderkey int, l_partkey int,
                       l_suppkey int, l_linenumber int, l_quantity real,
                       l_extendedprice real, l_discount real, l_tax real,
                       l_returnflag char(1), l_linestatus char(1),
                       l_shipdate date, l_commitdate date,
                       l_receiptdate date, l_shipinstruct char(25),
                       l_shipmode char(10), l_comment varchar(44),
                       primary key(l_orderkey, l_linenumber));
create table nation (n_nationkey int, n_name char(25),
                     n_regionkey int, n_comment varchar(152),
                     primary key(n_nationkey));
create table region (r_regionkey int, r_name char(25),
                     r_comment varchar(152), primary key(r_regionkey));

# queries

# Q01
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 '?' day (3)
 group by
     l_returnflag,
     l_linestatus
 order by
     l_returnflag,
     l_linestatus;

# Q02
select
     s_acctbal,
     s_name,
     n_name,
     p_partkey,
     p_mfgr,
     s_address,
     s_phone,
     s_comment
 from
     part,
     supplier,
     partsupp,
     nation,
     region
 where
     p_partkey = ps_partkey
     and s_suppkey = ps_suppkey
     and p_size = ?
     and p_type like '%?'
     and s_nationkey = n_nationkey
     and n_regionkey = r_regionkey
     and r_name = '?'
     and ps_supplycost = (
         select
             min(ps_supplycost)
         from
             partsupp,
             supplier,
             nation,
             region
         where
             p_partkey = ps_partkey
             and s_suppkey = ps_suppkey
             and s_nationkey = n_nationkey
             and n_regionkey = r_regionkey
             and r_name = '?'
     )
 order by
     s_acctbal desc,
     n_name,
     s_name,
     p_partkey;

# Q03
select
     l_orderkey,
     sum(l_extendedprice * (1 - l_discount)) as revenue,
     o_orderdate,
     o_shippriority
 from
     customer,
     orders,
     lineitem
 where
     c_mktsegment = '?'
     and c_custkey = o_custkey
     and l_orderkey = o_orderkey
     and o_orderdate < date '?'
     and l_shipdate > date '?'
 group by
     l_orderkey,
     o_orderdate,
     o_shippriority
 order by
     revenue desc,
     o_orderdate;

# Q04
select
     o_orderpriority,
     count(*) as order_count
 from
     orders
 where
     o_orderdate >= date '?'
     and o_orderdate < date '?' + 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;

# Q05
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 = '?'
     and o_orderdate >= date '?'
     and o_orderdate < date '?' + interval '1' year
 group by
     n_name
 order by
     revenue desc;

# Q06
select
     sum(l_extendedprice * l_discount) as revenue
 from
     lineitem
 where
     l_shipdate >= date '?'
     and l_shipdate < date '?' + interval '1' year
     and l_discount between ? - 0.01 and ? + 0.01
     and l_quantity < ?;

# Q07
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 = '?' and n2.n_name = '?')
                 or (n1.n_name = '?' and n2.n_name = '?')
             )
             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;

# Q08
select
     o_year,
     sum(case
         when nation = '?' 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 = '?'
             and s_nationkey = n2.n_nationkey
             and o_orderdate between date '1995-01-01' and date '1996-12-31'
             and p_type = '?'
     ) as all_nations
 group by
     o_year
 order by
     o_year;

# Q09
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 '%?%'
     ) as profit
 group by
     nation,
     o_year
 order by
     nation,
     o_year desc;

# 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,
     orders,
     lineitem,
     nation
 where
     c_custkey = o_custkey
     and l_orderkey = o_orderkey
     and o_orderdate >= date '?'
     and o_orderdate < date '?' + 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;

# Q11
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 = '?'
 group by
     ps_partkey having
         sum(ps_supplycost * ps_availqty) > (
             select
                 sum(ps_supplycost * ps_availqty) * ?
             from
                 partsupp,
                 supplier,
                 nation
             where
                 ps_suppkey = s_suppkey
                 and s_nationkey = n_nationkey
                 and n_name = '?'
         )
 order by
     value desc;

# 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,
     lineitem
 where
     o_orderkey = l_orderkey
     and l_shipmode in (':1', ':2')
     and l_commitdate < l_receiptdate
     and l_shipdate < l_commitdate
     and l_receiptdate >= date ':3'
     and l_receiptdate < date ':3' + interval '1' year
 group by
     l_shipmode
 order by
     l_shipmode;

# Q13
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 '%:1%:2%'
         group by
             c_custkey
     ) as c_orders (c_custkey, c_count)
 group by
     c_count
 order by
     custdist desc,
     c_count desc;

# 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,
     part
 where
     l_partkey = p_partkey
     and l_shipdate >= date ':1'
     and l_shipdate < date ':1' + interval '1' month;

# Q15
# create view revenue:s (supplier_no, total_revenue) as
select
         l_suppkey,
         sum(l_extendedprice * (1 - l_discount))
     from
         lineitem
     where
         l_shipdate >= date ':1'
         and l_shipdate < date ':1' + interval '3' month
     group by
         l_suppkey;
# actual query
select
     s_suppkey,
     s_name,
     s_address,
     s_phone,
     total_revenue
 from
     supplier,
     revenue_s
 where
     s_suppkey = supplier_no
     and total_revenue = (
         select
             max(total_revenue)
         from
             revenue_s
     )
 order by
     s_suppkey;

# Q16
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 <> ':1'
     and p_type not like ':2%'
     and p_size in (:3, :4, :5, :6, :7, :8, :9, :10)
     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
select
   sum(l_extendedprice) / 7.0 as avg_yearly
 from
   lineitem,
   part
 where
   p_partkey = l_partkey
   and p_brand = ':1'
   and p_container = ':2'
   and l_quantity < (
     select
       0.2 * avg(l_quantity)
     from
       lineitem
     where
       l_partkey = p_partkey
   );

# Q18
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) > :1
     )
     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;

# Q19
select
     sum(l_extendedprice* (1 - l_discount)) as revenue
 from
     lineitem,
     part
 where
     (
         p_partkey = l_partkey
         and p_brand = ':1'
         and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
         and l_quantity >= :4 and l_quantity <= :4 + 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 = ':2'
         and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
         and l_quantity >= :5 and l_quantity <= :5 + 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 = ':3'
         and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
         and l_quantity >= :6 and l_quantity <= :6 + 10
         and p_size between 1 and 15
         and l_shipmode in ('AIR', 'AIR REG')
         and l_shipinstruct = 'DELIVER IN PERSON'
     );

# Q20
select
     s_name,
     s_address
 from
     supplier,
     nation
 where
     s_suppkey in (
         select
             ps_suppkey
         from
             partsupp
         where
             ps_partkey in (
                 select
                     p_partkey
                 from
                     part
                 where
                     p_name like ':1%'
             )
             and ps_availqty > (
                 select
                     0.5 * sum(l_quantity)
                 from
                     lineitem
                 where
                     l_partkey = ps_partkey
                     and l_suppkey = ps_suppkey
                     and l_shipdate >= date ':2'
                     and l_shipdate < date ':2' + interval '1' year
             )
     )
     and s_nationkey = n_nationkey
     and n_name = ':3'
 order by
     s_name;

# Q21
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 = ':1'
 group by
     s_name
 order by
     numwait desc,
     s_name;

# Q22
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
                 (':1', ':2', ':3', ':4', ':5', ':6', ':7')
             and c_acctbal > (
                 select
                     avg(c_acctbal)
                 from
                     customer
                 where
                     c_acctbal > 0.00
                     and substring(c_phone from 1 for 2) in
                         (':1', ':2', ':3', ':4', ':5', ':6', ':7')
             )
             and not exists (
                 select
                     *
                 from
                     orders
                 where
                     o_custkey = c_custkey
             )
     ) as custsale
 group by
     cntrycode
 order by
     cntrycode;