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 '93' 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 WHERE l_shipdate <= date '1998-12-01' - INTERVAL '93' day 
GROUP BY l_returnflag, l_linestatus ORDER BY l_returnflag, l_linestatus ;

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