def query21():
(nation, tmp) = helper.getNNames()
select = "s_name, count(*) as numwait"
fromTbl = "supplier, orders, nation, lineitem l1"
where = f"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 = '{nation}'"
group = "s_name"
order = "numwait DESC, s_name"
limit = "LIMIT 100"
query = f"SELECT {select} FROM {fromTbl} WHERE {where} GROUP BY {group} ORDER BY {order} {limit}"
return query
def query11():
(nation, tmp) = helper.getNNames()
fraction = 0.0001 / sf
select = "ps_partkey, SUM(ps_supplycost * ps_availqty) as value"
fromTbl = "partsupp, supplier, nation"
where = f"ps_suppkey = s_suppkey AND s_nationkey = n_nationkey AND n_name = '{nation}'"
subSelect = f"sum(ps_supplycost * ps_availqty) * {fraction}"
subFromTbl = "partsupp, supplier, nation"
subWhere = f"ps_suppkey = s_suppkey AND s_nationkey = n_nationkey AND n_name = '{nation}'"
group = f"ps_partkey HAVING SUM(ps_supplycost * ps_availqty) > (SELECT {subSelect} FROM {subFromTbl} WHERE {subWhere})"
order = "value DESC"
query = f"SELECT {select} FROM {fromTbl} WHERE {where} GROUP BY {group} ORDER BY {order}"
return query
def query7():
(nation1, nation2) = helper.getNNames()
if db == "sqlite" or db == "pandas":
dateIdentifier = ""
extractYear = "strftime('%Y', l_shipdate)"
else:
dateIdentifier = "DATE "
extractYear = "extract(year from l_shipdate)"
select = "supp_nation, cust_nation, l_year, sum(volume) as revenue"
subSelect = f"n1.n_name as supp_nation, n2.n_name as cust_nation, {extractYear} as l_year, l_extendedprice * (1 - l_discount) as volume"
subFromTbl = "supplier, lineitem, orders, customer, nation n1, nation n2"
subWhere = f"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 = '{nation1}' AND n2.n_name = '{nation2}') OR (n1.n_name = '{nation2}' and n2.n_name = '{nation1}')) AND l_shipdate between {dateIdentifier}'1995-01-01' and {dateIdentifier}'1996-12-31'"
group = "supp_nation, cust_nation, l_year"
order = "supp_nation, cust_nation, l_year"
query = f"SELECT {select} FROM(SELECT {subSelect} FROM {subFromTbl} WHERE {subWhere}) AS shipping GROUP BY {group} ORDER BY {order}"
return query
def query20():
color = helper.getColor()
randDate = date(helper.rand(1993, 1997), 1, 1)
(nation, tmp) = helper.getNNames()
if db == "duck":
addDays = str(helper.yearsToDays(randDate, 1))
elif db == "monet":
addDays = "interval '1' year"
if db == "sqlite" or db == "pandas":
dateIdentifier = ""
secondDate = f"date('{randDate}', '+1 year')"
else:
dateIdentifier = "DATE "
secondDate = f"DATE '{randDate}' + {addDays}"
select = "s_name, s_address"
fromTbl = "supplier, nation"
subQuery = f"SELECT ps_suppkey FROM partsupp WHERE ps_partkey in (SELECT p_partkey FROM part WHERE p_name LIKE '{color}%') AND ps_availqty > (SELECT 0.5 * SUM(l_quantity) FROM lineitem WHERE l_partkey = ps_partkey AND l_suppkey = ps_suppkey AND l_shipdate >= {dateIdentifier}'{randDate}' AND l_shipdate < {secondDate})"
where = f"s_suppkey IN ({subQuery}) AND s_nationkey = n_nationkey AND n_name = '{nation}'"
order = "s_name"
query = f"SELECT {select} FROM {fromTbl} WHERE {where} ORDER BY {order}"
return query