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)