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)