def query6():
randDate = date(helper.rand(1993, 1997), 1, 1)
discount = str(random.uniform(0.02, 0.09))
quantity = str(helper.rand(24, 25))
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 = "sum(l_extendedprice * l_discount) as revenue"
fromTbl = "lineitem"
where = f"l_shipdate >= {dateIdentifier}'{randDate}' AND l_shipdate < {secondDate} AND l_discount between {discount} - 0.01 AND {discount} + 0.01 AND l_quantity < {quantity}"
query = f"SELECT {select} FROM {fromTbl} WHERE {where}"
return query
def query12():
(shipmode1, shipmode2) = helper.getModes()
randDate = date(helper.rand(1993, 1997), 1, 1)
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 = "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"
fromTbl = "orders, lineitem"
where = f"o_orderkey = l_orderkey AND l_shipmode in ('{shipmode1}', '{shipmode2}') AND l_commitdate < l_receiptdate AND l_shipdate < l_commitdate AND l_receiptdate >= {dateIdentifier}'{randDate}' AND l_receiptdate < {secondDate}"
group = "l_shipmode"
order = "l_shipmode"
query = f"SELECT {select} FROM {fromTbl} WHERE {where} GROUP BY {group} ORDER BY {order}"
return query
def query5():
region = helper.getRName()
randDate = date(helper.rand(1993, 1997), 1, 1)
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 = "n_name, sum(l_extendedprice * (1 - l_discount)) as revenue"
fromTbl = "customer, orders, lineitem, supplier, nation, region"
where = f"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 = '{region}' AND o_orderdate >= {dateIdentifier}'{randDate}' AND o_orderdate < {secondDate}"
group = "n_name"
order = "revenue desc"
query = f"SELECT {select} FROM {fromTbl} WHERE {where} 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
