def query14():
randDate = date(helper.rand(1993, 1997), helper.rand(1, 12), 1)
if db == "duck":
addDays = str(helper.monthsToDays(randDate, 1))
elif db == "monet":
addDays = "interval '1' month"
if db == "sqlite" or db == "pandas":
dateIdentifier = ""
secondDate = f"date('{randDate}', '+1 month')"
else:
dateIdentifier = "DATE "
secondDate = f"DATE '{randDate}' + {addDays}"
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"
fromTbl = "lineitem, part"
where = f"l_partkey = p_partkey AND l_shipdate >= {dateIdentifier}'{randDate}' AND l_shipdate < {secondDate}"
query = f"SELECT {select} FROM {fromTbl} WHERE {where}"
return query
def query10():
randDate = helper.getRandMonth(date(1993, 2, 1), date(1995, 1, 1))
if db == "duck":
addDays = str(helper.monthsToDays(randDate, 3))
elif db == "monet":
addDays = "interval '3' month"
if db == "sqlite" or db == "pandas":
dateIdentifier = ""
secondDate = f"date('{randDate}', '+3 month')"
else:
dateIdentifier = "DATE "
secondDate = f"DATE '{randDate}' + {addDays}"
select = "c_custkey, c_name, sum(l_extendedprice * (1 - l_discount)) as revenue, c_acctbal, n_name, c_address, c_phone, c_comment"
fromTbl = "customer, orders, lineitem, nation"
where = f"c_custkey = o_custkey AND l_orderkey = o_orderkey AND o_orderdate >= {dateIdentifier}'{randDate}' AND o_orderdate < {secondDate} AND l_returnflag = 'R' AND c_nationkey = n_nationkey"
group = "c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment"
order = "revenue desc"
query = f"SELECT {select} FROM {fromTbl} WHERE {where} GROUP BY {group} ORDER BY {order}"
return query
def query4():
randDate = helper.getRandMonth(date(1993, 1, 1), date(1997, 10, 1))
if db == "duck":
addDays = str(helper.monthsToDays(randDate, 3))
elif db == "monet":
addDays = "interval '3' month"
if db == "sqlite" or db == "pandas":
dateIdentifier = ""
secondDate = f"date('{randDate}', '+3 month')"
else:
dateIdentifier = "DATE "
secondDate = f"DATE '{randDate}' + {addDays}"
subQuery = f"SELECT * FROM lineitem WHERE l_orderkey = o_orderkey AND l_commitdate < l_receiptdate"
select = "o_orderpriority, count(*) as order_count"
fromTbl = "orders"
where = f"o_orderdate >= {dateIdentifier}'{randDate}' AND o_orderdate < {secondDate} AND exists ({subQuery})"
group = "o_orderpriority"
order = "o_orderpriority"
query = f"SELECT {select} FROM {fromTbl} WHERE {where} GROUP BY {group} ORDER BY {order}"
return query
def query15():
randDate = helper.getRandMonth(date(1993, 1, 1), date(1997, 10, 1))
if db == "duck":
addDays = str(helper.monthsToDays(randDate, 3))
elif db == "monet":
addDays = "interval '3' month"
if db == "sqlite" or db == "pandas":
dateIdentifier = ""
secondDate = f"date('{randDate}', '+3 month')"
else:
dateIdentifier = "DATE "
secondDate = f"DATE '{randDate}' + {addDays}"
if db == "pandas":
view = f"SELECT l_suppkey as supplier_no, sum(l_extendedprice * (1 - l_discount)) as total_revenue FROM lineitem WHERE l_shipdate >= {dateIdentifier}'{randDate}' AND l_shipdate < {secondDate} GROUP BY l_suppkey"
else:
view = f"CREATE VIEW revenue (supplier_no, total_revenue) AS SELECT l_suppkey, sum(l_extendedprice * (1 - l_discount)) FROM lineitem WHERE l_shipdate >= {dateIdentifier}'{randDate}' AND l_shipdate < {secondDate} GROUP BY l_suppkey"
select = "s_suppkey, s_name, s_address, s_phone, total_revenue"
fromTbl = "supplier, revenue"
where = "s_suppkey = supplier_no AND total_revenue = (SELECT max(total_revenue) FROM revenue)"
order = "s_suppkey"
drop = "DROP VIEW revenue"
query = f"SELECT {select} FROM {fromTbl} WHERE {where} ORDER BY {order}"
return (view, query, drop)
