Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
CH-benCHmark
-- Q1
select ol_number,
sum(ol_quantity) as sum_qty,
sum(ol_amount) as sum_amount,
avg(ol_quantity) as avg_qty,
avg(ol_amount) as avg_amount,
count(*) as count_order
from order_line
where ol_delivery_d > '2007-01-02 00:00:00.000000'
group by ol_number order by ol_number;
-- Q2
select su_suppkey, su_name, n_name, i_id, i_name, su_address, su_phone, su_comment
from item, supplier, stock, nation, region,
(select s_i_id as m_i_id,
min(s_quantity) as m_s_quantity
from stock, supplier, nation, region
where mod((s_w_id*s_i_id),10000)=su_suppkey
and su_nationkey=n_nationkey
and n_regionkey=r_regionkey
and r_name like 'Europ%'
group by s_i_id) m
where i_id = s_i_id
and mod((s_w_id * s_i_id), 10000) = su_suppkey
and su_nationkey = n_nationkey
and n_regionkey = r_regionkey
and i_data like '%b'
and r_name like 'Europ%'
and i_id=m_i_id
and s_quantity = m_s_quantity
order by n_name, su_name, i_id;
-- Q3
select ol_o_id, ol_w_id, ol_d_id,
sum(ol_amount) as revenue, o_entry_d
from customer, new_order, oorder, order_line
where c_state like 'A%'
and c_id = o_c_id
and c_w_id = o_w_id
and c_d_id = o_d_id
and no_w_id = o_w_id
and no_d_id = o_d_id
and no_o_id = o_id
and ol_w_id = o_w_id
and ol_d_id = o_d_id
and ol_o_id = o_id
and o_entry_d > '2007-01-02 00:00:00.000000'
group by ol_o_id, ol_w_id, ol_d_id, o_entry_d
order by revenue desc, o_entry_d;
-- Q4
select o_ol_cnt, count(*) as order_count
from oorder
where o_entry_d >= '2007-01-02 00:00:00.000000'
and o_entry_d < '2012-01-02 00:00:00.000000'
and exists (select *
from order_line
where o_id = ol_o_id
and o_w_id = ol_w_id
and o_d_id = ol_d_id
and ol_delivery_d >= o_entry_d)
group by o_ol_cnt
order by o_ol_cnt;
-- Q5
select n_name,
sum(ol_amount) as revenue
from customer, oorder, order_line, stock, supplier, nation, region
where c_id = o_c_id
and c_w_id = o_w_id
and c_d_id = o_d_id
and ol_o_id = o_id
and ol_w_id = o_w_id
and ol_d_id=o_d_id
and ol_w_id = s_w_id
and ol_i_id = s_i_id
and mod((s_w_id * s_i_id),10000) = su_suppkey
and ascii(substr(c_state,1,1)) = su_nationkey
and su_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'Europe'
and o_entry_d >= '2007-01-02 00:00:00.000000'
group by n_name
order by revenue desc;
--Q6
select sum(ol_amount) as revenue
from order_line
where ol_delivery_d >= '1999-01-01 00:00:00.000000'
and ol_delivery_d < '2020-01-01 00:00:00.000000'
and ol_quantity between 1 and 100000;
--Q7
select su_nationkey as supp_nation,
substr(c_state,1,1) as cust_nation,
extract(year from o_entry_d) as l_year,
sum(ol_amount) as revenue
from supplier, stock, order_line, oorder, customer, nation n1, nation n2
where ol_supply_w_id = s_w_id
and ol_i_id = s_i_id
and mod((s_w_id * s_i_id), 10000) = su_suppkey
and ol_w_id = o_w_id
and ol_d_id = o_d_id
and ol_o_id = o_id
and c_id = o_c_id
and c_w_id = o_w_id
and c_d_id = o_d_id
and su_nationkey = n1.n_nationkey
and ascii(substr(c_state,1,1)) = n2.n_nationkey
and (
(n1.n_name = 'Germany' and n2.n_name = 'Cambodia')
or
(n1.n_name = 'Cambodia' and n2.n_name = 'Germany')
)
and ol_delivery_d between '2007-01-02 00:00:00.000000' and '2012-01-02 00:00:00.000000'
group by su_nationkey, substr(c_state,1,1), extract(year from o_entry_d)
order by su_nationkey, cust_nation, l_year;
--Q8
select extract(year from o_entry_d) as l_year,
sum(case when n2.n_name = 'Germany' then ol_amount else 0 end) / sum(ol_amount) as mkt_share
from item, supplier, stock, order_line, oorder, customer, nation n1, nation n2, region
where i_id = s_i_id
and ol_i_id = s_i_id
and ol_supply_w_id = s_w_id
and mod((s_w_id * s_i_id),10000) = su_suppkey
and ol_w_id = o_w_id
and ol_d_id = o_d_id
and ol_o_id = o_id
and c_id = o_c_id
and c_w_id = o_w_id
and c_d_id = o_d_id
and n1.n_nationkey = ascii(substr(c_state,1,1))
and n1.n_regionkey = r_regionkey
and ol_i_id < 1000
and r_name = 'Europe'
and su_nationkey = n2.n_nationkey
and o_entry_d between '2007-01-02 00:00:00.000000' and '2012-01-02 00:00:00.000000'
and i_data like '%b'
and i_id = ol_i_id
group by extract(year from o_entry_d)
order by l_year;
--Q9
select n_name, extract(year from o_entry_d) as l_year, sum(ol_amount) as sum_profit
from item, stock, supplier, order_line, oorder, nation
where ol_i_id = s_i_id
and ol_supply_w_id = s_w_id
and mod((s_w_id * s_i_id), 10000) = su_suppkey
and ol_w_id = o_w_id
and ol_d_id = o_d_id
and ol_o_id = o_id
and ol_i_id = i_id
and su_nationkey = n_nationkey
and i_data like '%BB'
group by n_name, extract(year from o_entry_d)
order by n_name, l_year desc;
--Q10
select c_id, c_last, sum(ol_amount) as revenue, c_city, c_phone, n_name
from customer, oorder, order_line, nation
where c_id = o_c_id
and c_w_id = o_w_id
and c_d_id = o_d_id
and ol_w_id = o_w_id
and ol_d_id = o_d_id
and ol_o_id = o_id
and o_entry_d >= '2007-01-02 00:00:00.000000'
and o_entry_d <= ol_delivery_d
and n_nationkey = ascii(substr(c_state,1,1))
group by c_id, c_last, c_city, c_phone, n_name
order by revenue desc;
--Q11
select s_i_id, sum(s_order_cnt) as ordercount
from stock, supplier, nation
where mod((s_w_id * s_i_id),10000) = su_suppkey
and su_nationkey = n_nationkey
and n_name = 'Germany'
group by s_i_id
having sum(s_order_cnt) >
(select sum(s_order_cnt) * .005
from stock, supplier, nation
where mod((s_w_id * s_i_id),10000) = su_suppkey
and su_nationkey = n_nationkey
and n_name = 'Germany')
order by ordercount desc;
--Q12
select o_ol_cnt,
sum(case when o_carrier_id = 1 or o_carrier_id = 2 then 1 else 0 end) as high_line_count,
sum(case when o_carrier_id <> 1 and o_carrier_id <> 2 then 1 else 0 end) as low_line_count
from oorder, order_line
where ol_w_id = o_w_id
and ol_d_id = o_d_id
and ol_o_id = o_id
and o_entry_d <= ol_delivery_d
and ol_delivery_d < '2020-01-01 00:00:00.000000'
group by o_ol_cnt
order by o_ol_cnt;
--Q13
select c_count, count(*) as custdist
from (select c_id, count(o_id)
from customer left outer join oorder on (
c_w_id = o_w_id
and c_d_id = o_d_id
and c_id = o_c_id
and o_carrier_id > 8)
group by c_id) as c_orders (c_id, c_count)
group by c_count
order by custdist desc, c_count desc;
--Q14
select 100.00 * sum(case when i_data like 'PR%' then ol_amount else 0 end) / (1+sum(ol_amount)) as promo_revenue
from order_line, item
where ol_i_id = i_id and ol_delivery_d >= '2007-01-02 00:00:00.000000'
and ol_delivery_d < '2020-01-02 00:00:00.000000';
--Q15
with revenue (supplier_no, total_revenue) as (
select mod((s_w_id * s_i_id),10000) as supplier_no,
sum(ol_amount) as total_revenue
from order_line, stock
where ol_i_id = s_i_id and ol_supply_w_id = s_w_id
and ol_delivery_d >= '2007-01-02 00:00:00.000000'
group by mod((s_w_id * s_i_id),10000))
select su_suppkey, su_name, su_address, su_phone, total_revenue
from supplier, revenue
where su_suppkey = supplier_no
and total_revenue = (select max(total_revenue) from revenue)
order by su_suppkey;
--Q16
select i_name,
substr(i_data, 1, 3) as brand,
i_price,
count(distinct (mod((s_w_id * s_i_id),10000))) as supplier_cnt
from stock, item
where i_id = s_i_id
and i_data not like 'zz%'
and (mod((s_w_id * s_i_id),10000) not in
(select su_suppkey
from supplier
where su_comment like '%bad%'))
group by i_name, substr(i_data, 1, 3), i_price
order by supplier_cnt desc;
--Q17
select sum(ol_amount) / 2.0 as avg_yearly
from order_line, (select i_id, avg(ol_quantity) as a
from item, order_line
where i_data like '%b'
and ol_i_id = i_id
group by i_id) t
where ol_i_id = t.i_id
and ol_quantity < t.a;
--Q18
select c_last, c_id o_id, o_entry_d, o_ol_cnt, sum(ol_amount)
from customer, oorder, order_line
where c_id = o_c_id
and c_w_id = o_w_id
and c_d_id = o_d_id
and ol_w_id = o_w_id
and ol_d_id = o_d_id
and ol_o_id = o_id
group by o_id, o_w_id, o_d_id, c_id, c_last, o_entry_d, o_ol_cnt
having sum(ol_amount) > 200
order by sum(ol_amount) desc, o_entry_d;
--Q19
select sum(ol_amount) as revenue
from order_line, item
where (
ol_i_id = i_id
and i_data like '%a'
and ol_quantity >= 1
and ol_quantity <= 10
and i_price between 1 and 400000
and ol_w_id in (1,2,3)
) or (
ol_i_id = i_id
and i_data like '%b'
and ol_quantity >= 1
and ol_quantity <= 10
and i_price between 1 and 400000
and ol_w_id in (1,2,4)
) or (
ol_i_id = i_id
and i_data like '%c'
and ol_quantity >= 1
and ol_quantity <= 10
and i_price between 1 and 400000
and ol_w_id in (1,5,3)
);
--Q20
select su_name, su_address
from supplier, nation
where su_suppkey in
(select mod(s_i_id * s_w_id, 10000)
from stock, order_line
where s_i_id in
(select i_id
from item
where i_data like 'co%')
and ol_i_id=s_i_id
and ol_delivery_d > '2010-05-23 12:00:00'
group by s_i_id, s_w_id, s_quantity
having 2*s_quantity > sum(ol_quantity))
and su_nationkey = n_nationkey
and n_name = 'Germany'
order by su_name;
--Q21
select su_name, count(*) as numwait
from supplier, order_line l1, oorder, stock, nation
where ol_o_id = o_id
and ol_w_id = o_w_id
and ol_d_id = o_d_id
and ol_w_id = s_w_id
and ol_i_id = s_i_id
and mod((s_w_id * s_i_id),10000) = su_suppkey
and l1.ol_delivery_d > o_entry_d
and not exists (select *
from order_line l2
where l2.ol_o_id = l1.ol_o_id
and l2.ol_w_id = l1.ol_w_id
and l2.ol_d_id = l1.ol_d_id
and l2.ol_delivery_d > l1.ol_delivery_d)
and su_nationkey = n_nationkey
and n_name = 'Germany'
group by su_name
order by numwait desc, su_name;
--Q22
select substr(c_state,1,1) as country,
count(*) as numcust,
sum(c_balance) as totacctbal
from customer
where substr(c_phone,1,1) in ('1','2','3','4','5','6','7')
and c_balance > (select avg(c_BALANCE)
from customer
where c_balance > 0.00
and substr(c_phone,1,1) in ('1','2','3','4','5','6','7'))
and not exists (select *
from oorder
where o_c_id = c_id
and o_w_id = c_w_id
and o_d_id = c_d_id)
group by substr(c_state,1,1)
order by substr(c_state,1,1);
DROP TABLE IF EXISTS order_line;
CREATE TABLE order_line (
ol_w_id int NOT NULL,
ol_d_id int NOT NULL,
ol_o_id int NOT NULL,
ol_number int NOT NULL,
ol_i_id int NOT NULL,
ol_delivery_d timestamp NULL DEFAULT NULL,
ol_amount decimal(6,2) NOT NULL,
ol_supply_w_id int NOT NULL,
ol_quantity decimal(2,0) NOT NULL,
ol_dist_info char(24) NOT NULL,
PRIMARY KEY (ol_w_id,ol_d_id,ol_o_id,ol_number)
);
DROP TABLE IF EXISTS new_order;
CREATE TABLE new_order (
no_w_id int NOT NULL,
no_d_id int NOT NULL,
no_o_id int NOT NULL,
PRIMARY KEY (no_w_id,no_d_id,no_o_id)
);
DROP TABLE IF EXISTS stock;
CREATE TABLE stock (
s_w_id int NOT NULL,
s_i_id int NOT NULL,
s_quantity decimal(4,0) NOT NULL,
s_ytd decimal(8,2) NOT NULL,
s_order_cnt int NOT NULL,
s_remote_cnt int NOT NULL,
s_data varchar(50) NOT NULL,
s_dist_01 char(24) NOT NULL,
s_dist_02 char(24) NOT NULL,
s_dist_03 char(24) NOT NULL,
s_dist_04 char(24) NOT NULL,
s_dist_05 char(24) NOT NULL,
s_dist_06 char(24) NOT NULL,
s_dist_07 char(24) NOT NULL,
s_dist_08 char(24) NOT NULL,
s_dist_09 char(24) NOT NULL,
s_dist_10 char(24) NOT NULL,
PRIMARY KEY (s_w_id,s_i_id)
);
DROP TABLE IF EXISTS oorder;
CREATE TABLE oorder (
o_w_id int NOT NULL,
o_d_id int NOT NULL,
o_id int NOT NULL,
o_c_id int NOT NULL,
o_carrier_id int DEFAULT NULL,
o_ol_cnt decimal(2,0) NOT NULL,
o_all_local decimal(1,0) NOT NULL,
o_entry_d timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (o_w_id,o_d_id,o_id),
UNIQUE (o_w_id,o_d_id,o_c_id,o_id)
);
DROP TABLE IF EXISTS history;
CREATE TABLE history (
h_c_id int NOT NULL,
h_c_d_id int NOT NULL,
h_c_w_id int NOT NULL,
h_d_id int NOT NULL,
h_w_id int NOT NULL,
h_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
h_amount decimal(6,2) NOT NULL,
h_data varchar(24) NOT NULL
);
DROP TABLE IF EXISTS customer;
CREATE TABLE customer (
c_w_id int NOT NULL,
c_d_id int NOT NULL,
c_id int NOT NULL,
c_discount decimal(4,4) NOT NULL,
c_credit char(2) NOT NULL,
c_last varchar(16) NOT NULL,
c_first varchar(16) NOT NULL,
c_credit_lim decimal(12,2) NOT NULL,
c_balance decimal(12,2) NOT NULL,
c_ytd_payment float NOT NULL,
c_payment_cnt int NOT NULL,
c_delivery_cnt int NOT NULL,
c_street_1 varchar(20) NOT NULL,
c_street_2 varchar(20) NOT NULL,
c_city varchar(20) NOT NULL,
c_state char(2) NOT NULL,
c_zip char(9) NOT NULL,
c_phone char(16) NOT NULL,
c_since timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
c_middle char(2) NOT NULL,
c_data varchar(500) NOT NULL,
PRIMARY KEY (c_w_id,c_d_id,c_id)
);
DROP TABLE IF EXISTS district;
CREATE TABLE district (
d_w_id int NOT NULL,
d_id int NOT NULL,
d_ytd decimal(12,2) NOT NULL,
d_tax decimal(4,4) NOT NULL,
d_next_o_id int NOT NULL,
d_name varchar(10) NOT NULL,
d_street_1 varchar(20) NOT NULL,
d_street_2 varchar(20) NOT NULL,
d_city varchar(20) NOT NULL,
d_state char(2) NOT NULL,
d_zip char(9) NOT NULL,
PRIMARY KEY (d_w_id,d_id)
);
DROP TABLE IF EXISTS item;
CREATE TABLE item (
i_id int NOT NULL,
i_name varchar(24) NOT NULL,
i_price decimal(5,2) NOT NULL,
i_data varchar(50) NOT NULL,
i_im_id int NOT NULL,
PRIMARY KEY (i_id)
);
DROP TABLE IF EXISTS warehouse;
CREATE TABLE warehouse (
w_id int NOT NULL,
w_ytd decimal(12,2) NOT NULL,
w_tax decimal(4,4) NOT NULL,
w_name varchar(10) NOT NULL,
w_street_1 varchar(20) NOT NULL,
w_street_2 varchar(20) NOT NULL,
w_city varchar(20) NOT NULL,
w_state char(2) NOT NULL,
w_zip char(9) NOT NULL,
PRIMARY KEY (w_id)
);
DROP TABLE IF EXISTS region;
create table region (
r_regionkey int not null,
r_name char(55) not null,
r_comment char(152) not null,
PRIMARY KEY ( r_regionkey )
);
DROP TABLE IF EXISTS nation;
create table nation (
n_nationkey int not null,
n_name char(25) not null,
n_regionkey int not null,
n_comment char(152) not null,
PRIMARY KEY ( n_nationkey )
);
DROP TABLE IF EXISTS supplier;
create table supplier (
su_suppkey int not null,
su_name char(25) not null,
su_address varchar(40) not null,
su_nationkey int not null,
su_phone char(15) not null,
su_acctbal numeric(12,2) not null,
su_comment char(101) not null,
PRIMARY KEY ( su_suppkey )
);
SELECT create_distributed_table('order_line','ol_w_id');
SELECT create_distributed_table('new_order','no_w_id');
SELECT create_distributed_table('stock','s_w_id');
SELECT create_distributed_table('oorder','o_w_id');
SELECT create_distributed_table('history','h_w_id');
SELECT create_distributed_table('customer','c_w_id');
SELECT create_distributed_table('district','d_w_id');
SELECT create_distributed_table('warehouse','w_id');
SELECT create_reference_table('item');
SELECT create_reference_table('region');
SELECT create_reference_table('nation');
SELECT create_reference_table('supplier');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.