{ "cells": [ { "cell_type": "markdown", "id": "5a618aea", "metadata": {}, "source": [ "# TPC-DS: DuckDB vs Opteryx on the same Parquet\n", "\n", "Runs the 99 **TPC-DS** queries against two embedded SQL engines. Both engines read **the same Parquet files**, exported by DuckDB, with no rewriting or engine-specific loading:\n", "\n", "| Engine | Reads |\n", "|---|---|\n", "| **DuckDB** | the Parquet files, through `read_parquet` views |\n", "| **Opteryx** | the same Parquet files, as a dataset folder per table |\n", "\n", "The pipeline is: generate TPC-DS with DuckDB's built-in `dsdgen`, export each table to Parquet with DuckDB's default writer settings, run all 99 queries on both engines, and compare.\n", "\n", "TPC-DS is a different kind of test from TPC-H. Its 24-table snowflake schema and 99 queries reach into window frames, `ROLLUP` and `GROUPING SETS`, correlated subqueries and multi-branch `UNION ALL`, so the first result is **coverage**: how many of the 99 queries each engine plans and runs. Timings are compared only on the queries both engines complete, one query at a time.\n", "\n", "This notebook accompanies [Benchmarking](https://docs.opteryx.app/docs/reference/internals/benchmarking#tpc-ds) on docs.opteryx.app. The numbers it produces belong to whatever machine runs it. A Colab runtime is small and shared, so compare the two engines with each other, not with figures published anywhere else.\n", "\n", "**Runtime note:** at the default scale factor of 1, `store_sales` is about 2.9M rows and the 24 tables come to roughly 1 GB as generated. Generation, export and 99 queries on two engines take several minutes on a Colab CPU runtime. For a quick smoke test, lower `SCALE_FACTOR` to `0.1`." ] }, { "cell_type": "code", "execution_count": null, "id": "c006807b", "metadata": {}, "outputs": [], "source": [ "# --- Configuration -----------------------------------------------------\n", "SCALE_FACTOR = 1 # TPC-DS scale factor (1 = ~1 GB of raw data). DuckDB's\n", " # dsdgen also accepts fractions, e.g. 0.1, for a fast smoke test.\n", "ITERATIONS = 3 # warm runs per query per engine; the minimum is reported\n", "QUERY_TIMEOUT_S = 60 # per iteration; a query that overruns is recorded as timed out\n", "import os\n", "WORK_DIR = os.path.abspath(\"/content/tpcds_bench\" if os.path.isdir(\"/content\") else \"./tpcds_bench\")\n", "\n", "os.makedirs(WORK_DIR, exist_ok=True)\n", "os.chdir(WORK_DIR)\n", "print(\"Working directory:\", os.getcwd())\n", "\n", "cpus = len(os.sched_getaffinity(0)) if hasattr(os, \"sched_getaffinity\") else os.cpu_count()\n", "print(\"CPUs available:\", cpus)" ] }, { "cell_type": "markdown", "id": "45ee48d4", "metadata": {}, "source": [ "## 0. Install engines\n", "\n", "`duckdb` and `opteryx-core` install from PyPI." ] }, { "cell_type": "code", "execution_count": null, "id": "1c9775e6", "metadata": {}, "outputs": [], "source": [ "%pip install -q --upgrade duckdb opteryx-core pandas matplotlib\n", "\n", "import duckdb\n", "import opteryx\n", "\n", "print(\"duckdb \", duckdb.__version__)\n", "print(\"opteryx \", opteryx.__version__)" ] }, { "cell_type": "markdown", "id": "80cca686", "metadata": {}, "source": [ "## 1. Generate TPC-DS and export it to Parquet\n", "\n", "DuckDB's `tpcds` extension wraps the TPC's own `dsdgen` and generates the dataset into an in-memory connection. Each of the 24 tables is exported with DuckDB's own Parquet writer at its default settings. Those files are the only copy of the data either engine reads.\n", "\n", "Each table gets its own folder, `data/tpcds//`, which is the layout Opteryx reads as a dataset. DuckDB reads the same files by path." ] }, { "cell_type": "code", "execution_count": null, "id": "8b849f27", "metadata": {}, "outputs": [], "source": [ "import shutil\n", "\n", "con = duckdb.connect()\n", "con.execute(\"INSTALL tpcds; LOAD tpcds;\")\n", "con.execute(f\"CALL dsdgen(sf={SCALE_FACTOR});\")\n", "\n", "DATA_ROOT = os.path.join(WORK_DIR, \"data\", \"tpcds\")\n", "os.makedirs(DATA_ROOT, exist_ok=True)\n", "\n", "TABLES = [r[0] for r in con.execute(\"SHOW TABLES\").fetchall()]\n", "print(f\"{len(TABLES)} tables: {TABLES}\")\n", "for t in TABLES:\n", " table_dir = os.path.join(DATA_ROOT, t)\n", " shutil.rmtree(table_dir, ignore_errors=True) # a dataset folder must hold one format only\n", " os.makedirs(table_dir)\n", " output_path = os.path.join(table_dir, f\"{t}.parquet\")\n", " n = con.execute(f\"SELECT COUNT(*) FROM {t}\").fetchone()[0]\n", " con.execute(f\"COPY {t} TO '{output_path}' (FORMAT PARQUET)\")\n", " print(f\" {t:<24} {n:>12,} rows -> {output_path}\")\n", "con.close()" ] }, { "cell_type": "markdown", "id": "662759ca", "metadata": {}, "source": [ "## 2. The 99 TPC-DS queries\n", "\n", "These are the queries that Opteryx runs in its own TPC-DS suite ([`tests/performance/tpcds/opteryx/queries/`](https://github.com/mabel-dev/opteryx-core/tree/c0cd78f881a8ac31645dbb94277ff6cbb3ab4062/tests/performance/tpcds/opteryx/queries) in `opteryx-core`, copied here at commit `c0cd78f8`). That suite generates them from DuckDB's `tpcds_queries()`, the official TPC-DS templates with one resolved variant per query, so both engines run the same text DuckDB ships.\n", "\n", "A handful of those templates carry patches for syntax Opteryx does not accept, such as a bare string compared with a `DATE`, `CAST(x AS INT)` spelled `INT64`, or an unqualified `*` mixed with other columns. DuckDB accepts the patched text too. The suite keeps queries that Opteryx cannot yet plan rather than deleting them, so all 99 are here.\n", "\n", "The queries are written with a `testdata.tpcds_tiny.
` placeholder prefix. The only per-engine change is that prefix. Everything else runs unchanged on both engines:\n", "\n", "| | DuckDB | Opteryx |\n", "|---|---|---|\n", "| table prefix | stripped (bare table name) | rewritten to `data.tpcds.` |\n", "\n", "The queries are saved to `queries.json` for the benchmark script in the next step." ] }, { "cell_type": "code", "execution_count": null, "id": "9475c9c6", "metadata": {}, "outputs": [], "source": [ "QUERIES = {\n", " 'Q01': \"WITH customer_total_return AS\\n (SELECT sr_customer_sk AS ctr_customer_sk,\\n sr_store_sk AS ctr_store_sk,\\n sum(sr_return_amt) AS ctr_total_return\\n FROM testdata.tpcds_tiny.store_returns,\\n testdata.tpcds_tiny.date_dim\\n WHERE sr_returned_date_sk = d_date_sk\\n AND d_year = 2000\\n GROUP BY sr_customer_sk,\\n sr_store_sk)\\nSELECT c_customer_id\\nFROM customer_total_return ctr1,\\n testdata.tpcds_tiny.store,\\n testdata.tpcds_tiny.customer\\nWHERE ctr1.ctr_total_return >\\n (SELECT avg(ctr_total_return)*1.2\\n FROM customer_total_return ctr2\\n WHERE ctr1.ctr_store_sk = ctr2.ctr_store_sk)\\n AND s_store_sk = ctr1.ctr_store_sk\\n AND s_state = 'TN'\\n AND ctr1.ctr_customer_sk = c_customer_sk\\nORDER BY c_customer_id\\nLIMIT 100;\",\n", " 'Q02': \"WITH wscs AS\\n (SELECT sold_date_sk,\\n sales_price\\n FROM\\n (SELECT ws_sold_date_sk sold_date_sk,\\n ws_ext_sales_price sales_price\\n FROM testdata.tpcds_tiny.web_sales\\n UNION ALL SELECT cs_sold_date_sk sold_date_sk,\\n cs_ext_sales_price sales_price\\n FROM testdata.tpcds_tiny.catalog_sales) sq1),\\n wswscs AS\\n (SELECT d_week_seq,\\n sum(CASE\\n WHEN (d_day_name='Sunday') THEN sales_price\\n ELSE NULL\\n END) sun_sales,\\n sum(CASE\\n WHEN (d_day_name='Monday') THEN sales_price\\n ELSE NULL\\n END) mon_sales,\\n sum(CASE\\n WHEN (d_day_name='Tuesday') THEN sales_price\\n ELSE NULL\\n END) tue_sales,\\n sum(CASE\\n WHEN (d_day_name='Wednesday') THEN sales_price\\n ELSE NULL\\n END) wed_sales,\\n sum(CASE\\n WHEN (d_day_name='Thursday') THEN sales_price\\n ELSE NULL\\n END) thu_sales,\\n sum(CASE\\n WHEN (d_day_name='Friday') THEN sales_price\\n ELSE NULL\\n END) fri_sales,\\n sum(CASE\\n WHEN (d_day_name='Saturday') THEN sales_price\\n ELSE NULL\\n END) sat_sales\\n FROM wscs,\\n testdata.tpcds_tiny.date_dim\\n WHERE d_date_sk = sold_date_sk\\n GROUP BY d_week_seq)\\nSELECT d_week_seq1,\\n round(sun_sales1/sun_sales2, 2) r1,\\n round(mon_sales1/mon_sales2, 2) r2,\\n round(tue_sales1/tue_sales2, 2) r3,\\n round(wed_sales1/wed_sales2, 2) r4,\\n round(thu_sales1/thu_sales2, 2) r5,\\n round(fri_sales1/fri_sales2, 2) r6,\\n round(sat_sales1/sat_sales2, 2)\\nFROM\\n (SELECT wswscs.d_week_seq d_week_seq1,\\n sun_sales sun_sales1,\\n mon_sales mon_sales1,\\n tue_sales tue_sales1,\\n wed_sales wed_sales1,\\n thu_sales thu_sales1,\\n fri_sales fri_sales1,\\n sat_sales sat_sales1\\n FROM wswscs,\\n testdata.tpcds_tiny.date_dim\\n WHERE testdata.tpcds_tiny.date_dim.d_week_seq = wswscs.d_week_seq\\n AND d_year = 2001) y,\\n (SELECT wswscs.d_week_seq d_week_seq2,\\n sun_sales sun_sales2,\\n mon_sales mon_sales2,\\n tue_sales tue_sales2,\\n wed_sales wed_sales2,\\n thu_sales thu_sales2,\\n fri_sales fri_sales2,\\n sat_sales sat_sales2\\n FROM wswscs,\\n testdata.tpcds_tiny.date_dim\\n WHERE testdata.tpcds_tiny.date_dim.d_week_seq = wswscs.d_week_seq\\n AND d_year = 2001+1) z\\nWHERE d_week_seq1 = d_week_seq2-53\\nORDER BY d_week_seq1 NULLS FIRST;\",\n", " 'Q03': 'SELECT dt.d_year,\\n testdata.tpcds_tiny.item.i_brand_id brand_id,\\n testdata.tpcds_tiny.item.i_brand brand,\\n sum(ss_ext_sales_price) sum_agg\\nFROM testdata.tpcds_tiny.date_dim dt,\\n testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.item\\nWHERE dt.d_date_sk = testdata.tpcds_tiny.store_sales.ss_sold_date_sk\\n AND testdata.tpcds_tiny.store_sales.ss_item_sk = testdata.tpcds_tiny.item.i_item_sk\\n AND testdata.tpcds_tiny.item.i_manufact_id = 128\\n AND dt.d_moy=11\\nGROUP BY dt.d_year,\\n testdata.tpcds_tiny.item.i_brand,\\n testdata.tpcds_tiny.item.i_brand_id\\nORDER BY dt.d_year,\\n sum_agg DESC,\\n brand_id\\nLIMIT 100;',\n", " 'Q04': \"WITH year_total AS\\n (SELECT c_customer_id customer_id,\\n c_first_name customer_first_name,\\n c_last_name customer_last_name,\\n c_preferred_cust_flag customer_preferred_cust_flag,\\n c_birth_country customer_birth_country,\\n c_login customer_login,\\n c_email_address customer_email_address,\\n d_year dyear,\\n sum(((ss_ext_list_price-ss_ext_wholesale_cost-ss_ext_discount_amt)+ss_ext_sales_price)/2) year_total,\\n 's' sale_type\\n FROM testdata.tpcds_tiny.customer,\\n testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.date_dim\\n WHERE c_customer_sk = ss_customer_sk\\n AND ss_sold_date_sk = d_date_sk\\n GROUP BY c_customer_id,\\n c_first_name,\\n c_last_name,\\n c_preferred_cust_flag,\\n c_birth_country,\\n c_login,\\n c_email_address,\\n d_year\\n UNION ALL SELECT c_customer_id customer_id,\\n c_first_name customer_first_name,\\n c_last_name customer_last_name,\\n c_preferred_cust_flag customer_preferred_cust_flag,\\n c_birth_country customer_birth_country,\\n c_login customer_login,\\n c_email_address customer_email_address,\\n d_year dyear,\\n sum((((cs_ext_list_price-cs_ext_wholesale_cost-cs_ext_discount_amt)+cs_ext_sales_price)/2)) year_total,\\n 'c' sale_type\\n FROM testdata.tpcds_tiny.customer,\\n testdata.tpcds_tiny.catalog_sales,\\n testdata.tpcds_tiny.date_dim\\n WHERE c_customer_sk = cs_bill_customer_sk\\n AND cs_sold_date_sk = d_date_sk\\n GROUP BY c_customer_id,\\n c_first_name,\\n c_last_name,\\n c_preferred_cust_flag,\\n c_birth_country,\\n c_login,\\n c_email_address,\\n d_year\\n UNION ALL SELECT c_customer_id customer_id,\\n c_first_name customer_first_name,\\n c_last_name customer_last_name,\\n c_preferred_cust_flag customer_preferred_cust_flag,\\n c_birth_country customer_birth_country,\\n c_login customer_login,\\n c_email_address customer_email_address,\\n d_year dyear,\\n sum((((ws_ext_list_price-ws_ext_wholesale_cost-ws_ext_discount_amt)+ws_ext_sales_price)/2)) year_total,\\n 'w' sale_type\\n FROM testdata.tpcds_tiny.customer,\\n testdata.tpcds_tiny.web_sales,\\n testdata.tpcds_tiny.date_dim\\n WHERE c_customer_sk = ws_bill_customer_sk\\n AND ws_sold_date_sk = d_date_sk\\n GROUP BY c_customer_id,\\n c_first_name,\\n c_last_name,\\n c_preferred_cust_flag,\\n c_birth_country,\\n c_login,\\n c_email_address,\\n d_year)\\nSELECT t_s_secyear.customer_id,\\n t_s_secyear.customer_first_name,\\n t_s_secyear.customer_last_name,\\n t_s_secyear.customer_preferred_cust_flag\\nFROM year_total t_s_firstyear,\\n year_total t_s_secyear,\\n year_total t_c_firstyear,\\n year_total t_c_secyear,\\n year_total t_w_firstyear,\\n year_total t_w_secyear\\nWHERE t_s_secyear.customer_id = t_s_firstyear.customer_id\\n AND t_s_firstyear.customer_id = t_c_secyear.customer_id\\n AND t_s_firstyear.customer_id = t_c_firstyear.customer_id\\n AND t_s_firstyear.customer_id = t_w_firstyear.customer_id\\n AND t_s_firstyear.customer_id = t_w_secyear.customer_id\\n AND t_s_firstyear.sale_type = 's'\\n AND t_c_firstyear.sale_type = 'c'\\n AND t_w_firstyear.sale_type = 'w'\\n AND t_s_secyear.sale_type = 's'\\n AND t_c_secyear.sale_type = 'c'\\n AND t_w_secyear.sale_type = 'w'\\n AND t_s_firstyear.dyear = 2001\\n AND t_s_secyear.dyear = 2001+1\\n AND t_c_firstyear.dyear = 2001\\n AND t_c_secyear.dyear = 2001+1\\n AND t_w_firstyear.dyear = 2001\\n AND t_w_secyear.dyear = 2001+1\\n AND t_s_firstyear.year_total > 0\\n AND t_c_firstyear.year_total > 0\\n AND t_w_firstyear.year_total > 0\\n AND CASE\\n WHEN t_c_firstyear.year_total > 0 THEN t_c_secyear.year_total / t_c_firstyear.year_total\\n ELSE NULL\\n END > CASE\\n WHEN t_s_firstyear.year_total > 0 THEN t_s_secyear.year_total / t_s_firstyear.year_total\\n ELSE NULL\\n END\\n AND CASE\\n WHEN t_c_firstyear.year_total > 0 THEN t_c_secyear.year_total / t_c_firstyear.year_total\\n ELSE NULL\\n END > CASE\\n WHEN t_w_firstyear.year_total > 0 THEN t_w_secyear.year_total / t_w_firstyear.year_total\\n ELSE NULL\\n END\\nORDER BY t_s_secyear.customer_id NULLS FIRST,\\n t_s_secyear.customer_first_name NULLS FIRST,\\n t_s_secyear.customer_last_name NULLS FIRST,\\n t_s_secyear.customer_preferred_cust_flag NULLS FIRST\\nLIMIT 100;\",\n", " 'Q05': \"WITH ssr AS\\n (SELECT s_store_id,\\n sum(sales_price) AS sales,\\n sum(profit) AS profit,\\n sum(return_amt) AS returns_,\\n sum(net_loss) AS profit_loss\\n FROM\\n (SELECT ss_store_sk AS store_sk,\\n ss_sold_date_sk AS date_sk,\\n ss_ext_sales_price AS sales_price,\\n ss_net_profit AS profit,\\n cast(0 AS decimal(7,2)) AS return_amt,\\n cast(0 AS decimal(7,2)) AS net_loss\\n FROM testdata.tpcds_tiny.store_sales\\n UNION ALL SELECT sr_store_sk AS store_sk,\\n sr_returned_date_sk AS date_sk,\\n cast(0 AS decimal(7,2)) AS sales_price,\\n cast(0 AS decimal(7,2)) AS profit,\\n sr_return_amt AS return_amt,\\n sr_net_loss AS net_loss\\n FROM testdata.tpcds_tiny.store_returns ) salesreturns,\\n testdata.tpcds_tiny.date_dim,\\n testdata.tpcds_tiny.store\\n WHERE date_sk = d_date_sk\\n AND d_date BETWEEN cast('2000-08-23' AS date) AND cast('2000-09-06' AS date)\\n AND store_sk = s_store_sk\\n GROUP BY s_store_id) ,\\n csr AS\\n (SELECT cp_catalog_page_id,\\n sum(sales_price) AS sales,\\n sum(profit) AS profit,\\n sum(return_amt) AS returns_,\\n sum(net_loss) AS profit_loss\\n FROM\\n (SELECT cs_catalog_page_sk AS page_sk,\\n cs_sold_date_sk AS date_sk,\\n cs_ext_sales_price AS sales_price,\\n cs_net_profit AS profit,\\n cast(0 AS decimal(7,2)) AS return_amt,\\n cast(0 AS decimal(7,2)) AS net_loss\\n FROM testdata.tpcds_tiny.catalog_sales\\n UNION ALL SELECT cr_catalog_page_sk AS page_sk,\\n cr_returned_date_sk AS date_sk,\\n cast(0 AS decimal(7,2)) AS sales_price,\\n cast(0 AS decimal(7,2)) AS profit,\\n cr_return_amount AS return_amt,\\n cr_net_loss AS net_loss\\n FROM testdata.tpcds_tiny.catalog_returns ) salesreturns,\\n testdata.tpcds_tiny.date_dim,\\n testdata.tpcds_tiny.catalog_page\\n WHERE date_sk = d_date_sk\\n AND d_date BETWEEN cast('2000-08-23' AS date) AND cast('2000-09-06' AS date)\\n AND page_sk = cp_catalog_page_sk\\n GROUP BY cp_catalog_page_id) ,\\n wsr AS\\n (SELECT web_site_id,\\n sum(sales_price) AS sales,\\n sum(profit) AS profit,\\n sum(return_amt) AS returns_,\\n sum(net_loss) AS profit_loss\\n FROM\\n (SELECT ws_web_site_sk AS wsr_web_site_sk,\\n ws_sold_date_sk AS date_sk,\\n ws_ext_sales_price AS sales_price,\\n ws_net_profit AS profit,\\n cast(0 AS decimal(7,2)) AS return_amt,\\n cast(0 AS decimal(7,2)) AS net_loss\\n FROM testdata.tpcds_tiny.web_sales\\n UNION ALL SELECT ws_web_site_sk AS wsr_web_site_sk,\\n wr_returned_date_sk AS date_sk,\\n cast(0 AS decimal(7,2)) AS sales_price,\\n cast(0 AS decimal(7,2)) AS profit,\\n wr_return_amt AS return_amt,\\n wr_net_loss AS net_loss\\n FROM testdata.tpcds_tiny.web_returns\\n LEFT OUTER JOIN testdata.tpcds_tiny.web_sales ON (wr_item_sk = ws_item_sk\\n AND wr_order_number = ws_order_number) ) salesreturns,\\n testdata.tpcds_tiny.date_dim,\\n testdata.tpcds_tiny.web_site\\n WHERE date_sk = d_date_sk\\n AND d_date BETWEEN cast('2000-08-23' AS date) AND cast('2000-09-06' AS date)\\n AND wsr_web_site_sk = web_site_sk\\n GROUP BY web_site_id)\\nSELECT channel ,\\n id ,\\n sum(sales) AS sales ,\\n sum(returns_) AS returns_ ,\\n sum(profit) AS profit\\nFROM\\n (SELECT 'store channel' AS channel ,\\n concat('store', s_store_id) AS id ,\\n sales ,\\n returns_ ,\\n (profit - profit_loss) AS profit\\n FROM ssr\\n UNION ALL SELECT 'catalog channel' AS channel ,\\n concat('catalog_page', cp_catalog_page_id) AS id ,\\n sales ,\\n returns_ ,\\n (profit - profit_loss) AS profit\\n FROM csr\\n UNION ALL SELECT 'web channel' AS channel ,\\n concat('web_site', web_site_id) AS id ,\\n sales ,\\n returns_ ,\\n (profit - profit_loss) AS profit\\n FROM wsr ) x\\nGROUP BY ROLLUP (channel,\\n id)\\nORDER BY channel NULLS FIRST,\\n id NULLS FIRST\\nLIMIT 100;\",\n", " 'Q06': 'SELECT a.ca_state state,\\n count(*) cnt\\nFROM testdata.tpcds_tiny.customer_address a ,\\n testdata.tpcds_tiny.customer c ,\\n testdata.tpcds_tiny.store_sales s ,\\n testdata.tpcds_tiny.date_dim d ,\\n testdata.tpcds_tiny.item i\\nWHERE a.ca_address_sk = c.c_current_addr_sk\\n AND c.c_customer_sk = s.ss_customer_sk\\n AND s.ss_sold_date_sk = d.d_date_sk\\n AND s.ss_item_sk = i.i_item_sk\\n AND d.d_month_seq =\\n (SELECT DISTINCT (d_month_seq)\\n FROM testdata.tpcds_tiny.date_dim\\n WHERE d_year = 2001\\n AND d_moy = 1 )\\n AND i.i_current_price > 1.2 *\\n (SELECT avg(j.i_current_price)\\n FROM testdata.tpcds_tiny.item j\\n WHERE j.i_category = i.i_category)\\nGROUP BY a.ca_state\\nHAVING count(*) >= 10\\nORDER BY cnt NULLS FIRST,\\n a.ca_state NULLS FIRST\\nLIMIT 100;',\n", " 'Q07': \"SELECT i_item_id,\\n avg(ss_quantity) agg1,\\n avg(ss_list_price) agg2,\\n avg(ss_coupon_amt) agg3,\\n avg(ss_sales_price) agg4\\nFROM testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.customer_demographics,\\n testdata.tpcds_tiny.date_dim,\\n testdata.tpcds_tiny.item,\\n testdata.tpcds_tiny.promotion\\nWHERE ss_sold_date_sk = d_date_sk\\n AND ss_item_sk = i_item_sk\\n AND ss_cdemo_sk = cd_demo_sk\\n AND ss_promo_sk = p_promo_sk\\n AND cd_gender = 'M'\\n AND cd_marital_status = 'S'\\n AND cd_education_status = 'College'\\n AND (p_channel_email = 'N'\\n OR p_channel_event = 'N')\\n AND d_year = 2000\\nGROUP BY i_item_id\\nORDER BY i_item_id\\nLIMIT 100;\",\n", " 'Q08': \"SELECT s_store_name,\\n sum(ss_net_profit)\\nFROM testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.date_dim,\\n testdata.tpcds_tiny.store,\\n (SELECT ca_zip\\n FROM\\n (SELECT SUBSTRING(ca_zip, 1, 5) ca_zip\\n FROM testdata.tpcds_tiny.customer_address\\n WHERE SUBSTRING(ca_zip, 1, 5) IN ('24128',\\n '76232',\\n '65084',\\n '87816',\\n '83926',\\n '77556',\\n '20548',\\n '26231',\\n '43848',\\n '15126',\\n '91137',\\n '61265',\\n '98294',\\n '25782',\\n '17920',\\n '18426',\\n '98235',\\n '40081',\\n '84093',\\n '28577',\\n '55565',\\n '17183',\\n '54601',\\n '67897',\\n '22752',\\n '86284',\\n '18376',\\n '38607',\\n '45200',\\n '21756',\\n '29741',\\n '96765',\\n '23932',\\n '89360',\\n '29839',\\n '25989',\\n '28898',\\n '91068',\\n '72550',\\n '10390',\\n '18845',\\n '47770',\\n '82636',\\n '41367',\\n '76638',\\n '86198',\\n '81312',\\n '37126',\\n '39192',\\n '88424',\\n '72175',\\n '81426',\\n '53672',\\n '10445',\\n '42666',\\n '66864',\\n '66708',\\n '41248',\\n '48583',\\n '82276',\\n '18842',\\n '78890',\\n '49448',\\n '14089',\\n '38122',\\n '34425',\\n '79077',\\n '19849',\\n '43285',\\n '39861',\\n '66162',\\n '77610',\\n '13695',\\n '99543',\\n '83444',\\n '83041',\\n '12305',\\n '57665',\\n '68341',\\n '25003',\\n '57834',\\n '62878',\\n '49130',\\n '81096',\\n '18840',\\n '27700',\\n '23470',\\n '50412',\\n '21195',\\n '16021',\\n '76107',\\n '71954',\\n '68309',\\n '18119',\\n '98359',\\n '64544',\\n '10336',\\n '86379',\\n '27068',\\n '39736',\\n '98569',\\n '28915',\\n '24206',\\n '56529',\\n '57647',\\n '54917',\\n '42961',\\n '91110',\\n '63981',\\n '14922',\\n '36420',\\n '23006',\\n '67467',\\n '32754',\\n '30903',\\n '20260',\\n '31671',\\n '51798',\\n '72325',\\n '85816',\\n '68621',\\n '13955',\\n '36446',\\n '41766',\\n '68806',\\n '16725',\\n '15146',\\n '22744',\\n '35850',\\n '88086',\\n '51649',\\n '18270',\\n '52867',\\n '39972',\\n '96976',\\n '63792',\\n '11376',\\n '94898',\\n '13595',\\n '10516',\\n '90225',\\n '58943',\\n '39371',\\n '94945',\\n '28587',\\n '96576',\\n '57855',\\n '28488',\\n '26105',\\n '83933',\\n '25858',\\n '34322',\\n '44438',\\n '73171',\\n '30122',\\n '34102',\\n '22685',\\n '71256',\\n '78451',\\n '54364',\\n '13354',\\n '45375',\\n '40558',\\n '56458',\\n '28286',\\n '45266',\\n '47305',\\n '69399',\\n '83921',\\n '26233',\\n '11101',\\n '15371',\\n '69913',\\n '35942',\\n '15882',\\n '25631',\\n '24610',\\n '44165',\\n '99076',\\n '33786',\\n '70738',\\n '26653',\\n '14328',\\n '72305',\\n '62496',\\n '22152',\\n '10144',\\n '64147',\\n '48425',\\n '14663',\\n '21076',\\n '18799',\\n '30450',\\n '63089',\\n '81019',\\n '68893',\\n '24996',\\n '51200',\\n '51211',\\n '45692',\\n '92712',\\n '70466',\\n '79994',\\n '22437',\\n '25280',\\n '38935',\\n '71791',\\n '73134',\\n '56571',\\n '14060',\\n '19505',\\n '72425',\\n '56575',\\n '74351',\\n '68786',\\n '51650',\\n '20004',\\n '18383',\\n '76614',\\n '11634',\\n '18906',\\n '15765',\\n '41368',\\n '73241',\\n '76698',\\n '78567',\\n '97189',\\n '28545',\\n '76231',\\n '75691',\\n '22246',\\n '51061',\\n '90578',\\n '56691',\\n '68014',\\n '51103',\\n '94167',\\n '57047',\\n '14867',\\n '73520',\\n '15734',\\n '63435',\\n '25733',\\n '35474',\\n '24676',\\n '94627',\\n '53535',\\n '17879',\\n '15559',\\n '53268',\\n '59166',\\n '11928',\\n '59402',\\n '33282',\\n '45721',\\n '43933',\\n '68101',\\n '33515',\\n '36634',\\n '71286',\\n '19736',\\n '58058',\\n '55253',\\n '67473',\\n '41918',\\n '19515',\\n '36495',\\n '19430',\\n '22351',\\n '77191',\\n '91393',\\n '49156',\\n '50298',\\n '87501',\\n '18652',\\n '53179',\\n '18767',\\n '63193',\\n '23968',\\n '65164',\\n '68880',\\n '21286',\\n '72823',\\n '58470',\\n '67301',\\n '13394',\\n '31016',\\n '70372',\\n '67030',\\n '40604',\\n '24317',\\n '45748',\\n '39127',\\n '26065',\\n '77721',\\n '31029',\\n '31880',\\n '60576',\\n '24671',\\n '45549',\\n '13376',\\n '50016',\\n '33123',\\n '19769',\\n '22927',\\n '97789',\\n '46081',\\n '72151',\\n '15723',\\n '46136',\\n '51949',\\n '68100',\\n '96888',\\n '64528',\\n '14171',\\n '79777',\\n '28709',\\n '11489',\\n '25103',\\n '32213',\\n '78668',\\n '22245',\\n '15798',\\n '27156',\\n '37930',\\n '62971',\\n '21337',\\n '51622',\\n '67853',\\n '10567',\\n '38415',\\n '15455',\\n '58263',\\n '42029',\\n '60279',\\n '37125',\\n '56240',\\n '88190',\\n '50308',\\n '26859',\\n '64457',\\n '89091',\\n '82136',\\n '62377',\\n '36233',\\n '63837',\\n '58078',\\n '17043',\\n '30010',\\n '60099',\\n '28810',\\n '98025',\\n '29178',\\n '87343',\\n '73273',\\n '30469',\\n '64034',\\n '39516',\\n '86057',\\n '21309',\\n '90257',\\n '67875',\\n '40162',\\n '11356',\\n '73650',\\n '61810',\\n '72013',\\n '30431',\\n '22461',\\n '19512',\\n '13375',\\n '55307',\\n '30625',\\n '83849',\\n '68908',\\n '26689',\\n '96451',\\n '38193',\\n '46820',\\n '88885',\\n '84935',\\n '69035',\\n '83144',\\n '47537',\\n '56616',\\n '94983',\\n '48033',\\n '69952',\\n '25486',\\n '61547',\\n '27385',\\n '61860',\\n '58048',\\n '56910',\\n '16807',\\n '17871',\\n '35258',\\n '31387',\\n '35458',\\n '35576') INTERSECT\\n SELECT ca_zip\\n FROM\\n (SELECT SUBSTRING(ca_zip, 1, 5) ca_zip,\\n count(*) cnt\\n FROM testdata.tpcds_tiny.customer_address,\\n testdata.tpcds_tiny.customer\\n WHERE ca_address_sk = c_current_addr_sk\\n AND c_preferred_cust_flag='Y'\\n GROUP BY ca_zip\\n HAVING count(*) > 10)A1)A2) V1\\nWHERE ss_store_sk = s_store_sk\\n AND ss_sold_date_sk = d_date_sk\\n AND d_qoy = 2\\n AND d_year = 1998\\n AND (SUBSTRING(s_zip, 1, 2) = SUBSTRING(V1.ca_zip, 1, 2))\\nGROUP BY s_store_name\\nORDER BY s_store_name\\nLIMIT 100;\",\n", " 'Q09': 'SELECT CASE\\n WHEN\\n (SELECT count(*)\\n FROM testdata.tpcds_tiny.store_sales\\n WHERE ss_quantity BETWEEN 1 AND 20) > 74129 THEN\\n (SELECT avg(ss_ext_discount_amt)\\n FROM testdata.tpcds_tiny.store_sales\\n WHERE ss_quantity BETWEEN 1 AND 20)\\n ELSE\\n (SELECT avg(ss_net_paid)\\n FROM testdata.tpcds_tiny.store_sales\\n WHERE ss_quantity BETWEEN 1 AND 20)\\n END bucket1,\\n CASE\\n WHEN\\n (SELECT count(*)\\n FROM testdata.tpcds_tiny.store_sales\\n WHERE ss_quantity BETWEEN 21 AND 40) > 122840 THEN\\n (SELECT avg(ss_ext_discount_amt)\\n FROM testdata.tpcds_tiny.store_sales\\n WHERE ss_quantity BETWEEN 21 AND 40)\\n ELSE\\n (SELECT avg(ss_net_paid)\\n FROM testdata.tpcds_tiny.store_sales\\n WHERE ss_quantity BETWEEN 21 AND 40)\\n END bucket2,\\n CASE\\n WHEN\\n (SELECT count(*)\\n FROM testdata.tpcds_tiny.store_sales\\n WHERE ss_quantity BETWEEN 41 AND 60) > 56580 THEN\\n (SELECT avg(ss_ext_discount_amt)\\n FROM testdata.tpcds_tiny.store_sales\\n WHERE ss_quantity BETWEEN 41 AND 60)\\n ELSE\\n (SELECT avg(ss_net_paid)\\n FROM testdata.tpcds_tiny.store_sales\\n WHERE ss_quantity BETWEEN 41 AND 60)\\n END bucket3,\\n CASE\\n WHEN\\n (SELECT count(*)\\n FROM testdata.tpcds_tiny.store_sales\\n WHERE ss_quantity BETWEEN 61 AND 80) > 10097 THEN\\n (SELECT avg(ss_ext_discount_amt)\\n FROM testdata.tpcds_tiny.store_sales\\n WHERE ss_quantity BETWEEN 61 AND 80)\\n ELSE\\n (SELECT avg(ss_net_paid)\\n FROM testdata.tpcds_tiny.store_sales\\n WHERE ss_quantity BETWEEN 61 AND 80)\\n END bucket4,\\n CASE\\n WHEN\\n (SELECT count(*)\\n FROM testdata.tpcds_tiny.store_sales\\n WHERE ss_quantity BETWEEN 81 AND 100) > 165306 THEN\\n (SELECT avg(ss_ext_discount_amt)\\n FROM testdata.tpcds_tiny.store_sales\\n WHERE ss_quantity BETWEEN 81 AND 100)\\n ELSE\\n (SELECT avg(ss_net_paid)\\n FROM testdata.tpcds_tiny.store_sales\\n WHERE ss_quantity BETWEEN 81 AND 100)\\n END bucket5\\nFROM testdata.tpcds_tiny.reason\\nWHERE r_reason_sk = 1 ;',\n", " 'Q10': \"SELECT cd_gender,\\n cd_marital_status,\\n cd_education_status,\\n count(*) cnt1,\\n cd_purchase_estimate,\\n count(*) cnt2,\\n cd_credit_rating,\\n count(*) cnt3,\\n cd_dep_count,\\n count(*) cnt4,\\n cd_dep_employed_count,\\n count(*) cnt5,\\n cd_dep_college_count,\\n count(*) cnt6\\nFROM testdata.tpcds_tiny.customer c,\\n testdata.tpcds_tiny.customer_address ca,\\n testdata.tpcds_tiny.customer_demographics\\nWHERE c.c_current_addr_sk = ca.ca_address_sk\\n AND ca_county IN ('Rush County',\\n 'Toole County',\\n 'Jefferson County',\\n 'Dona Ana County',\\n 'La Porte County')\\n AND cd_demo_sk = c.c_current_cdemo_sk\\n AND EXISTS\\n (SELECT *\\n FROM testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.date_dim\\n WHERE c.c_customer_sk = ss_customer_sk\\n AND ss_sold_date_sk = d_date_sk\\n AND d_year = 2002\\n AND d_moy BETWEEN 1 AND 1+3)\\n AND (EXISTS\\n (SELECT *\\n FROM testdata.tpcds_tiny.web_sales,\\n testdata.tpcds_tiny.date_dim\\n WHERE c.c_customer_sk = ws_bill_customer_sk\\n AND ws_sold_date_sk = d_date_sk\\n AND d_year = 2002\\n AND d_moy BETWEEN 1 AND 1+3)\\n OR EXISTS\\n (SELECT *\\n FROM testdata.tpcds_tiny.catalog_sales,\\n testdata.tpcds_tiny.date_dim\\n WHERE c.c_customer_sk = cs_ship_customer_sk\\n AND cs_sold_date_sk = d_date_sk\\n AND d_year = 2002\\n AND d_moy BETWEEN 1 AND 1+3))\\nGROUP BY cd_gender,\\n cd_marital_status,\\n cd_education_status,\\n cd_purchase_estimate,\\n cd_credit_rating,\\n cd_dep_count,\\n cd_dep_employed_count,\\n cd_dep_college_count\\nORDER BY cd_gender,\\n cd_marital_status,\\n cd_education_status,\\n cd_purchase_estimate,\\n cd_credit_rating,\\n cd_dep_count,\\n cd_dep_employed_count,\\n cd_dep_college_count\\nLIMIT 100;\",\n", " 'Q11': \"WITH year_total AS\\n (SELECT c_customer_id customer_id,\\n c_first_name customer_first_name,\\n c_last_name customer_last_name,\\n c_preferred_cust_flag customer_preferred_cust_flag,\\n c_birth_country customer_birth_country,\\n c_login customer_login,\\n c_email_address customer_email_address,\\n d_year dyear,\\n sum(ss_ext_list_price-ss_ext_discount_amt) year_total,\\n 's' sale_type\\n FROM testdata.tpcds_tiny.customer,\\n testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.date_dim\\n WHERE c_customer_sk = ss_customer_sk\\n AND ss_sold_date_sk = d_date_sk\\n GROUP BY c_customer_id,\\n c_first_name,\\n c_last_name,\\n c_preferred_cust_flag,\\n c_birth_country,\\n c_login,\\n c_email_address,\\n d_year\\n UNION ALL SELECT c_customer_id customer_id,\\n c_first_name customer_first_name,\\n c_last_name customer_last_name,\\n c_preferred_cust_flag customer_preferred_cust_flag,\\n c_birth_country customer_birth_country,\\n c_login customer_login,\\n c_email_address customer_email_address,\\n d_year dyear,\\n sum(ws_ext_list_price-ws_ext_discount_amt) year_total,\\n 'w' sale_type\\n FROM testdata.tpcds_tiny.customer,\\n testdata.tpcds_tiny.web_sales,\\n testdata.tpcds_tiny.date_dim\\n WHERE c_customer_sk = ws_bill_customer_sk\\n AND ws_sold_date_sk = d_date_sk\\n GROUP BY c_customer_id,\\n c_first_name,\\n c_last_name,\\n c_preferred_cust_flag,\\n c_birth_country,\\n c_login,\\n c_email_address,\\n d_year)\\nSELECT t_s_secyear.customer_id,\\n t_s_secyear.customer_first_name,\\n t_s_secyear.customer_last_name,\\n t_s_secyear.customer_preferred_cust_flag\\nFROM year_total t_s_firstyear,\\n year_total t_s_secyear,\\n year_total t_w_firstyear,\\n year_total t_w_secyear\\nWHERE t_s_secyear.customer_id = t_s_firstyear.customer_id\\n AND t_s_firstyear.customer_id = t_w_secyear.customer_id\\n AND t_s_firstyear.customer_id = t_w_firstyear.customer_id\\n AND t_s_firstyear.sale_type = 's'\\n AND t_w_firstyear.sale_type = 'w'\\n AND t_s_secyear.sale_type = 's'\\n AND t_w_secyear.sale_type = 'w'\\n AND t_s_firstyear.dyear = 2001\\n AND t_s_secyear.dyear = 2001+1\\n AND t_w_firstyear.dyear = 2001\\n AND t_w_secyear.dyear = 2001+1\\n AND t_s_firstyear.year_total > 0\\n AND t_w_firstyear.year_total > 0\\n AND CASE\\n WHEN t_w_firstyear.year_total > 0 THEN (t_w_secyear.year_total*1.0000) / t_w_firstyear.year_total\\n ELSE 0.0\\n END > CASE\\n WHEN t_s_firstyear.year_total > 0 THEN (t_s_secyear.year_total*1.0000) / t_s_firstyear.year_total\\n ELSE 0.0\\n END\\nORDER BY t_s_secyear.customer_id NULLS FIRST,\\n t_s_secyear.customer_first_name NULLS FIRST,\\n t_s_secyear.customer_last_name NULLS FIRST,\\n t_s_secyear.customer_preferred_cust_flag NULLS FIRST\\nLIMIT 100;\",\n", " 'Q12': \"SELECT i_item_id,\\n i_item_desc,\\n i_category,\\n i_class,\\n i_current_price,\\n sum(ws_ext_sales_price) AS itemrevenue,\\n sum(ws_ext_sales_price)*100.0000/sum(sum(ws_ext_sales_price)) OVER (PARTITION BY i_class) AS revenueratio\\nFROM testdata.tpcds_tiny.web_sales,\\n testdata.tpcds_tiny.item,\\n testdata.tpcds_tiny.date_dim\\nWHERE ws_item_sk = i_item_sk\\n AND i_category IN ('Sports',\\n 'Books',\\n 'Home')\\n AND ws_sold_date_sk = d_date_sk\\n AND d_date BETWEEN cast('1999-02-22' AS date) AND cast('1999-03-24' AS date)\\nGROUP BY i_item_id,\\n i_item_desc,\\n i_category,\\n i_class,\\n i_current_price\\nORDER BY i_category,\\n i_class,\\n i_item_id,\\n i_item_desc,\\n revenueratio\\nLIMIT 100;\",\n", " 'Q13': \"SELECT avg(ss_quantity) avg1,\\n avg(ss_ext_sales_price) avg2,\\n avg(ss_ext_wholesale_cost) avg3,\\n sum(ss_ext_wholesale_cost)\\nFROM testdata.tpcds_tiny.store_sales ,\\n testdata.tpcds_tiny.store ,\\n testdata.tpcds_tiny.customer_demographics ,\\n testdata.tpcds_tiny.household_demographics ,\\n testdata.tpcds_tiny.customer_address ,\\n testdata.tpcds_tiny.date_dim\\nWHERE s_store_sk = ss_store_sk\\n AND ss_sold_date_sk = d_date_sk\\n AND d_year = 2001 and((ss_hdemo_sk=hd_demo_sk\\n AND cd_demo_sk = ss_cdemo_sk\\n AND cd_marital_status = 'M'\\n AND cd_education_status = 'Advanced Degree'\\n AND ss_sales_price BETWEEN 100.00 AND 150.00\\n AND hd_dep_count = 3)\\n OR (ss_hdemo_sk=hd_demo_sk\\n AND cd_demo_sk = ss_cdemo_sk\\n AND cd_marital_status = 'S'\\n AND cd_education_status = 'College'\\n AND ss_sales_price BETWEEN 50.00 AND 100.00\\n AND hd_dep_count = 1 )\\n OR (ss_hdemo_sk=hd_demo_sk\\n AND cd_demo_sk = ss_cdemo_sk\\n AND cd_marital_status = 'W'\\n AND cd_education_status = '2 yr Degree'\\n AND ss_sales_price BETWEEN 150.00 AND 200.00\\n AND hd_dep_count = 1)) and((ss_addr_sk = ca_address_sk\\n AND ca_country = 'United States'\\n AND ca_state IN ('TX', 'OH', 'TX')\\n AND ss_net_profit BETWEEN 100 AND 200)\\n OR (ss_addr_sk = ca_address_sk\\n AND ca_country = 'United States'\\n AND ca_state IN ('OR', 'NM', 'KY')\\n AND ss_net_profit BETWEEN 150 AND 300)\\n OR (ss_addr_sk = ca_address_sk\\n AND ca_country = 'United States'\\n AND ca_state IN ('VA', 'TX', 'MS')\\n AND ss_net_profit BETWEEN 50 AND 250)) ;\",\n", " 'Q14': \"WITH cross_items AS\\n (SELECT i_item_sk ss_item_sk\\n FROM testdata.tpcds_tiny.item,\\n (SELECT iss.i_brand_id brand_id,\\n iss.i_class_id class_id,\\n iss.i_category_id category_id\\n FROM testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.item iss,\\n testdata.tpcds_tiny.date_dim d1\\n WHERE ss_item_sk = iss.i_item_sk\\n AND ss_sold_date_sk = d1.d_date_sk\\n AND d1.d_year BETWEEN 1999 AND 1999 + 2 INTERSECT\\n SELECT ics.i_brand_id,\\n ics.i_class_id,\\n ics.i_category_id\\n FROM testdata.tpcds_tiny.catalog_sales,\\n testdata.tpcds_tiny.item ics,\\n testdata.tpcds_tiny.date_dim d2 WHERE cs_item_sk = ics.i_item_sk\\n AND cs_sold_date_sk = d2.d_date_sk\\n AND d2.d_year BETWEEN 1999 AND 1999 + 2 INTERSECT\\n SELECT iws.i_brand_id,\\n iws.i_class_id,\\n iws.i_category_id\\n FROM testdata.tpcds_tiny.web_sales,\\n testdata.tpcds_tiny.item iws,\\n testdata.tpcds_tiny.date_dim d3 WHERE ws_item_sk = iws.i_item_sk\\n AND ws_sold_date_sk = d3.d_date_sk\\n AND d3.d_year BETWEEN 1999 AND 1999 + 2) sq1\\n WHERE i_brand_id = brand_id\\n AND i_class_id = class_id\\n AND i_category_id = category_id ),\\n avg_sales AS\\n (SELECT avg(quantity*list_price) average_sales\\n FROM\\n (SELECT ss_quantity quantity,\\n ss_list_price list_price\\n FROM testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.date_dim\\n WHERE ss_sold_date_sk = d_date_sk\\n AND d_year BETWEEN 1999 AND 1999 + 2\\n UNION ALL SELECT cs_quantity quantity,\\n cs_list_price list_price\\n FROM testdata.tpcds_tiny.catalog_sales,\\n testdata.tpcds_tiny.date_dim\\n WHERE cs_sold_date_sk = d_date_sk\\n AND d_year BETWEEN 1999 AND 1999 + 2\\n UNION ALL SELECT ws_quantity quantity,\\n ws_list_price list_price\\n FROM testdata.tpcds_tiny.web_sales,\\n testdata.tpcds_tiny.date_dim\\n WHERE ws_sold_date_sk = d_date_sk\\n AND d_year BETWEEN 1999 AND 1999 + 2) sq2)\\nSELECT channel,\\n i_brand_id,\\n i_class_id,\\n i_category_id,\\n sum(sales) AS sum_sales,\\n sum(number_sales) AS sum_number_sales\\nFROM\\n (SELECT 'store' channel,\\n i_brand_id,\\n i_class_id,\\n i_category_id,\\n sum(ss_quantity*ss_list_price) sales,\\n count(*) number_sales\\n FROM testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.item,\\n testdata.tpcds_tiny.date_dim\\n WHERE ss_item_sk IN\\n (SELECT ss_item_sk\\n FROM cross_items)\\n AND ss_item_sk = i_item_sk\\n AND ss_sold_date_sk = d_date_sk\\n AND d_year = 1999+2\\n AND d_moy = 11\\n GROUP BY i_brand_id,\\n i_class_id,\\n i_category_id\\n HAVING sum(ss_quantity*ss_list_price) >\\n (SELECT average_sales\\n FROM avg_sales)\\n UNION ALL SELECT 'catalog' channel,\\n i_brand_id,\\n i_class_id,\\n i_category_id,\\n sum(cs_quantity*cs_list_price) sales,\\n count(*) number_sales\\n FROM testdata.tpcds_tiny.catalog_sales,\\n testdata.tpcds_tiny.item,\\n testdata.tpcds_tiny.date_dim\\n WHERE cs_item_sk IN\\n (SELECT ss_item_sk\\n FROM cross_items)\\n AND cs_item_sk = i_item_sk\\n AND cs_sold_date_sk = d_date_sk\\n AND d_year = 1999+2\\n AND d_moy = 11\\n GROUP BY i_brand_id,\\n i_class_id,\\n i_category_id\\n HAVING sum(cs_quantity*cs_list_price) >\\n (SELECT average_sales\\n FROM avg_sales)\\n UNION ALL SELECT 'web' channel,\\n i_brand_id,\\n i_class_id,\\n i_category_id,\\n sum(ws_quantity*ws_list_price) sales,\\n count(*) number_sales\\n FROM testdata.tpcds_tiny.web_sales,\\n testdata.tpcds_tiny.item,\\n testdata.tpcds_tiny.date_dim\\n WHERE ws_item_sk IN\\n (SELECT ss_item_sk\\n FROM cross_items)\\n AND ws_item_sk = i_item_sk\\n AND ws_sold_date_sk = d_date_sk\\n AND d_year = 1999+2\\n AND d_moy = 11\\n GROUP BY i_brand_id,\\n i_class_id,\\n i_category_id\\n HAVING sum(ws_quantity*ws_list_price) >\\n (SELECT average_sales\\n FROM avg_sales)) y\\nGROUP BY ROLLUP (channel,\\n i_brand_id,\\n i_class_id,\\n i_category_id)\\nORDER BY channel NULLS FIRST,\\n i_brand_id NULLS FIRST,\\n i_class_id NULLS FIRST,\\n i_category_id NULLS FIRST\\nLIMIT 100;\",\n", " 'Q15': \"SELECT ca_zip,\\n sum(cs_sales_price)\\nFROM testdata.tpcds_tiny.catalog_sales,\\n testdata.tpcds_tiny.customer,\\n testdata.tpcds_tiny.customer_address,\\n testdata.tpcds_tiny.date_dim\\nWHERE cs_bill_customer_sk = c_customer_sk\\n AND c_current_addr_sk = ca_address_sk\\n AND (SUBSTRING(ca_zip, 1, 5) IN ('85669',\\n '86197',\\n '88274',\\n '83405',\\n '86475',\\n '85392',\\n '85460',\\n '80348',\\n '81792')\\n OR ca_state IN ('CA',\\n 'WA',\\n 'GA')\\n OR cs_sales_price > 500)\\n AND cs_sold_date_sk = d_date_sk\\n AND d_qoy = 2\\n AND d_year = 2001\\nGROUP BY ca_zip\\nORDER BY ca_zip NULLS FIRST\\nLIMIT 100;\",\n", " 'Q16': 'SELECT count(DISTINCT cs_order_number) AS \"order count\",\\n sum(cs_ext_ship_cost) AS \"total shipping cost\",\\n sum(cs_net_profit) AS \"total net profit\"\\nFROM testdata.tpcds_tiny.catalog_sales cs1,\\n testdata.tpcds_tiny.date_dim,\\n testdata.tpcds_tiny.customer_address,\\n testdata.tpcds_tiny.call_center\\nWHERE d_date BETWEEN cast(\\'2002-02-01\\' AS date) AND cast(\\'2002-04-02\\' AS date)\\n AND cs1.cs_ship_date_sk = d_date_sk\\n AND cs1.cs_ship_addr_sk = ca_address_sk\\n AND ca_state = \\'GA\\'\\n AND cs1.cs_call_center_sk = cc_call_center_sk\\n AND cc_county = \\'Williamson County\\'\\n AND EXISTS\\n (SELECT *\\n FROM testdata.tpcds_tiny.catalog_sales cs2\\n WHERE cs1.cs_order_number = cs2.cs_order_number\\n AND cs1.cs_warehouse_sk <> cs2.cs_warehouse_sk)\\n AND NOT EXISTS\\n (SELECT *\\n FROM testdata.tpcds_tiny.catalog_returns cr1\\n WHERE cs1.cs_order_number = cr1.cr_order_number)\\nORDER BY count(DISTINCT cs_order_number)\\nLIMIT 100;',\n", " 'Q17': \"SELECT i_item_id,\\n i_item_desc,\\n s_state,\\n count(ss_quantity) AS store_sales_quantitycount,\\n avg(ss_quantity) AS store_sales_quantityave,\\n stddev_samp(ss_quantity) AS store_sales_quantitystdev,\\n stddev_samp(ss_quantity)/avg(ss_quantity) AS store_sales_quantitycov,\\n count(sr_return_quantity) AS store_returns_quantitycount,\\n avg(sr_return_quantity) AS store_returns_quantityave,\\n stddev_samp(sr_return_quantity) AS store_returns_quantitystdev,\\n stddev_samp(sr_return_quantity)/avg(sr_return_quantity) AS store_returns_quantitycov,\\n count(cs_quantity) AS catalog_sales_quantitycount,\\n avg(cs_quantity) AS catalog_sales_quantityave,\\n stddev_samp(cs_quantity) AS catalog_sales_quantitystdev,\\n stddev_samp(cs_quantity)/avg(cs_quantity) AS catalog_sales_quantitycov\\nFROM testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.store_returns,\\n testdata.tpcds_tiny.catalog_sales,\\n testdata.tpcds_tiny.date_dim d1,\\n testdata.tpcds_tiny.date_dim d2,\\n testdata.tpcds_tiny.date_dim d3,\\n testdata.tpcds_tiny.store,\\n testdata.tpcds_tiny.item\\nWHERE d1.d_quarter_name = '2001Q1'\\n AND d1.d_date_sk = ss_sold_date_sk\\n AND i_item_sk = ss_item_sk\\n AND s_store_sk = ss_store_sk\\n AND ss_customer_sk = sr_customer_sk\\n AND ss_item_sk = sr_item_sk\\n AND ss_ticket_number = sr_ticket_number\\n AND sr_returned_date_sk = d2.d_date_sk\\n AND d2.d_quarter_name IN ('2001Q1',\\n '2001Q2',\\n '2001Q3')\\n AND sr_customer_sk = cs_bill_customer_sk\\n AND sr_item_sk = cs_item_sk\\n AND cs_sold_date_sk = d3.d_date_sk\\n AND d3.d_quarter_name IN ('2001Q1',\\n '2001Q2',\\n '2001Q3')\\nGROUP BY i_item_id,\\n i_item_desc,\\n s_state\\nORDER BY i_item_id NULLS FIRST,\\n i_item_desc NULLS FIRST,\\n s_state NULLS FIRST\\nLIMIT 100;\",\n", " 'Q18': \"SELECT i_item_id,\\n ca_country,\\n ca_state,\\n ca_county,\\n avg(cast(cs_quantity AS decimal(12, 2))) agg1,\\n avg(cast(cs_list_price AS decimal(12, 2))) agg2,\\n avg(cast(cs_coupon_amt AS decimal(12, 2))) agg3,\\n avg(cast(cs_sales_price AS decimal(12, 2))) agg4,\\n avg(cast(cs_net_profit AS decimal(12, 2))) agg5,\\n avg(cast(c_birth_year AS decimal(12, 2))) agg6,\\n avg(cast(cd1.cd_dep_count AS decimal(12, 2))) agg7\\nFROM testdata.tpcds_tiny.catalog_sales,\\n testdata.tpcds_tiny.customer_demographics cd1,\\n testdata.tpcds_tiny.customer_demographics cd2,\\n testdata.tpcds_tiny.customer,\\n testdata.tpcds_tiny.customer_address,\\n testdata.tpcds_tiny.date_dim,\\n testdata.tpcds_tiny.item\\nWHERE cs_sold_date_sk = d_date_sk\\n AND cs_item_sk = i_item_sk\\n AND cs_bill_cdemo_sk = cd1.cd_demo_sk\\n AND cs_bill_customer_sk = c_customer_sk\\n AND cd1.cd_gender = 'F'\\n AND cd1.cd_education_status = 'Unknown'\\n AND c_current_cdemo_sk = cd2.cd_demo_sk\\n AND c_current_addr_sk = ca_address_sk\\n AND c_birth_month IN (1,\\n 6,\\n 8,\\n 9,\\n 12,\\n 2)\\n AND d_year = 1998\\n AND ca_state IN ('MS',\\n 'IN',\\n 'ND',\\n 'OK',\\n 'NM',\\n 'VA',\\n 'MS')\\nGROUP BY ROLLUP (i_item_id,\\n ca_country,\\n ca_state,\\n ca_county)\\nORDER BY ca_country NULLS FIRST,\\n ca_state NULLS FIRST,\\n ca_county NULLS FIRST,\\n i_item_id NULLS FIRST\\nLIMIT 100;\",\n", " 'Q19': 'SELECT i_brand_id brand_id,\\n i_brand brand,\\n i_manufact_id,\\n i_manufact,\\n sum(ss_ext_sales_price) ext_price\\nFROM testdata.tpcds_tiny.date_dim,\\n testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.item,\\n testdata.tpcds_tiny.customer,\\n testdata.tpcds_tiny.customer_address,\\n testdata.tpcds_tiny.store\\nWHERE d_date_sk = ss_sold_date_sk\\n AND ss_item_sk = i_item_sk\\n AND i_manager_id=8\\n AND d_moy=11\\n AND d_year=1998\\n AND ss_customer_sk = c_customer_sk\\n AND c_current_addr_sk = ca_address_sk\\n AND SUBSTRING(ca_zip, 1, 5) <> SUBSTRING(s_zip, 1, 5)\\n AND ss_store_sk = s_store_sk\\nGROUP BY i_brand,\\n i_brand_id,\\n i_manufact_id,\\n i_manufact\\nORDER BY ext_price DESC,\\n i_brand,\\n i_brand_id,\\n i_manufact_id,\\n i_manufact\\nLIMIT 100 ;',\n", " 'Q20': \"SELECT i_item_id ,\\n i_item_desc,\\n i_category,\\n i_class,\\n i_current_price ,\\n sum(cs_ext_sales_price) AS itemrevenue,\\n sum(cs_ext_sales_price)*100.0000/sum(sum(cs_ext_sales_price)) OVER (PARTITION BY i_class) AS revenueratio\\nFROM testdata.tpcds_tiny.catalog_sales ,\\n testdata.tpcds_tiny.item,\\n testdata.tpcds_tiny.date_dim\\nWHERE cs_item_sk = i_item_sk\\n AND i_category IN ('Sports',\\n 'Books',\\n 'Home')\\n AND cs_sold_date_sk = d_date_sk\\n AND d_date BETWEEN cast('1999-02-22' AS date) AND cast('1999-03-24' AS date)\\nGROUP BY i_item_id ,\\n i_item_desc,\\n i_category ,\\n i_class ,\\n i_current_price\\nORDER BY i_category NULLS FIRST,\\n i_class NULLS FIRST,\\n i_item_id NULLS FIRST,\\n i_item_desc NULLS FIRST,\\n revenueratio NULLS FIRST\\nLIMIT 100;\",\n", " 'Q21': \"SELECT *\\nFROM\\n (SELECT w_warehouse_name,\\n i_item_id,\\n sum(CASE\\n WHEN (cast(d_date AS date) < CAST ('2000-03-11' AS date)) THEN inv_quantity_on_hand\\n ELSE 0\\n END) AS inv_before,\\n sum(CASE\\n WHEN (cast(d_date AS date) >= CAST ('2000-03-11' AS date)) THEN inv_quantity_on_hand\\n ELSE 0\\n END) AS inv_after\\n FROM testdata.tpcds_tiny.inventory,\\n testdata.tpcds_tiny.warehouse,\\n testdata.tpcds_tiny.item,\\n testdata.tpcds_tiny.date_dim\\n WHERE i_current_price BETWEEN 0.99 AND 1.49\\n AND i_item_sk = inv_item_sk\\n AND inv_warehouse_sk = w_warehouse_sk\\n AND inv_date_sk = d_date_sk\\n AND d_date BETWEEN CAST ('2000-02-10' AS date) AND CAST ('2000-04-10' AS date)\\n GROUP BY w_warehouse_name,\\n i_item_id) x\\nWHERE (CASE\\n WHEN inv_before > 0 THEN (inv_after*1.000) / inv_before\\n ELSE NULL\\n END) BETWEEN 2.000/3.000 AND 3.000/2.000\\nORDER BY w_warehouse_name NULLS FIRST,\\n i_item_id NULLS FIRST\\nLIMIT 100;\",\n", " 'Q22': 'SELECT i_product_name ,\\n i_brand ,\\n i_class ,\\n i_category ,\\n avg(inv_quantity_on_hand) qoh\\nFROM testdata.tpcds_tiny.inventory ,\\n testdata.tpcds_tiny.date_dim ,\\n testdata.tpcds_tiny.item\\nWHERE inv_date_sk=d_date_sk\\n AND inv_item_sk=i_item_sk\\n AND d_month_seq BETWEEN 1200 AND 1200 + 11\\nGROUP BY rollup(i_product_name ,i_brand ,i_class ,i_category)\\nORDER BY qoh NULLS FIRST,\\n i_product_name NULLS FIRST,\\n i_brand NULLS FIRST,\\n i_class NULLS FIRST,\\n i_category NULLS FIRST\\nLIMIT 100;',\n", " 'Q23': 'WITH frequent_ss_items AS\\n (SELECT itemdesc,\\n i_item_sk item_sk,\\n d_date solddate,\\n count(*) cnt\\n FROM testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.date_dim,\\n (SELECT testdata.tpcds_tiny.item.*,\\n SUBSTRING(i_item_desc, 1, 30) itemdesc\\n FROM testdata.tpcds_tiny.item) sq1\\n WHERE ss_sold_date_sk = d_date_sk\\n AND ss_item_sk = i_item_sk\\n AND d_year IN (2000,\\n 2000+1,\\n 2000+2,\\n 2000+3)\\n GROUP BY itemdesc,\\n i_item_sk,\\n d_date\\n HAVING count(*) >4),\\n max_store_sales AS\\n (SELECT max(csales) tpcds_cmax\\n FROM\\n (SELECT c_customer_sk,\\n sum(ss_quantity*ss_sales_price) csales\\n FROM testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.customer,\\n testdata.tpcds_tiny.date_dim\\n WHERE ss_customer_sk = c_customer_sk\\n AND ss_sold_date_sk = d_date_sk\\n AND d_year IN (2000,\\n 2000+1,\\n 2000+2,\\n 2000+3)\\n GROUP BY c_customer_sk) sq2),\\n best_ss_customer AS\\n (SELECT c_customer_sk,\\n sum(ss_quantity*ss_sales_price) ssales\\n FROM testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.customer,\\n max_store_sales\\n WHERE ss_customer_sk = c_customer_sk\\n GROUP BY c_customer_sk\\n HAVING sum(ss_quantity*ss_sales_price) > (50/100.0) * max(tpcds_cmax))\\nSELECT c_last_name,\\n c_first_name,\\n sales\\nFROM\\n (SELECT c_last_name,\\n c_first_name,\\n sum(cs_quantity*cs_list_price) sales\\n FROM testdata.tpcds_tiny.catalog_sales,\\n testdata.tpcds_tiny.customer,\\n testdata.tpcds_tiny.date_dim,\\n frequent_ss_items,\\n best_ss_customer\\n WHERE d_year = 2000\\n AND d_moy = 2\\n AND cs_sold_date_sk = d_date_sk\\n AND cs_item_sk = item_sk\\n AND cs_bill_customer_sk = best_ss_customer.c_customer_sk\\n AND cs_bill_customer_sk = testdata.tpcds_tiny.customer.c_customer_sk\\n GROUP BY c_last_name,\\n c_first_name\\n UNION ALL SELECT c_last_name,\\n c_first_name,\\n sum(ws_quantity*ws_list_price) sales\\n FROM testdata.tpcds_tiny.web_sales,\\n testdata.tpcds_tiny.customer,\\n testdata.tpcds_tiny.date_dim,\\n frequent_ss_items,\\n best_ss_customer\\n WHERE d_year = 2000\\n AND d_moy = 2\\n AND ws_sold_date_sk = d_date_sk\\n AND ws_item_sk = item_sk\\n AND ws_bill_customer_sk = best_ss_customer.c_customer_sk\\n AND ws_bill_customer_sk = testdata.tpcds_tiny.customer.c_customer_sk\\n GROUP BY c_last_name,\\n c_first_name) sq3\\nORDER BY c_last_name NULLS FIRST,\\n c_first_name NULLS FIRST,\\n sales NULLS FIRST\\nLIMIT 100;',\n", " 'Q24': \"WITH ssales AS\\n (SELECT c_last_name,\\n c_first_name,\\n s_store_name,\\n ca_state,\\n s_state,\\n i_color,\\n i_current_price,\\n i_manager_id,\\n i_units,\\n i_size,\\n sum(ss_net_paid) netpaid\\n FROM testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.store_returns,\\n testdata.tpcds_tiny.store,\\n testdata.tpcds_tiny.item,\\n testdata.tpcds_tiny.customer,\\n testdata.tpcds_tiny.customer_address\\n WHERE ss_ticket_number = sr_ticket_number\\n AND ss_item_sk = sr_item_sk\\n AND ss_customer_sk = c_customer_sk\\n AND ss_item_sk = i_item_sk\\n AND ss_store_sk = s_store_sk\\n AND c_current_addr_sk = ca_address_sk\\n AND c_birth_country <> upper(ca_country)\\n AND s_zip = ca_zip\\n AND s_market_id=8\\n GROUP BY c_last_name,\\n c_first_name,\\n s_store_name,\\n ca_state,\\n s_state,\\n i_color,\\n i_current_price,\\n i_manager_id,\\n i_units,\\n i_size)\\nSELECT c_last_name,\\n c_first_name,\\n s_store_name,\\n sum(netpaid) paid\\nFROM ssales\\nWHERE i_color = 'peach'\\nGROUP BY c_last_name,\\n c_first_name,\\n s_store_name\\nHAVING sum(netpaid) >\\n (SELECT 0.05*avg(netpaid)\\n FROM ssales)\\nORDER BY c_last_name,\\n c_first_name,\\n s_store_name ;\",\n", " 'Q25': 'SELECT i_item_id ,\\n i_item_desc ,\\n s_store_id ,\\n s_store_name ,\\n sum(ss_net_profit) AS store_sales_profit ,\\n sum(sr_net_loss) AS store_returns_loss ,\\n sum(cs_net_profit) AS catalog_sales_profit\\nFROM testdata.tpcds_tiny.store_sales ,\\n testdata.tpcds_tiny.store_returns ,\\n testdata.tpcds_tiny.catalog_sales ,\\n testdata.tpcds_tiny.date_dim d1 ,\\n testdata.tpcds_tiny.date_dim d2 ,\\n testdata.tpcds_tiny.date_dim d3 ,\\n testdata.tpcds_tiny.store ,\\n testdata.tpcds_tiny.item\\nWHERE d1.d_moy = 4\\n AND d1.d_year = 2001\\n AND d1.d_date_sk = ss_sold_date_sk\\n AND i_item_sk = ss_item_sk\\n AND s_store_sk = ss_store_sk\\n AND ss_customer_sk = sr_customer_sk\\n AND ss_item_sk = sr_item_sk\\n AND ss_ticket_number = sr_ticket_number\\n AND sr_returned_date_sk = d2.d_date_sk\\n AND d2.d_moy BETWEEN 4 AND 10\\n AND d2.d_year = 2001\\n AND sr_customer_sk = cs_bill_customer_sk\\n AND sr_item_sk = cs_item_sk\\n AND cs_sold_date_sk = d3.d_date_sk\\n AND d3.d_moy BETWEEN 4 AND 10\\n AND d3.d_year = 2001\\nGROUP BY i_item_id ,\\n i_item_desc ,\\n s_store_id ,\\n s_store_name\\nORDER BY i_item_id ,\\n i_item_desc ,\\n s_store_id ,\\n s_store_name\\nLIMIT 100;',\n", " 'Q26': \"SELECT i_item_id,\\n avg(cs_quantity) agg1,\\n avg(cs_list_price) agg2,\\n avg(cs_coupon_amt) agg3,\\n avg(cs_sales_price) agg4\\nFROM testdata.tpcds_tiny.catalog_sales,\\n testdata.tpcds_tiny.customer_demographics,\\n testdata.tpcds_tiny.date_dim,\\n testdata.tpcds_tiny.item,\\n testdata.tpcds_tiny.promotion\\nWHERE cs_sold_date_sk = d_date_sk\\n AND cs_item_sk = i_item_sk\\n AND cs_bill_cdemo_sk = cd_demo_sk\\n AND cs_promo_sk = p_promo_sk\\n AND cd_gender = 'M'\\n AND cd_marital_status = 'S'\\n AND cd_education_status = 'College'\\n AND (p_channel_email = 'N'\\n OR p_channel_event = 'N')\\n AND d_year = 2000\\nGROUP BY i_item_id\\nORDER BY i_item_id\\nLIMIT 100;\",\n", " 'Q27': \"WITH results AS\\n (SELECT i_item_id,\\n s_state,\\n 0 AS g_state,\\n ss_quantity agg1,\\n ss_list_price agg2,\\n ss_coupon_amt agg3,\\n ss_sales_price agg4\\n FROM testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.customer_demographics,\\n testdata.tpcds_tiny.date_dim,\\n testdata.tpcds_tiny.store,\\n testdata.tpcds_tiny.item\\n WHERE ss_sold_date_sk = d_date_sk\\n AND ss_item_sk = i_item_sk\\n AND ss_store_sk = s_store_sk\\n AND ss_cdemo_sk = cd_demo_sk\\n AND cd_gender = 'M'\\n AND cd_marital_status = 'S'\\n AND cd_education_status = 'College'\\n AND d_year = 2002\\n AND s_state = 'TN' )\\nSELECT i_item_id,\\n s_state,\\n g_state,\\n agg1,\\n agg2,\\n agg3,\\n agg4\\nFROM\\n ( SELECT i_item_id,\\n s_state,\\n 0 AS g_state,\\n avg(agg1) agg1,\\n avg(agg2) agg2,\\n avg(agg3) agg3,\\n avg(agg4) agg4\\n FROM results\\n GROUP BY i_item_id ,\\n s_state\\n UNION ALL SELECT i_item_id,\\n NULL AS s_state,\\n 1 AS g_state,\\n avg(agg1) agg1,\\n avg(agg2) agg2,\\n avg(agg3) agg3,\\n avg(agg4) agg4\\n FROM results\\n GROUP BY i_item_id\\n UNION ALL SELECT NULL AS i_item_id,\\n NULL AS s_state,\\n 1 AS g_state,\\n avg(agg1) agg1,\\n avg(agg2) agg2,\\n avg(agg3) agg3,\\n avg(agg4) agg4\\n FROM results ) foo\\nORDER BY i_item_id NULLS FIRST,\\n s_state NULLS FIRST\\nLIMIT 100;\",\n", " 'Q28': 'SELECT *\\nFROM\\n (SELECT avg(ss_list_price) B1_LP,\\n count(ss_list_price) B1_CNT,\\n count(DISTINCT ss_list_price) B1_CNTD\\n FROM testdata.tpcds_tiny.store_sales\\n WHERE ss_quantity BETWEEN 0 AND 5\\n AND (ss_list_price BETWEEN 8 AND 8+10\\n OR ss_coupon_amt BETWEEN 459 AND 459+1000\\n OR ss_wholesale_cost BETWEEN 57 AND 57+20)) B1,\\n (SELECT avg(ss_list_price) B2_LP,\\n count(ss_list_price) B2_CNT,\\n count(DISTINCT ss_list_price) B2_CNTD\\n FROM testdata.tpcds_tiny.store_sales\\n WHERE ss_quantity BETWEEN 6 AND 10\\n AND (ss_list_price BETWEEN 90 AND 90+10\\n OR ss_coupon_amt BETWEEN 2323 AND 2323+1000\\n OR ss_wholesale_cost BETWEEN 31 AND 31+20)) B2,\\n (SELECT avg(ss_list_price) B3_LP,\\n count(ss_list_price) B3_CNT,\\n count(DISTINCT ss_list_price) B3_CNTD\\n FROM testdata.tpcds_tiny.store_sales\\n WHERE ss_quantity BETWEEN 11 AND 15\\n AND (ss_list_price BETWEEN 142 AND 142+10\\n OR ss_coupon_amt BETWEEN 12214 AND 12214+1000\\n OR ss_wholesale_cost BETWEEN 79 AND 79+20)) B3,\\n (SELECT avg(ss_list_price) B4_LP,\\n count(ss_list_price) B4_CNT,\\n count(DISTINCT ss_list_price) B4_CNTD\\n FROM testdata.tpcds_tiny.store_sales\\n WHERE ss_quantity BETWEEN 16 AND 20\\n AND (ss_list_price BETWEEN 135 AND 135+10\\n OR ss_coupon_amt BETWEEN 6071 AND 6071+1000\\n OR ss_wholesale_cost BETWEEN 38 AND 38+20)) B4,\\n (SELECT avg(ss_list_price) B5_LP,\\n count(ss_list_price) B5_CNT,\\n count(DISTINCT ss_list_price) B5_CNTD\\n FROM testdata.tpcds_tiny.store_sales\\n WHERE ss_quantity BETWEEN 21 AND 25\\n AND (ss_list_price BETWEEN 122 AND 122+10\\n OR ss_coupon_amt BETWEEN 836 AND 836+1000\\n OR ss_wholesale_cost BETWEEN 17 AND 17+20)) B5,\\n (SELECT avg(ss_list_price) B6_LP,\\n count(ss_list_price) B6_CNT,\\n count(DISTINCT ss_list_price) B6_CNTD\\n FROM testdata.tpcds_tiny.store_sales\\n WHERE ss_quantity BETWEEN 26 AND 30\\n AND (ss_list_price BETWEEN 154 AND 154+10\\n OR ss_coupon_amt BETWEEN 7326 AND 7326+1000\\n OR ss_wholesale_cost BETWEEN 7 AND 7+20)) B6\\nLIMIT 100;',\n", " 'Q29': 'SELECT i_item_id,\\n i_item_desc,\\n s_store_id,\\n s_store_name,\\n sum(ss_quantity) AS store_sales_quantity,\\n sum(sr_return_quantity) AS store_returns_quantity,\\n sum(cs_quantity) AS catalog_sales_quantity\\nFROM testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.store_returns,\\n testdata.tpcds_tiny.catalog_sales,\\n testdata.tpcds_tiny.date_dim d1,\\n testdata.tpcds_tiny.date_dim d2,\\n testdata.tpcds_tiny.date_dim d3,\\n testdata.tpcds_tiny.store,\\n testdata.tpcds_tiny.item\\nWHERE d1.d_moy = 9\\n AND d1.d_year = 1999\\n AND d1.d_date_sk = ss_sold_date_sk\\n AND i_item_sk = ss_item_sk\\n AND s_store_sk = ss_store_sk\\n AND ss_customer_sk = sr_customer_sk\\n AND ss_item_sk = sr_item_sk\\n AND ss_ticket_number = sr_ticket_number\\n AND sr_returned_date_sk = d2.d_date_sk\\n AND d2.d_moy BETWEEN 9 AND 9 + 3\\n AND d2.d_year = 1999\\n AND sr_customer_sk = cs_bill_customer_sk\\n AND sr_item_sk = cs_item_sk\\n AND cs_sold_date_sk = d3.d_date_sk\\n AND d3.d_year IN (1999,\\n 1999+1,\\n 1999+2)\\nGROUP BY i_item_id,\\n i_item_desc,\\n s_store_id,\\n s_store_name\\nORDER BY i_item_id,\\n i_item_desc,\\n s_store_id,\\n s_store_name\\nLIMIT 100;',\n", " 'Q30': \"WITH customer_total_return AS\\n (SELECT wr_returning_customer_sk AS ctr_customer_sk,\\n ca_state AS ctr_state,\\n sum(wr_return_amt) AS ctr_total_return\\n FROM testdata.tpcds_tiny.web_returns,\\n testdata.tpcds_tiny.date_dim,\\n testdata.tpcds_tiny.customer_address\\n WHERE wr_returned_date_sk = d_date_sk\\n AND d_year = 2002\\n AND wr_returning_addr_sk = ca_address_sk\\n GROUP BY wr_returning_customer_sk,\\n ca_state)\\nSELECT c_customer_id,\\n c_salutation,\\n c_first_name,\\n c_last_name,\\n c_preferred_cust_flag,\\n c_birth_day,\\n c_birth_month,\\n c_birth_year,\\n c_birth_country,\\n c_login,\\n c_email_address,\\n c_last_review_date_sk,\\n ctr_total_return\\nFROM customer_total_return ctr1,\\n testdata.tpcds_tiny.customer_address,\\n testdata.tpcds_tiny.customer\\nWHERE ctr1.ctr_total_return >\\n (SELECT avg(ctr_total_return)*1.2\\n FROM customer_total_return ctr2\\n WHERE ctr1.ctr_state = ctr2.ctr_state)\\n AND ca_address_sk = c_current_addr_sk\\n AND ca_state = 'GA'\\n AND ctr1.ctr_customer_sk = c_customer_sk\\nORDER BY c_customer_id NULLS FIRST,\\n c_salutation NULLS FIRST,\\n c_first_name NULLS FIRST,\\n c_last_name NULLS FIRST,\\n c_preferred_cust_flag NULLS FIRST,\\n c_birth_day NULLS FIRST,\\n c_birth_month NULLS FIRST,\\n c_birth_year NULLS FIRST,\\n c_birth_country NULLS FIRST,\\n c_login NULLS FIRST,\\n c_email_address NULLS FIRST,\\n c_last_review_date_sk NULLS FIRST,\\n ctr_total_return NULLS FIRST\\nLIMIT 100;\",\n", " 'Q31': 'WITH ss AS\\n (SELECT ca_county,\\n d_qoy,\\n d_year,\\n sum(ss_ext_sales_price) AS store_sales\\n FROM testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.date_dim,\\n testdata.tpcds_tiny.customer_address\\n WHERE ss_sold_date_sk = d_date_sk\\n AND ss_addr_sk=ca_address_sk\\n GROUP BY ca_county,\\n d_qoy,\\n d_year),\\n ws AS\\n (SELECT ca_county,\\n d_qoy,\\n d_year,\\n sum(ws_ext_sales_price) AS web_sales\\n FROM testdata.tpcds_tiny.web_sales,\\n testdata.tpcds_tiny.date_dim,\\n testdata.tpcds_tiny.customer_address\\n WHERE ws_sold_date_sk = d_date_sk\\n AND ws_bill_addr_sk=ca_address_sk\\n GROUP BY ca_county,\\n d_qoy,\\n d_year)\\nSELECT ss1.ca_county ,\\n ss1.d_year ,\\n (ws2.web_sales*1.0000)/ws1.web_sales web_q1_q2_increase ,\\n (ss2.store_sales*1.0000)/ss1.store_sales store_q1_q2_increase ,\\n (ws3.web_sales*1.0000)/ws2.web_sales web_q2_q3_increase ,\\n (ss3.store_sales*1.0000)/ss2.store_sales store_q2_q3_increase\\nFROM ss ss1 ,\\n ss ss2 ,\\n ss ss3 ,\\n ws ws1 ,\\n ws ws2 ,\\n ws ws3\\nWHERE ss1.d_qoy = 1\\n AND ss1.d_year = 2000\\n AND ss1.ca_county = ss2.ca_county\\n AND ss2.d_qoy = 2\\n AND ss2.d_year = 2000\\n AND ss2.ca_county = ss3.ca_county\\n AND ss3.d_qoy = 3\\n AND ss3.d_year = 2000\\n AND ss1.ca_county = ws1.ca_county\\n AND ws1.d_qoy = 1\\n AND ws1.d_year = 2000\\n AND ws1.ca_county = ws2.ca_county\\n AND ws2.d_qoy = 2\\n AND ws2.d_year = 2000\\n AND ws1.ca_county = ws3.ca_county\\n AND ws3.d_qoy = 3\\n AND ws3.d_year = 2000\\n AND CASE\\n WHEN ws1.web_sales > 0 THEN (ws2.web_sales*1.0000)/ws1.web_sales\\n ELSE NULL\\n END > CASE\\n WHEN ss1.store_sales > 0 THEN (ss2.store_sales*1.0000)/ss1.store_sales\\n ELSE NULL\\n END\\n AND CASE\\n WHEN ws2.web_sales > 0 THEN (ws3.web_sales*1.0000)/ws2.web_sales\\n ELSE NULL\\n END > CASE\\n WHEN ss2.store_sales > 0 THEN (ss3.store_sales*1.0000)/ss2.store_sales\\n ELSE NULL\\n END\\nORDER BY ss1.ca_county;',\n", " 'Q32': 'SELECT sum(cs_ext_discount_amt) AS \"excess discount amount\"\\nFROM testdata.tpcds_tiny.catalog_sales ,\\n testdata.tpcds_tiny.item ,\\n testdata.tpcds_tiny.date_dim\\nWHERE i_manufact_id = 977\\n AND i_item_sk = cs_item_sk\\n AND d_date BETWEEN cast(\\'2000-01-27\\' AS date) AND cast(\\'2000-04-26\\' AS date)\\n AND d_date_sk = cs_sold_date_sk\\n AND cs_ext_discount_amt >\\n ( SELECT 1.3 * avg(cs_ext_discount_amt)\\n FROM testdata.tpcds_tiny.catalog_sales ,\\n testdata.tpcds_tiny.date_dim\\n WHERE cs_item_sk = i_item_sk\\n AND d_date BETWEEN cast(\\'2000-01-27\\' AS date) AND cast(\\'2000-04-26\\' AS date)\\n AND d_date_sk = cs_sold_date_sk )\\nLIMIT 100;',\n", " 'Q33': \"WITH ss AS\\n ( SELECT i_manufact_id,\\n sum(ss_ext_sales_price) total_sales\\n FROM testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.date_dim,\\n testdata.tpcds_tiny.customer_address,\\n testdata.tpcds_tiny.item\\n WHERE i_manufact_id IN\\n (SELECT i_manufact_id\\n FROM testdata.tpcds_tiny.item\\n WHERE i_category IN ('Electronics'))\\n AND ss_item_sk = i_item_sk\\n AND ss_sold_date_sk = d_date_sk\\n AND d_year = 1998\\n AND d_moy = 5\\n AND ss_addr_sk = ca_address_sk\\n AND ca_gmt_offset = -5\\n GROUP BY i_manufact_id),\\n cs AS\\n ( SELECT i_manufact_id,\\n sum(cs_ext_sales_price) total_sales\\n FROM testdata.tpcds_tiny.catalog_sales,\\n testdata.tpcds_tiny.date_dim,\\n testdata.tpcds_tiny.customer_address,\\n testdata.tpcds_tiny.item\\n WHERE i_manufact_id IN\\n (SELECT i_manufact_id\\n FROM testdata.tpcds_tiny.item\\n WHERE i_category IN ('Electronics'))\\n AND cs_item_sk = i_item_sk\\n AND cs_sold_date_sk = d_date_sk\\n AND d_year = 1998\\n AND d_moy = 5\\n AND cs_bill_addr_sk = ca_address_sk\\n AND ca_gmt_offset = -5\\n GROUP BY i_manufact_id),\\n ws AS\\n ( SELECT i_manufact_id,\\n sum(ws_ext_sales_price) total_sales\\n FROM testdata.tpcds_tiny.web_sales,\\n testdata.tpcds_tiny.date_dim,\\n testdata.tpcds_tiny.customer_address,\\n testdata.tpcds_tiny.item\\n WHERE i_manufact_id IN\\n (SELECT i_manufact_id\\n FROM testdata.tpcds_tiny.item\\n WHERE i_category IN ('Electronics'))\\n AND ws_item_sk = i_item_sk\\n AND ws_sold_date_sk = d_date_sk\\n AND d_year = 1998\\n AND d_moy = 5\\n AND ws_bill_addr_sk = ca_address_sk\\n AND ca_gmt_offset = -5\\n GROUP BY i_manufact_id)\\nSELECT i_manufact_id,\\n sum(total_sales) total_sales\\nFROM\\n (SELECT *\\n FROM ss\\n UNION ALL SELECT *\\n FROM cs\\n UNION ALL SELECT *\\n FROM ws) tmp1\\nGROUP BY i_manufact_id\\nORDER BY total_sales\\nLIMIT 100;\",\n", " 'Q34': \"SELECT c_last_name ,\\n c_first_name ,\\n c_salutation ,\\n c_preferred_cust_flag ,\\n ss_ticket_number ,\\n cnt\\nFROM\\n (SELECT ss_ticket_number ,\\n ss_customer_sk ,\\n count(*) cnt\\n FROM testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.date_dim,\\n testdata.tpcds_tiny.store,\\n testdata.tpcds_tiny.household_demographics\\n WHERE testdata.tpcds_tiny.store_sales.ss_sold_date_sk = testdata.tpcds_tiny.date_dim.d_date_sk\\n AND testdata.tpcds_tiny.store_sales.ss_store_sk = testdata.tpcds_tiny.store.s_store_sk\\n AND testdata.tpcds_tiny.store_sales.ss_hdemo_sk = testdata.tpcds_tiny.household_demographics.hd_demo_sk\\n AND (testdata.tpcds_tiny.date_dim.d_dom BETWEEN 1 AND 3\\n OR testdata.tpcds_tiny.date_dim.d_dom BETWEEN 25 AND 28)\\n AND (testdata.tpcds_tiny.household_demographics.hd_buy_potential = '>10000'\\n OR testdata.tpcds_tiny.household_demographics.hd_buy_potential = 'Unknown')\\n AND testdata.tpcds_tiny.household_demographics.hd_vehicle_count > 0\\n AND (CASE\\n WHEN testdata.tpcds_tiny.household_demographics.hd_vehicle_count > 0 THEN (testdata.tpcds_tiny.household_demographics.hd_dep_count*1.000)/ testdata.tpcds_tiny.household_demographics.hd_vehicle_count\\n ELSE NULL\\n END) > 1.2\\n AND testdata.tpcds_tiny.date_dim.d_year IN (1999,\\n 1999+1,\\n 1999+2)\\n AND testdata.tpcds_tiny.store.s_county = 'Williamson County'\\n GROUP BY ss_ticket_number,\\n ss_customer_sk) dn,\\n testdata.tpcds_tiny.customer\\nWHERE ss_customer_sk = c_customer_sk\\n AND cnt BETWEEN 15 AND 20\\nORDER BY c_last_name NULLS FIRST,\\n c_first_name NULLS FIRST,\\n c_salutation NULLS FIRST,\\n c_preferred_cust_flag DESC NULLS FIRST,\\n ss_ticket_number NULLS FIRST;\",\n", " 'Q35': 'SELECT ca_state,\\n cd_gender,\\n cd_marital_status,\\n cd_dep_count,\\n count(*) cnt1,\\n min(cd_dep_count) min1,\\n max(cd_dep_count) max1,\\n avg(cd_dep_count) avg1,\\n cd_dep_employed_count,\\n count(*) cnt2,\\n min(cd_dep_employed_count) min2,\\n max(cd_dep_employed_count) max2,\\n avg(cd_dep_employed_count) avg2,\\n cd_dep_college_count,\\n count(*) cnt3,\\n min(cd_dep_college_count),\\n max(cd_dep_college_count),\\n avg(cd_dep_college_count)\\nFROM testdata.tpcds_tiny.customer c,\\n testdata.tpcds_tiny.customer_address ca,\\n testdata.tpcds_tiny.customer_demographics\\nWHERE c.c_current_addr_sk = ca.ca_address_sk\\n AND cd_demo_sk = c.c_current_cdemo_sk\\n AND EXISTS\\n (SELECT *\\n FROM testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.date_dim\\n WHERE c.c_customer_sk = ss_customer_sk\\n AND ss_sold_date_sk = d_date_sk\\n AND d_year = 2002\\n AND d_qoy < 4)\\n AND (EXISTS\\n (SELECT *\\n FROM testdata.tpcds_tiny.web_sales,\\n testdata.tpcds_tiny.date_dim\\n WHERE c.c_customer_sk = ws_bill_customer_sk\\n AND ws_sold_date_sk = d_date_sk\\n AND d_year = 2002\\n AND d_qoy < 4)\\n OR EXISTS\\n (SELECT *\\n FROM testdata.tpcds_tiny.catalog_sales,\\n testdata.tpcds_tiny.date_dim\\n WHERE c.c_customer_sk = cs_ship_customer_sk\\n AND cs_sold_date_sk = d_date_sk\\n AND d_year = 2002\\n AND d_qoy < 4))\\nGROUP BY ca_state,\\n cd_gender,\\n cd_marital_status,\\n cd_dep_count,\\n cd_dep_employed_count,\\n cd_dep_college_count\\nORDER BY ca_state NULLS FIRST,\\n cd_gender NULLS FIRST,\\n cd_marital_status NULLS FIRST,\\n cd_dep_count NULLS FIRST,\\n cd_dep_employed_count NULLS FIRST,\\n cd_dep_college_count NULLS FIRST\\nLIMIT 100;',\n", " 'Q36': \"WITH results AS\\n (SELECT sum(ss_net_profit) AS ss_net_profit,\\n sum(ss_ext_sales_price) AS ss_ext_sales_price,\\n (sum(ss_net_profit)*1.0000)/sum(ss_ext_sales_price) AS gross_margin ,\\n i_category ,\\n i_class ,\\n 0 AS g_category,\\n 0 AS g_class\\n FROM testdata.tpcds_tiny.store_sales ,\\n testdata.tpcds_tiny.date_dim d1 ,\\n testdata.tpcds_tiny.item ,\\n testdata.tpcds_tiny.store\\n WHERE d1.d_year = 2001\\n AND d1.d_date_sk = ss_sold_date_sk\\n AND i_item_sk = ss_item_sk\\n AND s_store_sk = ss_store_sk\\n AND s_state ='TN'\\n GROUP BY i_category,\\n i_class) ,\\n results_rollup AS\\n (SELECT gross_margin,\\n i_category,\\n i_class,\\n 0 AS t_category,\\n 0 AS t_class,\\n 0 AS lochierarchy\\n FROM results\\n UNION SELECT (sum(ss_net_profit)*1.0000)/sum(ss_ext_sales_price) AS gross_margin,\\n i_category,\\n NULL AS i_class,\\n 0 AS t_category,\\n 1 AS t_class,\\n 1 AS lochierarchy\\n FROM results\\n GROUP BY i_category\\n UNION SELECT (sum(ss_net_profit)*1.0000)/sum(ss_ext_sales_price) AS gross_margin,\\n NULL AS i_category,\\n NULL AS i_class,\\n 1 AS t_category,\\n 1 AS t_class,\\n 2 AS lochierarchy\\n FROM results)\\nSELECT gross_margin,\\n i_category,\\n i_class,\\n lochierarchy,\\n rank() OVER ( PARTITION BY lochierarchy,\\n CASE\\n WHEN t_class = 0 THEN i_category\\n END\\n ORDER BY gross_margin ASC) AS rank_within_parent\\nFROM results_rollup\\nORDER BY lochierarchy DESC NULLS FIRST,\\n CASE\\n WHEN lochierarchy = 0 THEN i_category\\n END NULLS FIRST,\\n rank_within_parent NULLS FIRST\\nLIMIT 100;\",\n", " 'Q37': \"SELECT i_item_id,\\n i_item_desc,\\n i_current_price\\nFROM testdata.tpcds_tiny.item,\\n testdata.tpcds_tiny.inventory,\\n testdata.tpcds_tiny.date_dim,\\n testdata.tpcds_tiny.catalog_sales\\nWHERE i_current_price BETWEEN 68 AND 68 + 30\\n AND inv_item_sk = i_item_sk\\n AND d_date_sk=inv_date_sk\\n AND d_date BETWEEN cast('2000-02-01' AS date) AND cast('2000-04-01' AS date)\\n AND i_manufact_id IN (677,\\n 940,\\n 694,\\n 808)\\n AND inv_quantity_on_hand BETWEEN 100 AND 500\\n AND cs_item_sk = i_item_sk\\nGROUP BY i_item_id,\\n i_item_desc,\\n i_current_price\\nORDER BY i_item_id\\nLIMIT 100;\",\n", " 'Q38': 'SELECT count(*)\\nFROM\\n (SELECT DISTINCT c_last_name,\\n c_first_name,\\n d_date\\n FROM testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.date_dim,\\n testdata.tpcds_tiny.customer\\n WHERE testdata.tpcds_tiny.store_sales.ss_sold_date_sk = testdata.tpcds_tiny.date_dim.d_date_sk\\n AND testdata.tpcds_tiny.store_sales.ss_customer_sk = testdata.tpcds_tiny.customer.c_customer_sk\\n AND d_month_seq BETWEEN 1200 AND 1200 + 11 INTERSECT\\n SELECT DISTINCT c_last_name,\\n c_first_name,\\n d_date\\n FROM testdata.tpcds_tiny.catalog_sales,\\n testdata.tpcds_tiny.date_dim,\\n testdata.tpcds_tiny.customer WHERE testdata.tpcds_tiny.catalog_sales.cs_sold_date_sk = testdata.tpcds_tiny.date_dim.d_date_sk\\n AND testdata.tpcds_tiny.catalog_sales.cs_bill_customer_sk = testdata.tpcds_tiny.customer.c_customer_sk\\n AND d_month_seq BETWEEN 1200 AND 1200 + 11 INTERSECT\\n SELECT DISTINCT c_last_name,\\n c_first_name,\\n d_date\\n FROM testdata.tpcds_tiny.web_sales,\\n testdata.tpcds_tiny.date_dim,\\n testdata.tpcds_tiny.customer WHERE testdata.tpcds_tiny.web_sales.ws_sold_date_sk = testdata.tpcds_tiny.date_dim.d_date_sk\\n AND testdata.tpcds_tiny.web_sales.ws_bill_customer_sk = testdata.tpcds_tiny.customer.c_customer_sk\\n AND d_month_seq BETWEEN 1200 AND 1200 + 11 ) hot_cust\\nLIMIT 100;',\n", " 'Q39': 'WITH inv AS\\n (SELECT w_warehouse_name,\\n w_warehouse_sk,\\n i_item_sk,\\n d_moy,\\n stdev,\\n mean,\\n CASE mean\\n WHEN 0 THEN NULL\\n ELSE stdev/mean\\n END cov\\n FROM\\n (SELECT w_warehouse_name,\\n w_warehouse_sk,\\n i_item_sk,\\n d_moy,\\n stddev_samp(inv_quantity_on_hand)*1.000 stdev,\\n avg(inv_quantity_on_hand) mean\\n FROM testdata.tpcds_tiny.inventory,\\n testdata.tpcds_tiny.item,\\n testdata.tpcds_tiny.warehouse,\\n testdata.tpcds_tiny.date_dim\\n WHERE inv_item_sk = i_item_sk\\n AND inv_warehouse_sk = w_warehouse_sk\\n AND inv_date_sk = d_date_sk\\n AND d_year =2001\\n GROUP BY w_warehouse_name,\\n w_warehouse_sk,\\n i_item_sk,\\n d_moy) foo\\n WHERE CASE mean\\n WHEN 0 THEN 0\\n ELSE stdev/mean\\n END > 1)\\nSELECT inv1.w_warehouse_sk wsk1,\\n inv1.i_item_sk isk1,\\n inv1.d_moy dmoy1,\\n inv1.mean mean1,\\n inv1.cov cov1,\\n inv2.w_warehouse_sk,\\n inv2.i_item_sk,\\n inv2.d_moy,\\n inv2.mean,\\n inv2.cov\\nFROM inv inv1,\\n inv inv2\\nWHERE inv1.i_item_sk = inv2.i_item_sk\\n AND inv1.w_warehouse_sk = inv2.w_warehouse_sk\\n AND inv1.d_moy=1\\n AND inv2.d_moy=1+1\\nORDER BY inv1.w_warehouse_sk NULLS FIRST,\\n inv1.i_item_sk NULLS FIRST,\\n inv1.d_moy NULLS FIRST,\\n inv1.mean NULLS FIRST,\\n inv1.cov NULLS FIRST,\\n inv2.d_moy NULLS FIRST,\\n inv2.mean NULLS FIRST,\\n inv2.cov NULLS FIRST;',\n", " 'Q40': \"SELECT w_state,\\n i_item_id,\\n sum(CASE\\n WHEN (cast(d_date AS date) < CAST ('2000-03-11' AS date)) THEN cs_sales_price - coalesce(cr_refunded_cash,0)\\n ELSE 0\\n END) AS sales_before,\\n sum(CASE\\n WHEN (cast(d_date AS date) >= CAST ('2000-03-11' AS date)) THEN cs_sales_price - coalesce(cr_refunded_cash,0)\\n ELSE 0\\n END) AS sales_after\\nFROM testdata.tpcds_tiny.catalog_sales\\nLEFT OUTER JOIN testdata.tpcds_tiny.catalog_returns ON (cs_order_number = cr_order_number\\n AND cs_item_sk = cr_item_sk) ,testdata.tpcds_tiny.warehouse,\\n testdata.tpcds_tiny.item,\\n testdata.tpcds_tiny.date_dim\\nWHERE i_current_price BETWEEN 0.99 AND 1.49\\n AND i_item_sk = cs_item_sk\\n AND cs_warehouse_sk = w_warehouse_sk\\n AND cs_sold_date_sk = d_date_sk\\n AND d_date BETWEEN CAST ('2000-02-10' AS date) AND CAST ('2000-04-10' AS date)\\nGROUP BY w_state,\\n i_item_id\\nORDER BY w_state,\\n i_item_id\\nLIMIT 100;\",\n", " 'Q41': \"SELECT distinct(i_product_name)\\nFROM testdata.tpcds_tiny.item i1\\nWHERE i_manufact_id BETWEEN 738 AND 738+40\\n AND\\n (SELECT count(*) AS item_cnt\\n FROM testdata.tpcds_tiny.item\\n WHERE (i_manufact = i1.i_manufact\\n AND ((i_category = 'Women'\\n AND (i_color = 'powder'\\n OR i_color = 'khaki')\\n AND (i_units = 'Ounce'\\n OR i_units = 'Oz')\\n AND (i_size = 'medium'\\n OR i_size = 'extra large'))\\n OR (i_category = 'Women'\\n AND (i_color = 'brown'\\n OR i_color = 'honeydew')\\n AND (i_units = 'Bunch'\\n OR i_units = 'Ton')\\n AND (i_size = 'N/A'\\n OR i_size = 'small'))\\n OR (i_category = 'Men'\\n AND (i_color = 'floral'\\n OR i_color = 'deep')\\n AND (i_units = 'N/A'\\n OR i_units = 'Dozen')\\n AND (i_size = 'petite'\\n OR i_size = 'petite'))\\n OR (i_category = 'Men'\\n AND (i_color = 'light'\\n OR i_color = 'cornflower')\\n AND (i_units = 'Box'\\n OR i_units = 'Pound')\\n AND (i_size = 'medium'\\n OR i_size = 'extra large'))))\\n OR (i_manufact = i1.i_manufact\\n AND ((i_category = 'Women'\\n AND (i_color = 'midnight'\\n OR i_color = 'snow')\\n AND (i_units = 'Pallet'\\n OR i_units = 'Gross')\\n AND (i_size = 'medium'\\n OR i_size = 'extra large'))\\n OR (i_category = 'Women'\\n AND (i_color = 'cyan'\\n OR i_color = 'papaya')\\n AND (i_units = 'Cup'\\n OR i_units = 'Dram')\\n AND (i_size = 'N/A'\\n OR i_size = 'small'))\\n OR (i_category = 'Men'\\n AND (i_color = 'orange'\\n OR i_color = 'frosted')\\n AND (i_units = 'Each'\\n OR i_units = 'Tbl')\\n AND (i_size = 'petite'\\n OR i_size = 'petite'))\\n OR (i_category = 'Men'\\n AND (i_color = 'forest'\\n OR i_color = 'ghost')\\n AND (i_units = 'Lb'\\n OR i_units = 'Bundle')\\n AND (i_size = 'medium'\\n OR i_size = 'extra large'))))) > 0\\nORDER BY i_product_name\\nLIMIT 100;\",\n", " 'Q42': 'SELECT dt.d_year,\\n testdata.tpcds_tiny.item.i_category_id,\\n testdata.tpcds_tiny.item.i_category,\\n sum(ss_ext_sales_price)\\nFROM testdata.tpcds_tiny.date_dim dt,\\n testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.item\\nWHERE dt.d_date_sk = testdata.tpcds_tiny.store_sales.ss_sold_date_sk\\n AND testdata.tpcds_tiny.store_sales.ss_item_sk = testdata.tpcds_tiny.item.i_item_sk\\n AND testdata.tpcds_tiny.item.i_manager_id = 1\\n AND dt.d_moy=11\\n AND dt.d_year=2000\\nGROUP BY dt.d_year,\\n testdata.tpcds_tiny.item.i_category_id,\\n testdata.tpcds_tiny.item.i_category\\nORDER BY sum(ss_ext_sales_price) DESC,dt.d_year,\\n testdata.tpcds_tiny.item.i_category_id,\\n testdata.tpcds_tiny.item.i_category\\nLIMIT 100 ;',\n", " 'Q43': \"SELECT s_store_name,\\n s_store_id,\\n sum(CASE\\n WHEN (d_day_name='Sunday') THEN ss_sales_price\\n ELSE NULL\\n END) sun_sales,\\n sum(CASE\\n WHEN (d_day_name='Monday') THEN ss_sales_price\\n ELSE NULL\\n END) mon_sales,\\n sum(CASE\\n WHEN (d_day_name='Tuesday') THEN ss_sales_price\\n ELSE NULL\\n END) tue_sales,\\n sum(CASE\\n WHEN (d_day_name='Wednesday') THEN ss_sales_price\\n ELSE NULL\\n END) wed_sales,\\n sum(CASE\\n WHEN (d_day_name='Thursday') THEN ss_sales_price\\n ELSE NULL\\n END) thu_sales,\\n sum(CASE\\n WHEN (d_day_name='Friday') THEN ss_sales_price\\n ELSE NULL\\n END) fri_sales,\\n sum(CASE\\n WHEN (d_day_name='Saturday') THEN ss_sales_price\\n ELSE NULL\\n END) sat_sales\\nFROM testdata.tpcds_tiny.date_dim,\\n testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.store\\nWHERE d_date_sk = ss_sold_date_sk\\n AND s_store_sk = ss_store_sk\\n AND s_gmt_offset = -5\\n AND d_year = 2000\\nGROUP BY s_store_name,\\n s_store_id\\nORDER BY s_store_name,\\n s_store_id,\\n sun_sales,\\n mon_sales,\\n tue_sales,\\n wed_sales,\\n thu_sales,\\n fri_sales,\\n sat_sales\\nLIMIT 100;\",\n", " 'Q44': 'SELECT asceding.rnk,\\n i1.i_product_name best_performing,\\n i2.i_product_name worst_performing\\nFROM\\n (SELECT *\\n FROM\\n (SELECT item_sk,\\n rank() OVER (\\n ORDER BY rank_col ASC) rnk\\n FROM\\n (SELECT ss_item_sk item_sk,\\n avg(ss_net_profit) rank_col\\n FROM testdata.tpcds_tiny.store_sales ss1\\n WHERE ss_store_sk = 4\\n GROUP BY ss_item_sk\\n HAVING avg(ss_net_profit) > 0.9*\\n (SELECT avg(ss_net_profit) rank_col\\n FROM testdata.tpcds_tiny.store_sales\\n WHERE ss_store_sk = 4\\n AND ss_addr_sk IS NULL\\n GROUP BY ss_store_sk))V1)V11\\n WHERE rnk < 11) asceding,\\n (SELECT *\\n FROM\\n (SELECT item_sk,\\n rank() OVER (\\n ORDER BY rank_col DESC) rnk\\n FROM\\n (SELECT ss_item_sk item_sk,\\n avg(ss_net_profit) rank_col\\n FROM testdata.tpcds_tiny.store_sales ss1\\n WHERE ss_store_sk = 4\\n GROUP BY ss_item_sk\\n HAVING avg(ss_net_profit) > 0.9*\\n (SELECT avg(ss_net_profit) rank_col\\n FROM testdata.tpcds_tiny.store_sales\\n WHERE ss_store_sk = 4\\n AND ss_addr_sk IS NULL\\n GROUP BY ss_store_sk))V2)V21\\n WHERE rnk < 11) descending,\\n testdata.tpcds_tiny.item i1,\\n testdata.tpcds_tiny.item i2\\nWHERE asceding.rnk = descending.rnk\\n AND i1.i_item_sk=asceding.item_sk\\n AND i2.i_item_sk=descending.item_sk\\nORDER BY asceding.rnk\\nLIMIT 100;',\n", " 'Q45': \"SELECT ca_zip,\\n ca_city,\\n sum(ws_sales_price)\\nFROM testdata.tpcds_tiny.web_sales,\\n testdata.tpcds_tiny.customer,\\n testdata.tpcds_tiny.customer_address,\\n testdata.tpcds_tiny.date_dim,\\n testdata.tpcds_tiny.item\\nWHERE ws_bill_customer_sk = c_customer_sk\\n AND c_current_addr_sk = ca_address_sk\\n AND ws_item_sk = i_item_sk\\n AND (SUBSTRING(ca_zip,1,5) IN ('85669',\\n '86197',\\n '88274',\\n '83405',\\n '86475',\\n '85392',\\n '85460',\\n '80348',\\n '81792')\\n OR i_item_id IN\\n (SELECT i_item_id\\n FROM testdata.tpcds_tiny.item\\n WHERE i_item_sk IN (2,\\n 3,\\n 5,\\n 7,\\n 11,\\n 13,\\n 17,\\n 19,\\n 23,\\n 29) ))\\n AND ws_sold_date_sk = d_date_sk\\n AND d_qoy = 2\\n AND d_year = 2001\\nGROUP BY ca_zip,\\n ca_city\\nORDER BY ca_zip,\\n ca_city\\nLIMIT 100;\",\n", " 'Q46': \"SELECT c_last_name,\\n c_first_name,\\n ca_city,\\n bought_city,\\n ss_ticket_number,\\n amt,\\n profit\\nFROM\\n (SELECT ss_ticket_number,\\n ss_customer_sk,\\n ca_city bought_city,\\n sum(ss_coupon_amt) amt,\\n sum(ss_net_profit) profit\\n FROM testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.date_dim,\\n testdata.tpcds_tiny.store,\\n testdata.tpcds_tiny.household_demographics,\\n testdata.tpcds_tiny.customer_address\\n WHERE testdata.tpcds_tiny.store_sales.ss_sold_date_sk = testdata.tpcds_tiny.date_dim.d_date_sk\\n AND testdata.tpcds_tiny.store_sales.ss_store_sk = testdata.tpcds_tiny.store.s_store_sk\\n AND testdata.tpcds_tiny.store_sales.ss_hdemo_sk = testdata.tpcds_tiny.household_demographics.hd_demo_sk\\n AND testdata.tpcds_tiny.store_sales.ss_addr_sk = testdata.tpcds_tiny.customer_address.ca_address_sk\\n AND (testdata.tpcds_tiny.household_demographics.hd_dep_count = 4\\n OR testdata.tpcds_tiny.household_demographics.hd_vehicle_count= 3)\\n AND testdata.tpcds_tiny.date_dim.d_dow IN (6,\\n 0)\\n AND testdata.tpcds_tiny.date_dim.d_year IN (1999,\\n 1999+1,\\n 1999+2)\\n AND testdata.tpcds_tiny.store.s_city IN ('Fairview',\\n 'Midway')\\n GROUP BY ss_ticket_number,\\n ss_customer_sk,\\n ss_addr_sk,\\n ca_city) dn,\\n testdata.tpcds_tiny.customer,\\n testdata.tpcds_tiny.customer_address current_addr\\nWHERE ss_customer_sk = c_customer_sk\\n AND testdata.tpcds_tiny.customer.c_current_addr_sk = current_addr.ca_address_sk\\n AND current_addr.ca_city <> bought_city\\nORDER BY c_last_name NULLS FIRST,\\n c_first_name NULLS FIRST,\\n ca_city NULLS FIRST,\\n bought_city NULLS FIRST,\\n ss_ticket_number NULLS FIRST\\nLIMIT 100;\",\n", " 'Q47': 'WITH v1 AS\\n (SELECT i_category,\\n i_brand,\\n s_store_name,\\n s_company_name,\\n d_year,\\n d_moy,\\n sum(ss_sales_price) sum_sales,\\n avg(sum(ss_sales_price)) OVER (PARTITION BY i_category,\\n i_brand,\\n s_store_name,\\n s_company_name,\\n d_year) avg_monthly_sales,\\n rank() OVER (PARTITION BY i_category,\\n i_brand,\\n s_store_name,\\n s_company_name\\n ORDER BY d_year,\\n d_moy) rn\\n FROM testdata.tpcds_tiny.item,\\n testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.date_dim,\\n testdata.tpcds_tiny.store\\n WHERE ss_item_sk = i_item_sk\\n AND ss_sold_date_sk = d_date_sk\\n AND ss_store_sk = s_store_sk\\n AND (d_year = 1999\\n OR (d_year = 1999-1\\n AND d_moy =12)\\n OR (d_year = 1999+1\\n AND d_moy =1))\\n GROUP BY i_category,\\n i_brand,\\n s_store_name,\\n s_company_name,\\n d_year,\\n d_moy),\\n v2 AS\\n (SELECT v1.i_category,\\n v1.i_brand,\\n v1.s_store_name,\\n v1.s_company_name,\\n v1.d_year,\\n v1.d_moy,\\n v1.avg_monthly_sales,\\n v1.sum_sales,\\n v1_lag.sum_sales psum,\\n v1_lead.sum_sales nsum\\n FROM v1,\\n v1 v1_lag,\\n v1 v1_lead\\n WHERE v1.i_category = v1_lag.i_category\\n AND v1.i_category = v1_lead.i_category\\n AND v1.i_brand = v1_lag.i_brand\\n AND v1.i_brand = v1_lead.i_brand\\n AND v1.s_store_name = v1_lag.s_store_name\\n AND v1.s_store_name = v1_lead.s_store_name\\n AND v1.s_company_name = v1_lag.s_company_name\\n AND v1.s_company_name = v1_lead.s_company_name\\n AND v1.rn = v1_lag.rn + 1\\n AND v1.rn = v1_lead.rn - 1)\\nSELECT *\\nFROM v2\\nWHERE d_year = 1999\\n AND avg_monthly_sales > 0\\n AND CASE\\n WHEN avg_monthly_sales > 0 THEN abs(sum_sales - avg_monthly_sales) / avg_monthly_sales\\n ELSE NULL\\n END > 0.1\\nORDER BY sum_sales - avg_monthly_sales, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10\\nLIMIT 100;',\n", " 'Q48': \"SELECT SUM (ss_quantity)\\nFROM testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.store,\\n testdata.tpcds_tiny.customer_demographics,\\n testdata.tpcds_tiny.customer_address,\\n testdata.tpcds_tiny.date_dim\\nWHERE s_store_sk = ss_store_sk\\n AND ss_sold_date_sk = d_date_sk\\n AND d_year = 2000\\n AND ((cd_demo_sk = ss_cdemo_sk\\n AND cd_marital_status = 'M'\\n AND cd_education_status = '4 yr Degree'\\n AND ss_sales_price BETWEEN 100.00 AND 150.00)\\n OR (cd_demo_sk = ss_cdemo_sk\\n AND cd_marital_status = 'D'\\n AND cd_education_status = '2 yr Degree'\\n AND ss_sales_price BETWEEN 50.00 AND 100.00)\\n OR (cd_demo_sk = ss_cdemo_sk\\n AND cd_marital_status = 'S'\\n AND cd_education_status = 'College'\\n AND ss_sales_price BETWEEN 150.00 AND 200.00))\\n AND ((ss_addr_sk = ca_address_sk\\n AND ca_country = 'United States'\\n AND ca_state IN ('CO',\\n 'OH',\\n 'TX')\\n AND ss_net_profit BETWEEN 0 AND 2000)\\n OR (ss_addr_sk = ca_address_sk\\n AND ca_country = 'United States'\\n AND ca_state IN ('OR',\\n 'MN',\\n 'KY')\\n AND ss_net_profit BETWEEN 150 AND 3000)\\n OR (ss_addr_sk = ca_address_sk\\n AND ca_country = 'United States'\\n AND ca_state IN ('VA',\\n 'CA',\\n 'MS')\\n AND ss_net_profit BETWEEN 50 AND 25000)) ;\",\n", " 'Q49': \"SELECT channel,\\n item,\\n return_ratio,\\n return_rank,\\n currency_rank\\nFROM\\n (SELECT 'web' AS channel,\\n web.item,\\n web.return_ratio,\\n web.return_rank,\\n web.currency_rank\\n FROM\\n (SELECT item,\\n return_ratio,\\n currency_ratio,\\n rank() OVER (\\n ORDER BY return_ratio) AS return_rank,\\n rank() OVER (\\n ORDER BY currency_ratio) AS currency_rank\\n FROM\\n (SELECT ws.ws_item_sk AS item,\\n (cast(sum(coalesce(wr.wr_return_quantity,0)) AS decimal(15,4))/ cast(sum(coalesce(ws.ws_quantity,0)) AS decimal(15,4))) AS return_ratio,\\n (cast(sum(coalesce(wr.wr_return_amt,0)) AS decimal(15,4))/ cast(sum(coalesce(ws.ws_net_paid,0)) AS decimal(15,4))) AS currency_ratio\\n FROM testdata.tpcds_tiny.web_sales ws\\n LEFT OUTER JOIN testdata.tpcds_tiny.web_returns wr ON (ws.ws_order_number = wr.wr_order_number\\n AND ws.ws_item_sk = wr.wr_item_sk) ,testdata.tpcds_tiny.date_dim\\n WHERE wr.wr_return_amt > 10000\\n AND ws.ws_net_profit > 1\\n AND ws.ws_net_paid > 0\\n AND ws.ws_quantity > 0\\n AND ws_sold_date_sk = d_date_sk\\n AND d_year = 2001\\n AND d_moy = 12\\n GROUP BY ws.ws_item_sk) in_web) web\\n WHERE (web.return_rank <= 10\\n OR web.currency_rank <= 10)\\n UNION SELECT 'catalog' AS channel,\\n catalog.item,\\n catalog.return_ratio,\\n catalog.return_rank,\\n catalog.currency_rank\\n FROM\\n (SELECT item,\\n return_ratio,\\n currency_ratio,\\n rank() OVER (\\n ORDER BY return_ratio) AS return_rank,\\n rank() OVER (\\n ORDER BY currency_ratio) AS currency_rank\\n FROM\\n (SELECT cs.cs_item_sk AS item,\\n (cast(sum(coalesce(cr.cr_return_quantity,0)) AS decimal(15,4))/ cast(sum(coalesce(cs.cs_quantity,0)) AS decimal(15,4))) AS return_ratio,\\n (cast(sum(coalesce(cr.cr_return_amount,0)) AS decimal(15,4))/ cast(sum(coalesce(cs.cs_net_paid,0)) AS decimal(15,4))) AS currency_ratio\\n FROM testdata.tpcds_tiny.catalog_sales cs\\n LEFT OUTER JOIN testdata.tpcds_tiny.catalog_returns cr ON (cs.cs_order_number = cr.cr_order_number\\n AND cs.cs_item_sk = cr.cr_item_sk) ,testdata.tpcds_tiny.date_dim\\n WHERE cr.cr_return_amount > 10000\\n AND cs.cs_net_profit > 1\\n AND cs.cs_net_paid > 0\\n AND cs.cs_quantity > 0\\n AND cs_sold_date_sk = d_date_sk\\n AND d_year = 2001\\n AND d_moy = 12\\n GROUP BY cs.cs_item_sk) in_cat) CATALOG\\n WHERE (catalog.return_rank <= 10\\n OR catalog.currency_rank <=10)\\n UNION SELECT 'store' AS channel,\\n store.item,\\n store.return_ratio,\\n store.return_rank,\\n store.currency_rank\\n FROM\\n (SELECT item,\\n return_ratio,\\n currency_ratio,\\n rank() OVER (\\n ORDER BY return_ratio) AS return_rank,\\n rank() OVER (\\n ORDER BY currency_ratio) AS currency_rank\\n FROM\\n (SELECT sts.ss_item_sk AS item,\\n (cast(sum(coalesce(sr.sr_return_quantity,0)) AS decimal(15,4))/cast(sum(coalesce(sts.ss_quantity,0)) AS decimal(15,4))) AS return_ratio,\\n (cast(sum(coalesce(sr.sr_return_amt,0)) AS decimal(15,4))/cast(sum(coalesce(sts.ss_net_paid,0)) AS decimal(15,4))) AS currency_ratio\\n FROM testdata.tpcds_tiny.store_sales sts\\n LEFT OUTER JOIN testdata.tpcds_tiny.store_returns sr ON (sts.ss_ticket_number = sr.sr_ticket_number\\n AND sts.ss_item_sk = sr.sr_item_sk) ,testdata.tpcds_tiny.date_dim\\n WHERE sr.sr_return_amt > 10000\\n AND sts.ss_net_profit > 1\\n AND sts.ss_net_paid > 0\\n AND sts.ss_quantity > 0\\n AND ss_sold_date_sk = d_date_sk\\n AND d_year = 2001\\n AND d_moy = 12\\n GROUP BY sts.ss_item_sk) in_store) store\\n WHERE (store.return_rank <= 10\\n OR store.currency_rank <= 10) ) sq1\\nORDER BY 1 NULLS FIRST,\\n 4 NULLS FIRST,\\n 5 NULLS FIRST,\\n 2 NULLS FIRST\\nLIMIT 100;\",\n", " 'Q50': 'SELECT s_store_name,\\n s_company_id,\\n s_street_number,\\n s_street_name,\\n s_street_type,\\n s_suite_number,\\n s_city,\\n s_county,\\n s_state,\\n s_zip,\\n sum(CASE\\n WHEN (sr_returned_date_sk - ss_sold_date_sk <= 30) THEN 1\\n ELSE 0\\n END) AS \"30 days\",\\n sum(CASE\\n WHEN (sr_returned_date_sk - ss_sold_date_sk > 30)\\n AND (sr_returned_date_sk - ss_sold_date_sk <= 60) THEN 1\\n ELSE 0\\n END) AS \"31-60 days\",\\n sum(CASE\\n WHEN (sr_returned_date_sk - ss_sold_date_sk > 60)\\n AND (sr_returned_date_sk - ss_sold_date_sk <= 90) THEN 1\\n ELSE 0\\n END) AS \"61-90 days\",\\n sum(CASE\\n WHEN (sr_returned_date_sk - ss_sold_date_sk > 90)\\n AND (sr_returned_date_sk - ss_sold_date_sk <= 120) THEN 1\\n ELSE 0\\n END) AS \"91-120 days\",\\n sum(CASE\\n WHEN (sr_returned_date_sk - ss_sold_date_sk > 120) THEN 1\\n ELSE 0\\n END) AS \">120 days\"\\nFROM testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.store_returns,\\n testdata.tpcds_tiny.store,\\n testdata.tpcds_tiny.date_dim d1,\\n testdata.tpcds_tiny.date_dim d2\\nWHERE d2.d_year = 2001\\n AND d2.d_moy = 8\\n AND ss_ticket_number = sr_ticket_number\\n AND ss_item_sk = sr_item_sk\\n AND ss_sold_date_sk = d1.d_date_sk\\n AND sr_returned_date_sk = d2.d_date_sk\\n AND ss_customer_sk = sr_customer_sk\\n AND ss_store_sk = s_store_sk\\nGROUP BY s_store_name,\\n s_company_id,\\n s_street_number,\\n s_street_name,\\n s_street_type,\\n s_suite_number,\\n s_city,\\n s_county,\\n s_state,\\n s_zip\\nORDER BY s_store_name,\\n s_company_id,\\n s_street_number,\\n s_street_name,\\n s_street_type,\\n s_suite_number,\\n s_city,\\n s_county,\\n s_state,\\n s_zip\\nLIMIT 100;',\n", " 'Q51': 'WITH web_v1 AS\\n (SELECT ws_item_sk item_sk,\\n d_date,\\n sum(sum(ws_sales_price)) OVER (PARTITION BY ws_item_sk\\n ORDER BY d_date ROWS BETWEEN unbounded preceding AND CURRENT ROW) cume_sales\\n FROM testdata.tpcds_tiny.web_sales,\\n testdata.tpcds_tiny.date_dim\\n WHERE ws_sold_date_sk=d_date_sk\\n AND d_month_seq BETWEEN 1200 AND 1200+11\\n AND ws_item_sk IS NOT NULL\\n GROUP BY ws_item_sk,\\n d_date),\\n store_v1 AS\\n (SELECT ss_item_sk item_sk,\\n d_date,\\n sum(sum(ss_sales_price)) OVER (PARTITION BY ss_item_sk\\n ORDER BY d_date ROWS BETWEEN unbounded preceding AND CURRENT ROW) cume_sales\\n FROM testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.date_dim\\n WHERE ss_sold_date_sk=d_date_sk\\n AND d_month_seq BETWEEN 1200 AND 1200+11\\n AND ss_item_sk IS NOT NULL\\n GROUP BY ss_item_sk,\\n d_date)\\nSELECT *\\nFROM\\n (SELECT item_sk,\\n d_date,\\n web_sales,\\n store_sales,\\n max(web_sales) OVER (PARTITION BY item_sk\\n ORDER BY d_date ROWS BETWEEN unbounded preceding AND CURRENT ROW) web_cumulative,\\n max(store_sales) OVER (PARTITION BY item_sk\\n ORDER BY d_date ROWS BETWEEN unbounded preceding AND CURRENT ROW) store_cumulative\\n FROM\\n (SELECT CASE\\n WHEN web.item_sk IS NOT NULL THEN web.item_sk\\n ELSE testdata.tpcds_tiny.store.item_sk\\n END item_sk,\\n CASE\\n WHEN web.d_date IS NOT NULL THEN web.d_date\\n ELSE testdata.tpcds_tiny.store.d_date\\n END d_date,\\n web.cume_sales web_sales,\\n testdata.tpcds_tiny.store.cume_sales store_sales\\n FROM web_v1 web\\n FULL OUTER JOIN store_v1 store ON (web.item_sk = testdata.tpcds_tiny.store.item_sk\\n AND web.d_date = testdata.tpcds_tiny.store.d_date))x)y\\nWHERE web_cumulative > store_cumulative\\nORDER BY item_sk NULLS FIRST,\\n d_date NULLS FIRST\\nLIMIT 100;',\n", " 'Q52': 'SELECT dt.d_year,\\n testdata.tpcds_tiny.item.i_brand_id brand_id,\\n testdata.tpcds_tiny.item.i_brand brand,\\n sum(ss_ext_sales_price) ext_price\\nFROM testdata.tpcds_tiny.date_dim dt,\\n testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.item\\nWHERE dt.d_date_sk = testdata.tpcds_tiny.store_sales.ss_sold_date_sk\\n AND testdata.tpcds_tiny.store_sales.ss_item_sk = testdata.tpcds_tiny.item.i_item_sk\\n AND testdata.tpcds_tiny.item.i_manager_id = 1\\n AND dt.d_moy=11\\n AND dt.d_year=2000\\nGROUP BY dt.d_year,\\n testdata.tpcds_tiny.item.i_brand,\\n testdata.tpcds_tiny.item.i_brand_id\\nORDER BY dt.d_year,\\n ext_price DESC,\\n brand_id\\nLIMIT 100 ;',\n", " 'Q53': \"SELECT *\\nFROM\\n (SELECT i_manufact_id,\\n sum(ss_sales_price) sum_sales,\\n avg(sum(ss_sales_price)) OVER (PARTITION BY i_manufact_id) avg_quarterly_sales\\n FROM testdata.tpcds_tiny.item,\\n testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.date_dim,\\n testdata.tpcds_tiny.store\\n WHERE ss_item_sk = i_item_sk\\n AND ss_sold_date_sk = d_date_sk\\n AND ss_store_sk = s_store_sk\\n AND d_month_seq IN (1200,\\n 1200+1,\\n 1200+2,\\n 1200+3,\\n 1200+4,\\n 1200+5,\\n 1200+6,\\n 1200+7,\\n 1200+8,\\n 1200+9,\\n 1200+10,\\n 1200+11)\\n AND ((i_category IN ('Books',\\n 'Children',\\n 'Electronics')\\n AND i_class IN ('personal',\\n 'portable',\\n 'reference',\\n 'self-help')\\n AND i_brand IN ('scholaramalgamalg #14',\\n 'scholaramalgamalg #7',\\n 'exportiunivamalg #9',\\n 'scholaramalgamalg #9')) or(i_category IN ('Women','Music','Men')\\n AND i_class IN ('accessories','classical','fragrances','pants')\\n AND i_brand IN ('amalgimporto #1','edu packscholar #1','exportiimporto #1', 'importoamalg #1')))\\n GROUP BY i_manufact_id,\\n d_qoy) tmp1\\nWHERE CASE\\n WHEN avg_quarterly_sales > 0 THEN ABS (sum_sales - avg_quarterly_sales)/ avg_quarterly_sales\\n ELSE NULL\\n END > 0.1\\nORDER BY avg_quarterly_sales,\\n sum_sales,\\n i_manufact_id\\nLIMIT 100;\",\n", " 'Q54': \"WITH my_customers AS\\n (SELECT DISTINCT c_customer_sk,\\n c_current_addr_sk\\n FROM\\n (SELECT cs_sold_date_sk sold_date_sk,\\n cs_bill_customer_sk customer_sk,\\n cs_item_sk item_sk\\n FROM testdata.tpcds_tiny.catalog_sales\\n UNION ALL SELECT ws_sold_date_sk sold_date_sk,\\n ws_bill_customer_sk customer_sk,\\n ws_item_sk item_sk\\n FROM testdata.tpcds_tiny.web_sales) cs_or_ws_sales,\\n testdata.tpcds_tiny.item,\\n testdata.tpcds_tiny.date_dim,\\n testdata.tpcds_tiny.customer\\n WHERE sold_date_sk = d_date_sk\\n AND item_sk = i_item_sk\\n AND i_category = 'Women'\\n AND i_class = 'maternity'\\n AND c_customer_sk = cs_or_ws_sales.customer_sk\\n AND d_moy = 12\\n AND d_year = 1998 ),\\n my_revenue AS\\n (SELECT c_customer_sk,\\n sum(ss_ext_sales_price) AS revenue\\n FROM my_customers,\\n testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.customer_address,\\n testdata.tpcds_tiny.store,\\n testdata.tpcds_tiny.date_dim\\n WHERE c_current_addr_sk = ca_address_sk\\n AND ca_county = s_county\\n AND ca_state = s_state\\n AND ss_sold_date_sk = d_date_sk\\n AND c_customer_sk = ss_customer_sk\\n AND d_month_seq BETWEEN\\n (SELECT DISTINCT d_month_seq+1\\n FROM testdata.tpcds_tiny.date_dim\\n WHERE d_year = 1998\\n AND d_moy = 12) AND\\n (SELECT DISTINCT d_month_seq+3\\n FROM testdata.tpcds_tiny.date_dim\\n WHERE d_year = 1998\\n AND d_moy = 12)\\n GROUP BY c_customer_sk),\\n segments AS\\n (SELECT cast(round(revenue/50) AS int64) AS SEGMENT\\n FROM my_revenue)\\nSELECT SEGMENT,\\n count(*) AS num_customers,\\n SEGMENT*50 AS segment_base\\nFROM segments\\nGROUP BY SEGMENT\\nORDER BY SEGMENT NULLS FIRST,\\n num_customers NULLS FIRST,\\n segment_base\\nLIMIT 100;\",\n", " 'Q55': 'SELECT i_brand_id brand_id,\\n i_brand brand,\\n sum(ss_ext_sales_price) ext_price\\nFROM testdata.tpcds_tiny.date_dim,\\n testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.item\\nWHERE d_date_sk = ss_sold_date_sk\\n AND ss_item_sk = i_item_sk\\n AND i_manager_id=28\\n AND d_moy=11\\n AND d_year=1999\\nGROUP BY i_brand,\\n i_brand_id\\nORDER BY ext_price DESC,\\n i_brand_id\\nLIMIT 100 ;',\n", " 'Q56': \"WITH ss AS\\n (SELECT i_item_id,\\n sum(ss_ext_sales_price) total_sales\\n FROM testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.date_dim,\\n testdata.tpcds_tiny.customer_address,\\n testdata.tpcds_tiny.item\\n WHERE i_item_id IN\\n (SELECT i_item_id\\n FROM testdata.tpcds_tiny.item\\n WHERE i_color IN ('slate',\\n 'blanched',\\n 'burnished'))\\n AND ss_item_sk = i_item_sk\\n AND ss_sold_date_sk = d_date_sk\\n AND d_year = 2001\\n AND d_moy = 2\\n AND ss_addr_sk = ca_address_sk\\n AND ca_gmt_offset = -5\\n GROUP BY i_item_id),\\n cs AS\\n (SELECT i_item_id,\\n sum(cs_ext_sales_price) total_sales\\n FROM testdata.tpcds_tiny.catalog_sales,\\n testdata.tpcds_tiny.date_dim,\\n testdata.tpcds_tiny.customer_address,\\n testdata.tpcds_tiny.item\\n WHERE i_item_id IN\\n (SELECT i_item_id\\n FROM testdata.tpcds_tiny.item\\n WHERE i_color IN ('slate',\\n 'blanched',\\n 'burnished'))\\n AND cs_item_sk = i_item_sk\\n AND cs_sold_date_sk = d_date_sk\\n AND d_year = 2001\\n AND d_moy = 2\\n AND cs_bill_addr_sk = ca_address_sk\\n AND ca_gmt_offset = -5\\n GROUP BY i_item_id),\\n ws AS\\n (SELECT i_item_id,\\n sum(ws_ext_sales_price) total_sales\\n FROM testdata.tpcds_tiny.web_sales,\\n testdata.tpcds_tiny.date_dim,\\n testdata.tpcds_tiny.customer_address,\\n testdata.tpcds_tiny.item\\n WHERE i_item_id IN\\n (SELECT i_item_id\\n FROM testdata.tpcds_tiny.item\\n WHERE i_color IN ('slate',\\n 'blanched',\\n 'burnished'))\\n AND ws_item_sk = i_item_sk\\n AND ws_sold_date_sk = d_date_sk\\n AND d_year = 2001\\n AND d_moy = 2\\n AND ws_bill_addr_sk = ca_address_sk\\n AND ca_gmt_offset = -5\\n GROUP BY i_item_id)\\nSELECT i_item_id,\\n sum(total_sales) total_sales\\nFROM\\n (SELECT *\\n FROM ss\\n UNION ALL SELECT *\\n FROM cs\\n UNION ALL SELECT *\\n FROM ws) tmp1\\nGROUP BY i_item_id\\nORDER BY total_sales NULLS FIRST,\\n i_item_id NULLS FIRST\\nLIMIT 100;\",\n", " 'Q57': 'WITH v1 AS\\n (SELECT i_category,\\n i_brand,\\n cc_name,\\n d_year,\\n d_moy,\\n sum(cs_sales_price) sum_sales,\\n avg(sum(cs_sales_price)) OVER (PARTITION BY i_category,\\n i_brand,\\n cc_name,\\n d_year) avg_monthly_sales,\\n rank() OVER (PARTITION BY i_category,\\n i_brand,\\n cc_name\\n ORDER BY d_year,\\n d_moy) rn\\n FROM testdata.tpcds_tiny.item,\\n testdata.tpcds_tiny.catalog_sales,\\n testdata.tpcds_tiny.date_dim,\\n testdata.tpcds_tiny.call_center\\n WHERE cs_item_sk = i_item_sk\\n AND cs_sold_date_sk = d_date_sk\\n AND cc_call_center_sk= cs_call_center_sk\\n AND (d_year = 1999\\n OR (d_year = 1999-1\\n AND d_moy =12)\\n OR (d_year = 1999+1\\n AND d_moy =1))\\n GROUP BY i_category,\\n i_brand,\\n cc_name,\\n d_year,\\n d_moy),\\n v2 AS\\n (SELECT v1.i_category,\\n v1.i_brand,\\n v1.cc_name,\\n v1.d_year,\\n v1.d_moy,\\n v1.avg_monthly_sales,\\n v1.sum_sales,\\n v1_lag.sum_sales psum,\\n v1_lead.sum_sales nsum\\n FROM v1,\\n v1 v1_lag,\\n v1 v1_lead\\n WHERE v1.i_category = v1_lag.i_category\\n AND v1.i_category = v1_lead.i_category\\n AND v1.i_brand = v1_lag.i_brand\\n AND v1.i_brand = v1_lead.i_brand\\n AND v1. cc_name = v1_lag. cc_name\\n AND v1. cc_name = v1_lead. cc_name\\n AND v1.rn = v1_lag.rn + 1\\n AND v1.rn = v1_lead.rn - 1)\\nSELECT *\\nFROM v2\\nWHERE d_year = 1999\\n AND avg_monthly_sales > 0\\n AND CASE\\n WHEN avg_monthly_sales > 0 THEN abs(sum_sales - avg_monthly_sales) / avg_monthly_sales\\n ELSE NULL\\n END > 0.1\\nORDER BY sum_sales - avg_monthly_sales NULLS FIRST, 1, 2, 3, 4, 5, 6, 7, 8, 9\\nLIMIT 100;',\n", " 'Q58': \"WITH ss_items AS\\n (SELECT i_item_id item_id,\\n sum(ss_ext_sales_price) ss_item_rev\\n FROM testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.item,\\n testdata.tpcds_tiny.date_dim\\n WHERE ss_item_sk = i_item_sk\\n AND d_date IN\\n (SELECT d_date\\n FROM testdata.tpcds_tiny.date_dim\\n WHERE d_week_seq =\\n (SELECT d_week_seq\\n FROM testdata.tpcds_tiny.date_dim\\n WHERE d_date = cast('2000-01-03' AS date)))\\n AND ss_sold_date_sk = d_date_sk\\n GROUP BY i_item_id),\\n cs_items AS\\n (SELECT i_item_id item_id,\\n sum(cs_ext_sales_price) cs_item_rev\\n FROM testdata.tpcds_tiny.catalog_sales,\\n testdata.tpcds_tiny.item,\\n testdata.tpcds_tiny.date_dim\\n WHERE cs_item_sk = i_item_sk\\n AND d_date IN\\n (SELECT d_date\\n FROM testdata.tpcds_tiny.date_dim\\n WHERE d_week_seq =\\n (SELECT d_week_seq\\n FROM testdata.tpcds_tiny.date_dim\\n WHERE d_date = cast('2000-01-03' AS date)))\\n AND cs_sold_date_sk = d_date_sk\\n GROUP BY i_item_id),\\n ws_items AS\\n (SELECT i_item_id item_id,\\n sum(ws_ext_sales_price) ws_item_rev\\n FROM testdata.tpcds_tiny.web_sales,\\n testdata.tpcds_tiny.item,\\n testdata.tpcds_tiny.date_dim\\n WHERE ws_item_sk = i_item_sk\\n AND d_date IN\\n (SELECT d_date\\n FROM testdata.tpcds_tiny.date_dim\\n WHERE d_week_seq =\\n (SELECT d_week_seq\\n FROM testdata.tpcds_tiny.date_dim\\n WHERE d_date = cast('2000-01-03' AS date)))\\n AND ws_sold_date_sk = d_date_sk\\n GROUP BY i_item_id)\\nSELECT ss_items.item_id,\\n ss_item_rev,\\n ss_item_rev/((ss_item_rev+cs_item_rev+ws_item_rev)/3) * 100 ss_dev,\\n cs_item_rev,\\n cs_item_rev/((ss_item_rev+cs_item_rev+ws_item_rev)/3) * 100 cs_dev,\\n ws_item_rev,\\n ws_item_rev/((ss_item_rev+cs_item_rev+ws_item_rev)/3) * 100 ws_dev,\\n (ss_item_rev+cs_item_rev+ws_item_rev)/3 average\\nFROM ss_items,\\n cs_items,\\n ws_items\\nWHERE ss_items.item_id=cs_items.item_id\\n AND ss_items.item_id=ws_items.item_id\\n AND ss_item_rev BETWEEN 0.9 * cs_item_rev AND 1.1 * cs_item_rev\\n AND ss_item_rev BETWEEN 0.9 * ws_item_rev AND 1.1 * ws_item_rev\\n AND cs_item_rev BETWEEN 0.9 * ss_item_rev AND 1.1 * ss_item_rev\\n AND cs_item_rev BETWEEN 0.9 * ws_item_rev AND 1.1 * ws_item_rev\\n AND ws_item_rev BETWEEN 0.9 * ss_item_rev AND 1.1 * ss_item_rev\\n AND ws_item_rev BETWEEN 0.9 * cs_item_rev AND 1.1 * cs_item_rev\\nORDER BY ss_items.item_id NULLS FIRST,\\n ss_item_rev NULLS FIRST\\nLIMIT 100;\",\n", " 'Q59': \"WITH wss AS\\n (SELECT d_week_seq,\\n ss_store_sk,\\n sum(CASE\\n WHEN (d_day_name='Sunday') THEN ss_sales_price\\n ELSE NULL\\n END) sun_sales,\\n sum(CASE\\n WHEN (d_day_name='Monday') THEN ss_sales_price\\n ELSE NULL\\n END) mon_sales,\\n sum(CASE\\n WHEN (d_day_name='Tuesday') THEN ss_sales_price\\n ELSE NULL\\n END) tue_sales,\\n sum(CASE\\n WHEN (d_day_name='Wednesday') THEN ss_sales_price\\n ELSE NULL\\n END) wed_sales,\\n sum(CASE\\n WHEN (d_day_name='Thursday') THEN ss_sales_price\\n ELSE NULL\\n END) thu_sales,\\n sum(CASE\\n WHEN (d_day_name='Friday') THEN ss_sales_price\\n ELSE NULL\\n END) fri_sales,\\n sum(CASE\\n WHEN (d_day_name='Saturday') THEN ss_sales_price\\n ELSE NULL\\n END) sat_sales\\n FROM testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.date_dim\\n WHERE d_date_sk = ss_sold_date_sk\\n GROUP BY d_week_seq,\\n ss_store_sk)\\nSELECT s_store_name1,\\n s_store_id1,\\n d_week_seq1,\\n sun_sales1/sun_sales2 AS sun_sales_ratio,\\n mon_sales1/mon_sales2 AS mon_sales_ratio,\\n tue_sales1/tue_sales2 AS tue_sales_ratio,\\n wed_sales1/wed_sales2 AS wed_sales_ratio,\\n thu_sales1/thu_sales2 AS thu_sales_ratio,\\n fri_sales1/fri_sales2 AS fri_sales_ratio,\\n sat_sales1/sat_sales2 AS sat_sales_ratio\\nFROM\\n (SELECT s_store_name s_store_name1,\\n wss.d_week_seq d_week_seq1,\\n s_store_id s_store_id1,\\n sun_sales sun_sales1,\\n mon_sales mon_sales1,\\n tue_sales tue_sales1,\\n wed_sales wed_sales1,\\n thu_sales thu_sales1,\\n fri_sales fri_sales1,\\n sat_sales sat_sales1\\n FROM wss,\\n testdata.tpcds_tiny.store,\\n testdata.tpcds_tiny.date_dim d\\n WHERE d.d_week_seq = wss.d_week_seq\\n AND ss_store_sk = s_store_sk\\n AND d_month_seq BETWEEN 1212 AND 1212 + 11) y,\\n (SELECT s_store_name s_store_name2,\\n wss.d_week_seq d_week_seq2,\\n s_store_id s_store_id2,\\n sun_sales sun_sales2,\\n mon_sales mon_sales2,\\n tue_sales tue_sales2,\\n wed_sales wed_sales2,\\n thu_sales thu_sales2,\\n fri_sales fri_sales2,\\n sat_sales sat_sales2\\n FROM wss,\\n testdata.tpcds_tiny.store,\\n testdata.tpcds_tiny.date_dim d\\n WHERE d.d_week_seq = wss.d_week_seq\\n AND ss_store_sk = s_store_sk\\n AND d_month_seq BETWEEN 1212 + 12 AND 1212 + 23) x\\nWHERE s_store_id1=s_store_id2\\n AND d_week_seq1=d_week_seq2-52\\nORDER BY s_store_name1 NULLS FIRST,\\n s_store_id1 NULLS FIRST,\\n d_week_seq1 NULLS FIRST\\nLIMIT 100;\",\n", " 'Q60': \"WITH ss AS\\n (SELECT i_item_id,\\n sum(ss_ext_sales_price) total_sales\\n FROM testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.date_dim,\\n testdata.tpcds_tiny.customer_address,\\n testdata.tpcds_tiny.item\\n WHERE i_item_id IN\\n (SELECT i_item_id\\n FROM testdata.tpcds_tiny.item\\n WHERE i_category ='Music')\\n AND ss_item_sk = i_item_sk\\n AND ss_sold_date_sk = d_date_sk\\n AND d_year = 1998\\n AND d_moy = 9\\n AND ss_addr_sk = ca_address_sk\\n AND ca_gmt_offset = -5\\n GROUP BY i_item_id),\\n cs AS\\n (SELECT i_item_id,\\n sum(cs_ext_sales_price) total_sales\\n FROM testdata.tpcds_tiny.catalog_sales,\\n testdata.tpcds_tiny.date_dim,\\n testdata.tpcds_tiny.customer_address,\\n testdata.tpcds_tiny.item\\n WHERE i_item_id IN\\n (SELECT i_item_id\\n FROM testdata.tpcds_tiny.item\\n WHERE i_category ='Music')\\n AND cs_item_sk = i_item_sk\\n AND cs_sold_date_sk = d_date_sk\\n AND d_year = 1998\\n AND d_moy = 9\\n AND cs_bill_addr_sk = ca_address_sk\\n AND ca_gmt_offset = -5\\n GROUP BY i_item_id),\\n ws AS\\n (SELECT i_item_id,\\n sum(ws_ext_sales_price) total_sales\\n FROM testdata.tpcds_tiny.web_sales,\\n testdata.tpcds_tiny.date_dim,\\n testdata.tpcds_tiny.customer_address,\\n testdata.tpcds_tiny.item\\n WHERE i_item_id IN\\n (SELECT i_item_id\\n FROM testdata.tpcds_tiny.item\\n WHERE i_category = 'Music')\\n AND ws_item_sk = i_item_sk\\n AND ws_sold_date_sk = d_date_sk\\n AND d_year = 1998\\n AND d_moy = 9\\n AND ws_bill_addr_sk = ca_address_sk\\n AND ca_gmt_offset = -5\\n GROUP BY i_item_id)\\nSELECT i_item_id,\\n sum(total_sales) total_sales\\nFROM\\n (SELECT *\\n FROM ss\\n UNION ALL SELECT *\\n FROM cs\\n UNION ALL SELECT *\\n FROM ws) tmp1\\nGROUP BY i_item_id\\nORDER BY i_item_id,\\n total_sales\\nLIMIT 100;\",\n", " 'Q61': \"SELECT promotions,\\n total,\\n cast(promotions AS decimal(15,4))/cast(total AS decimal(15,4))*100\\nFROM\\n (SELECT sum(ss_ext_sales_price) promotions\\n FROM testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.store,\\n testdata.tpcds_tiny.promotion,\\n testdata.tpcds_tiny.date_dim,\\n testdata.tpcds_tiny.customer,\\n testdata.tpcds_tiny.customer_address,\\n testdata.tpcds_tiny.item\\n WHERE ss_sold_date_sk = d_date_sk\\n AND ss_store_sk = s_store_sk\\n AND ss_promo_sk = p_promo_sk\\n AND ss_customer_sk= c_customer_sk\\n AND ca_address_sk = c_current_addr_sk\\n AND ss_item_sk = i_item_sk\\n AND ca_gmt_offset = -5\\n AND i_category = 'Jewelry'\\n AND (p_channel_dmail = 'Y'\\n OR p_channel_email = 'Y'\\n OR p_channel_tv = 'Y')\\n AND s_gmt_offset = -5\\n AND d_year = 1998\\n AND d_moy = 11) promotional_sales,\\n (SELECT sum(ss_ext_sales_price) total\\n FROM testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.store,\\n testdata.tpcds_tiny.date_dim,\\n testdata.tpcds_tiny.customer,\\n testdata.tpcds_tiny.customer_address,\\n testdata.tpcds_tiny.item\\n WHERE ss_sold_date_sk = d_date_sk\\n AND ss_store_sk = s_store_sk\\n AND ss_customer_sk= c_customer_sk\\n AND ca_address_sk = c_current_addr_sk\\n AND ss_item_sk = i_item_sk\\n AND ca_gmt_offset = -5\\n AND i_category = 'Jewelry'\\n AND s_gmt_offset = -5\\n AND d_year = 1998\\n AND d_moy = 11) all_sales\\nORDER BY promotions,\\n total\\nLIMIT 100;\",\n", " 'Q62': 'SELECT w_substr,\\n sm_type,\\n web_name,\\n sum(CASE\\n WHEN (ws_ship_date_sk - ws_sold_date_sk <= 30) THEN 1\\n ELSE 0\\n END) AS \"30 days\",\\n sum(CASE\\n WHEN (ws_ship_date_sk - ws_sold_date_sk > 30)\\n AND (ws_ship_date_sk - ws_sold_date_sk <= 60) THEN 1\\n ELSE 0\\n END) AS \"31-60 days\",\\n sum(CASE\\n WHEN (ws_ship_date_sk - ws_sold_date_sk > 60)\\n AND (ws_ship_date_sk - ws_sold_date_sk <= 90) THEN 1\\n ELSE 0\\n END) AS \"61-90 days\",\\n sum(CASE\\n WHEN (ws_ship_date_sk - ws_sold_date_sk > 90)\\n AND (ws_ship_date_sk - ws_sold_date_sk <= 120) THEN 1\\n ELSE 0\\n END) AS \"91-120 days\",\\n sum(CASE\\n WHEN (ws_ship_date_sk - ws_sold_date_sk > 120) THEN 1\\n ELSE 0\\n END) AS \">120 days\"\\nFROM testdata.tpcds_tiny.web_sales,\\n (SELECT testdata.tpcds_tiny.warehouse.*,\\n SUBSTRING(w_warehouse_name,1,20) w_substr\\n FROM testdata.tpcds_tiny.warehouse) sq1,\\n testdata.tpcds_tiny.ship_mode,\\n testdata.tpcds_tiny.web_site,\\n testdata.tpcds_tiny.date_dim\\nWHERE d_month_seq BETWEEN 1200 AND 1200 + 11\\n AND ws_ship_date_sk = d_date_sk\\n AND ws_warehouse_sk = w_warehouse_sk\\n AND ws_ship_mode_sk = sm_ship_mode_sk\\n AND ws_web_site_sk = web_site_sk\\nGROUP BY w_substr,\\n sm_type,\\n web_name\\nORDER BY 1 NULLS FIRST,\\n 2 NULLS FIRST,\\n 3 NULLS FIRST\\nLIMIT 100;',\n", " 'Q63': \"SELECT *\\nFROM\\n (SELECT i_manager_id,\\n sum(ss_sales_price) sum_sales,\\n avg(sum(ss_sales_price)) OVER (PARTITION BY i_manager_id) avg_monthly_sales\\n FROM testdata.tpcds_tiny.item,\\n testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.date_dim,\\n testdata.tpcds_tiny.store\\n WHERE ss_item_sk = i_item_sk\\n AND ss_sold_date_sk = d_date_sk\\n AND ss_store_sk = s_store_sk\\n AND d_month_seq IN (1200,\\n 1200+1,\\n 1200+2,\\n 1200+3,\\n 1200+4,\\n 1200+5,\\n 1200+6,\\n 1200+7,\\n 1200+8,\\n 1200+9,\\n 1200+10,\\n 1200+11)\\n AND ((i_category IN ('Books',\\n 'Children',\\n 'Electronics')\\n AND i_class IN ('personal',\\n 'portable',\\n 'reference',\\n 'self-help')\\n AND i_brand IN ('scholaramalgamalg #14',\\n 'scholaramalgamalg #7',\\n 'exportiunivamalg #9',\\n 'scholaramalgamalg #9')) or(i_category IN ('Women','Music','Men')\\n AND i_class IN ('accessories','classical','fragrances','pants')\\n AND i_brand IN ('amalgimporto #1','edu packscholar #1','exportiimporto #1', 'importoamalg #1')))\\n GROUP BY i_manager_id,\\n d_moy) tmp1\\nWHERE CASE\\n WHEN avg_monthly_sales > 0 THEN ABS (sum_sales - avg_monthly_sales) / avg_monthly_sales\\n ELSE NULL\\n END > 0.1\\nORDER BY i_manager_id,\\n avg_monthly_sales,\\n sum_sales\\nLIMIT 100;\",\n", " 'Q64': \"WITH cs_ui AS\\n (SELECT cs_item_sk,\\n sum(cs_ext_list_price) AS sale,\\n sum(cr_refunded_cash+cr_reversed_charge+cr_store_credit) AS refund\\n FROM testdata.tpcds_tiny.catalog_sales,\\n testdata.tpcds_tiny.catalog_returns\\n WHERE cs_item_sk = cr_item_sk\\n AND cs_order_number = cr_order_number\\n GROUP BY cs_item_sk\\n HAVING sum(cs_ext_list_price)>2*sum(cr_refunded_cash+cr_reversed_charge+cr_store_credit)),\\n cross_sales AS\\n (SELECT i_product_name product_name,\\n i_item_sk item_sk,\\n s_store_name store_name,\\n s_zip store_zip,\\n ad1.ca_street_number b_street_number,\\n ad1.ca_street_name b_street_name,\\n ad1.ca_city b_city,\\n ad1.ca_zip b_zip,\\n ad2.ca_street_number c_street_number,\\n ad2.ca_street_name c_street_name,\\n ad2.ca_city c_city,\\n ad2.ca_zip c_zip,\\n d1.d_year AS syear,\\n d2.d_year AS fsyear,\\n d3.d_year s2year,\\n count(*) cnt,\\n sum(ss_wholesale_cost) s1,\\n sum(ss_list_price) s2,\\n sum(ss_coupon_amt) s3\\n FROM testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.store_returns,\\n cs_ui,\\n testdata.tpcds_tiny.date_dim d1,\\n testdata.tpcds_tiny.date_dim d2,\\n testdata.tpcds_tiny.date_dim d3,\\n testdata.tpcds_tiny.store,\\n testdata.tpcds_tiny.customer,\\n testdata.tpcds_tiny.customer_demographics cd1,\\n testdata.tpcds_tiny.customer_demographics cd2,\\n testdata.tpcds_tiny.promotion,\\n testdata.tpcds_tiny.household_demographics hd1,\\n testdata.tpcds_tiny.household_demographics hd2,\\n testdata.tpcds_tiny.customer_address ad1,\\n testdata.tpcds_tiny.customer_address ad2,\\n testdata.tpcds_tiny.income_band ib1,\\n testdata.tpcds_tiny.income_band ib2,\\n testdata.tpcds_tiny.item\\n WHERE ss_store_sk = s_store_sk\\n AND ss_sold_date_sk = d1.d_date_sk\\n AND ss_customer_sk = c_customer_sk\\n AND ss_cdemo_sk= cd1.cd_demo_sk\\n AND ss_hdemo_sk = hd1.hd_demo_sk\\n AND ss_addr_sk = ad1.ca_address_sk\\n AND ss_item_sk = i_item_sk\\n AND ss_item_sk = sr_item_sk\\n AND ss_ticket_number = sr_ticket_number\\n AND ss_item_sk = cs_ui.cs_item_sk\\n AND c_current_cdemo_sk = cd2.cd_demo_sk\\n AND c_current_hdemo_sk = hd2.hd_demo_sk\\n AND c_current_addr_sk = ad2.ca_address_sk\\n AND c_first_sales_date_sk = d2.d_date_sk\\n AND c_first_shipto_date_sk = d3.d_date_sk\\n AND ss_promo_sk = p_promo_sk\\n AND hd1.hd_income_band_sk = ib1.ib_income_band_sk\\n AND hd2.hd_income_band_sk = ib2.ib_income_band_sk\\n AND cd1.cd_marital_status <> cd2.cd_marital_status\\n AND i_color IN ('purple',\\n 'burlywood',\\n 'indian',\\n 'spring',\\n 'floral',\\n 'medium')\\n AND i_current_price BETWEEN 64 AND 64 + 10\\n AND i_current_price BETWEEN 64 + 1 AND 64 + 15\\n GROUP BY i_product_name,\\n i_item_sk,\\n s_store_name,\\n s_zip,\\n ad1.ca_street_number,\\n ad1.ca_street_name,\\n ad1.ca_city,\\n ad1.ca_zip,\\n ad2.ca_street_number,\\n ad2.ca_street_name,\\n ad2.ca_city,\\n ad2.ca_zip,\\n d1.d_year,\\n d2.d_year,\\n d3.d_year)\\nSELECT cs1.product_name,\\n cs1.store_name,\\n cs1.store_zip,\\n cs1.b_street_number,\\n cs1.b_street_name,\\n cs1.b_city,\\n cs1.b_zip,\\n cs1.c_street_number,\\n cs1.c_street_name,\\n cs1.c_city,\\n cs1.c_zip,\\n cs1.syear cs1syear,\\n cs1.cnt cs1cnt,\\n cs1.s1 AS s11,\\n cs1.s2 AS s21,\\n cs1.s3 AS s31,\\n cs2.s1 AS s12,\\n cs2.s2 AS s22,\\n cs2.s3 AS s32,\\n cs2.syear,\\n cs2.cnt\\nFROM cross_sales cs1,\\n cross_sales cs2\\nWHERE cs1.item_sk=cs2.item_sk\\n AND cs1.syear = 1999\\n AND cs2.syear = 1999 + 1\\n AND cs2.cnt <= cs1.cnt\\n AND cs1.store_name = cs2.store_name\\n AND cs1.store_zip = cs2.store_zip\\nORDER BY cs1.product_name,\\n cs1.store_name,\\n cs2.cnt,\\n cs1.s1,\\n cs2.s1;\",\n", " 'Q65': 'SELECT s_store_name,\\n i_item_desc,\\n sc.revenue,\\n i_current_price,\\n i_wholesale_cost,\\n i_brand\\nFROM testdata.tpcds_tiny.store,\\n testdata.tpcds_tiny.item,\\n (SELECT ss_store_sk,\\n avg(revenue) AS ave\\n FROM\\n (SELECT ss_store_sk,\\n ss_item_sk,\\n sum(ss_sales_price) AS revenue\\n FROM testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.date_dim\\n WHERE ss_sold_date_sk = d_date_sk\\n AND d_month_seq BETWEEN 1176 AND 1176+11\\n GROUP BY ss_store_sk,\\n ss_item_sk) sa\\n GROUP BY ss_store_sk) sb,\\n (SELECT ss_store_sk,\\n ss_item_sk,\\n sum(ss_sales_price) AS revenue\\n FROM testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.date_dim\\n WHERE ss_sold_date_sk = d_date_sk\\n AND d_month_seq BETWEEN 1176 AND 1176+11\\n GROUP BY ss_store_sk,\\n ss_item_sk) sc\\nWHERE sb.ss_store_sk = sc.ss_store_sk\\n AND sc.revenue <= 0.1 * sb.ave\\n AND s_store_sk = sc.ss_store_sk\\n AND i_item_sk = sc.ss_item_sk\\nORDER BY s_store_name NULLS FIRST,\\n i_item_desc NULLS FIRST\\nLIMIT 100;',\n", " 'Q66': \"select\\n w_warehouse_name\\n ,w_warehouse_sq_ft\\n ,w_city\\n ,w_county\\n ,w_state\\n ,w_country\\n ,ship_carriers\\n ,year_\\n ,sum(jan_sales) as jan_sales\\n ,sum(feb_sales) as feb_sales\\n ,sum(mar_sales) as mar_sales\\n ,sum(apr_sales) as apr_sales\\n ,sum(may_sales) as may_sales\\n ,sum(jun_sales) as jun_sales\\n ,sum(jul_sales) as jul_sales\\n ,sum(aug_sales) as aug_sales\\n ,sum(sep_sales) as sep_sales\\n ,sum(oct_sales) as oct_sales\\n ,sum(nov_sales) as nov_sales\\n ,sum(dec_sales) as dec_sales\\n ,sum(jan_sales/w_warehouse_sq_ft) as jan_sales_per_sq_foot\\n ,sum(feb_sales/w_warehouse_sq_ft) as feb_sales_per_sq_foot\\n ,sum(mar_sales/w_warehouse_sq_ft) as mar_sales_per_sq_foot\\n ,sum(apr_sales/w_warehouse_sq_ft) as apr_sales_per_sq_foot\\n ,sum(may_sales/w_warehouse_sq_ft) as may_sales_per_sq_foot\\n ,sum(jun_sales/w_warehouse_sq_ft) as jun_sales_per_sq_foot\\n ,sum(jul_sales/w_warehouse_sq_ft) as jul_sales_per_sq_foot\\n ,sum(aug_sales/w_warehouse_sq_ft) as aug_sales_per_sq_foot\\n ,sum(sep_sales/w_warehouse_sq_ft) as sep_sales_per_sq_foot\\n ,sum(oct_sales/w_warehouse_sq_ft) as oct_sales_per_sq_foot\\n ,sum(nov_sales/w_warehouse_sq_ft) as nov_sales_per_sq_foot\\n ,sum(dec_sales/w_warehouse_sq_ft) as dec_sales_per_sq_foot\\n ,sum(jan_net) as jan_net\\n ,sum(feb_net) as feb_net\\n ,sum(mar_net) as mar_net\\n ,sum(apr_net) as apr_net\\n ,sum(may_net) as may_net\\n ,sum(jun_net) as jun_net\\n ,sum(jul_net) as jul_net\\n ,sum(aug_net) as aug_net\\n ,sum(sep_net) as sep_net\\n ,sum(oct_net) as oct_net\\n ,sum(nov_net) as nov_net\\n ,sum(dec_net) as dec_net\\n from (\\n select\\n w_warehouse_name\\n ,w_warehouse_sq_ft\\n ,w_city\\n ,w_county\\n ,w_state\\n ,w_country\\n ,'DHL,BARIAN' as ship_carriers\\n ,d_year as year_\\n ,sum(case when d_moy = 1\\n then ws_ext_sales_price* ws_quantity else 0 end) as jan_sales\\n ,sum(case when d_moy = 2\\n then ws_ext_sales_price* ws_quantity else 0 end) as feb_sales\\n ,sum(case when d_moy = 3\\n then ws_ext_sales_price* ws_quantity else 0 end) as mar_sales\\n ,sum(case when d_moy = 4\\n then ws_ext_sales_price* ws_quantity else 0 end) as apr_sales\\n ,sum(case when d_moy = 5\\n then ws_ext_sales_price* ws_quantity else 0 end) as may_sales\\n ,sum(case when d_moy = 6\\n then ws_ext_sales_price* ws_quantity else 0 end) as jun_sales\\n ,sum(case when d_moy = 7\\n then ws_ext_sales_price* ws_quantity else 0 end) as jul_sales\\n ,sum(case when d_moy = 8\\n then ws_ext_sales_price* ws_quantity else 0 end) as aug_sales\\n ,sum(case when d_moy = 9\\n then ws_ext_sales_price* ws_quantity else 0 end) as sep_sales\\n ,sum(case when d_moy = 10\\n then ws_ext_sales_price* ws_quantity else 0 end) as oct_sales\\n ,sum(case when d_moy = 11\\n then ws_ext_sales_price* ws_quantity else 0 end) as nov_sales\\n ,sum(case when d_moy = 12\\n then ws_ext_sales_price* ws_quantity else 0 end) as dec_sales\\n ,sum(case when d_moy = 1\\n then ws_net_paid * ws_quantity else 0 end) as jan_net\\n ,sum(case when d_moy = 2\\n then ws_net_paid * ws_quantity else 0 end) as feb_net\\n ,sum(case when d_moy = 3\\n then ws_net_paid * ws_quantity else 0 end) as mar_net\\n ,sum(case when d_moy = 4\\n then ws_net_paid * ws_quantity else 0 end) as apr_net\\n ,sum(case when d_moy = 5\\n then ws_net_paid * ws_quantity else 0 end) as may_net\\n ,sum(case when d_moy = 6\\n then ws_net_paid * ws_quantity else 0 end) as jun_net\\n ,sum(case when d_moy = 7\\n then ws_net_paid * ws_quantity else 0 end) as jul_net\\n ,sum(case when d_moy = 8\\n then ws_net_paid * ws_quantity else 0 end) as aug_net\\n ,sum(case when d_moy = 9\\n then ws_net_paid * ws_quantity else 0 end) as sep_net\\n ,sum(case when d_moy = 10\\n then ws_net_paid * ws_quantity else 0 end) as oct_net\\n ,sum(case when d_moy = 11\\n then ws_net_paid * ws_quantity else 0 end) as nov_net\\n ,sum(case when d_moy = 12\\n then ws_net_paid * ws_quantity else 0 end) as dec_net\\n from\\n testdata.tpcds_tiny.web_sales\\n ,testdata.tpcds_tiny.warehouse\\n ,testdata.tpcds_tiny.date_dim\\n ,testdata.tpcds_tiny.time_dim\\n ,testdata.tpcds_tiny.ship_mode\\n where\\n ws_warehouse_sk = w_warehouse_sk\\n and ws_sold_date_sk = d_date_sk\\n and ws_sold_time_sk = t_time_sk\\n and ws_ship_mode_sk = sm_ship_mode_sk\\n and d_year = 2001\\n and t_time between 30838 and 30838+28800\\n and sm_carrier in ('DHL','BARIAN')\\n group by\\n w_warehouse_name\\n ,w_warehouse_sq_ft\\n ,w_city\\n ,w_county\\n ,w_state\\n ,w_country\\n ,d_year\\n union all\\n select\\n w_warehouse_name\\n ,w_warehouse_sq_ft\\n ,w_city\\n ,w_county\\n ,w_state\\n ,w_country\\n ,'DHL,BARIAN' as ship_carriers\\n ,d_year as year_\\n ,sum(case when d_moy = 1\\n then cs_sales_price* cs_quantity else 0 end) as jan_sales\\n ,sum(case when d_moy = 2\\n then cs_sales_price* cs_quantity else 0 end) as feb_sales\\n ,sum(case when d_moy = 3\\n then cs_sales_price* cs_quantity else 0 end) as mar_sales\\n ,sum(case when d_moy = 4\\n then cs_sales_price* cs_quantity else 0 end) as apr_sales\\n ,sum(case when d_moy = 5\\n then cs_sales_price* cs_quantity else 0 end) as may_sales\\n ,sum(case when d_moy = 6\\n then cs_sales_price* cs_quantity else 0 end) as jun_sales\\n ,sum(case when d_moy = 7\\n then cs_sales_price* cs_quantity else 0 end) as jul_sales\\n ,sum(case when d_moy = 8\\n then cs_sales_price* cs_quantity else 0 end) as aug_sales\\n ,sum(case when d_moy = 9\\n then cs_sales_price* cs_quantity else 0 end) as sep_sales\\n ,sum(case when d_moy = 10\\n then cs_sales_price* cs_quantity else 0 end) as oct_sales\\n ,sum(case when d_moy = 11\\n then cs_sales_price* cs_quantity else 0 end) as nov_sales\\n ,sum(case when d_moy = 12\\n then cs_sales_price* cs_quantity else 0 end) as dec_sales\\n ,sum(case when d_moy = 1\\n then cs_net_paid_inc_tax * cs_quantity else 0 end) as jan_net\\n ,sum(case when d_moy = 2\\n then cs_net_paid_inc_tax * cs_quantity else 0 end) as feb_net\\n ,sum(case when d_moy = 3\\n then cs_net_paid_inc_tax * cs_quantity else 0 end) as mar_net\\n ,sum(case when d_moy = 4\\n then cs_net_paid_inc_tax * cs_quantity else 0 end) as apr_net\\n ,sum(case when d_moy = 5\\n then cs_net_paid_inc_tax * cs_quantity else 0 end) as may_net\\n ,sum(case when d_moy = 6\\n then cs_net_paid_inc_tax * cs_quantity else 0 end) as jun_net\\n ,sum(case when d_moy = 7\\n then cs_net_paid_inc_tax * cs_quantity else 0 end) as jul_net\\n ,sum(case when d_moy = 8\\n then cs_net_paid_inc_tax * cs_quantity else 0 end) as aug_net\\n ,sum(case when d_moy = 9\\n then cs_net_paid_inc_tax * cs_quantity else 0 end) as sep_net\\n ,sum(case when d_moy = 10\\n then cs_net_paid_inc_tax * cs_quantity else 0 end) as oct_net\\n ,sum(case when d_moy = 11\\n then cs_net_paid_inc_tax * cs_quantity else 0 end) as nov_net\\n ,sum(case when d_moy = 12\\n then cs_net_paid_inc_tax * cs_quantity else 0 end) as dec_net\\n from\\n testdata.tpcds_tiny.catalog_sales\\n ,testdata.tpcds_tiny.warehouse\\n ,testdata.tpcds_tiny.date_dim\\n ,testdata.tpcds_tiny.time_dim\\n ,testdata.tpcds_tiny.ship_mode\\n where\\n cs_warehouse_sk = w_warehouse_sk\\n and cs_sold_date_sk = d_date_sk\\n and cs_sold_time_sk = t_time_sk\\n and cs_ship_mode_sk = sm_ship_mode_sk\\n and d_year = 2001\\n and t_time between 30838 AND 30838+28800\\n and sm_carrier in ('DHL','BARIAN')\\n group by\\n w_warehouse_name\\n ,w_warehouse_sq_ft\\n ,w_city\\n ,w_county\\n ,w_state\\n ,w_country\\n ,d_year\\n ) x\\n group by\\n w_warehouse_name\\n ,w_warehouse_sq_ft\\n ,w_city\\n ,w_county\\n ,w_state\\n ,w_country\\n ,ship_carriers\\n ,year_\\n order by w_warehouse_name NULLS FIRST\\nLIMIT 100;\",\n", " 'Q67': 'SELECT *\\nFROM\\n (SELECT i_category,\\n i_class,\\n i_brand,\\n i_product_name,\\n d_year,\\n d_qoy,\\n d_moy,\\n s_store_id,\\n sumsales,\\n rank() OVER (PARTITION BY i_category\\n ORDER BY sumsales DESC) rk\\n FROM\\n (SELECT i_category,\\n i_class,\\n i_brand,\\n i_product_name,\\n d_year,\\n d_qoy,\\n d_moy,\\n s_store_id,\\n sum(coalesce(ss_sales_price*ss_quantity,0)) sumsales\\n FROM testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.date_dim,\\n testdata.tpcds_tiny.store,\\n testdata.tpcds_tiny.item\\n WHERE ss_sold_date_sk=d_date_sk\\n AND ss_item_sk=i_item_sk\\n AND ss_store_sk = s_store_sk\\n AND d_month_seq BETWEEN 1200 AND 1200+11\\n GROUP BY rollup(i_category, i_class, i_brand, i_product_name, d_year, d_qoy, d_moy,s_store_id))dw1) dw2\\nWHERE rk <= 100\\nORDER BY i_category NULLS FIRST,\\n i_class NULLS FIRST,\\n i_brand NULLS FIRST,\\n i_product_name NULLS FIRST,\\n d_year NULLS FIRST,\\n d_qoy NULLS FIRST,\\n d_moy NULLS FIRST,\\n s_store_id NULLS FIRST,\\n sumsales NULLS FIRST,\\n rk NULLS FIRST\\nLIMIT 100;',\n", " 'Q68': \"SELECT c_last_name,\\n c_first_name,\\n ca_city,\\n bought_city,\\n ss_ticket_number,\\n extended_price,\\n extended_tax,\\n list_price\\nFROM\\n (SELECT ss_ticket_number,\\n ss_customer_sk,\\n ca_city bought_city,\\n sum(ss_ext_sales_price) extended_price,\\n sum(ss_ext_list_price) list_price,\\n sum(ss_ext_tax) extended_tax\\n FROM testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.date_dim,\\n testdata.tpcds_tiny.store,\\n testdata.tpcds_tiny.household_demographics,\\n testdata.tpcds_tiny.customer_address\\n WHERE testdata.tpcds_tiny.store_sales.ss_sold_date_sk = testdata.tpcds_tiny.date_dim.d_date_sk\\n AND testdata.tpcds_tiny.store_sales.ss_store_sk = testdata.tpcds_tiny.store.s_store_sk\\n AND testdata.tpcds_tiny.store_sales.ss_hdemo_sk = testdata.tpcds_tiny.household_demographics.hd_demo_sk\\n AND testdata.tpcds_tiny.store_sales.ss_addr_sk = testdata.tpcds_tiny.customer_address.ca_address_sk\\n AND testdata.tpcds_tiny.date_dim.d_dom BETWEEN 1 AND 2\\n AND (testdata.tpcds_tiny.household_demographics.hd_dep_count = 4\\n OR testdata.tpcds_tiny.household_demographics.hd_vehicle_count= 3)\\n AND testdata.tpcds_tiny.date_dim.d_year IN (1999,\\n 1999+1,\\n 1999+2)\\n AND testdata.tpcds_tiny.store.s_city IN ('Fairview',\\n 'Midway')\\n GROUP BY ss_ticket_number,\\n ss_customer_sk,\\n ss_addr_sk,\\n ca_city) dn,\\n testdata.tpcds_tiny.customer,\\n testdata.tpcds_tiny.customer_address current_addr\\nWHERE ss_customer_sk = c_customer_sk\\n AND testdata.tpcds_tiny.customer.c_current_addr_sk = current_addr.ca_address_sk\\n AND current_addr.ca_city <> bought_city\\nORDER BY c_last_name NULLS FIRST,\\n ss_ticket_number NULLS FIRST\\nLIMIT 100;\",\n", " 'Q69': \"SELECT cd_gender,\\n cd_marital_status,\\n cd_education_status,\\n count(*) cnt1,\\n cd_purchase_estimate,\\n count(*) cnt2,\\n cd_credit_rating,\\n count(*) cnt3\\nFROM testdata.tpcds_tiny.customer c,\\n testdata.tpcds_tiny.customer_address ca,\\n testdata.tpcds_tiny.customer_demographics\\nWHERE c.c_current_addr_sk = ca.ca_address_sk\\n AND ca_state IN ('KY',\\n 'GA',\\n 'NM')\\n AND cd_demo_sk = c.c_current_cdemo_sk\\n AND EXISTS\\n (SELECT *\\n FROM testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.date_dim\\n WHERE c.c_customer_sk = ss_customer_sk\\n AND ss_sold_date_sk = d_date_sk\\n AND d_year = 2001\\n AND d_moy BETWEEN 4 AND 4+2)\\n AND (NOT EXISTS\\n (SELECT *\\n FROM testdata.tpcds_tiny.web_sales,\\n testdata.tpcds_tiny.date_dim\\n WHERE c.c_customer_sk = ws_bill_customer_sk\\n AND ws_sold_date_sk = d_date_sk\\n AND d_year = 2001\\n AND d_moy BETWEEN 4 AND 4+2)\\n AND NOT EXISTS\\n (SELECT *\\n FROM testdata.tpcds_tiny.catalog_sales,\\n testdata.tpcds_tiny.date_dim\\n WHERE c.c_customer_sk = cs_ship_customer_sk\\n AND cs_sold_date_sk = d_date_sk\\n AND d_year = 2001\\n AND d_moy BETWEEN 4 AND 4+2))\\nGROUP BY cd_gender,\\n cd_marital_status,\\n cd_education_status,\\n cd_purchase_estimate,\\n cd_credit_rating\\nORDER BY cd_gender,\\n cd_marital_status,\\n cd_education_status,\\n cd_purchase_estimate,\\n cd_credit_rating\\nLIMIT 100;\",\n", " 'Q70': 'SELECT sum(ss_net_profit) AS total_sum,\\n s_state,\\n s_county,\\n grouping(s_state)+grouping(s_county) AS lochierarchy,\\n rank() OVER (PARTITION BY grouping(s_state)+grouping(s_county),\\n CASE\\n WHEN grouping(s_county) = 0 THEN s_state\\n END\\n ORDER BY sum(ss_net_profit) DESC) AS rank_within_parent\\nFROM testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.date_dim d1,\\n testdata.tpcds_tiny.store\\nWHERE d1.d_month_seq BETWEEN 1200 AND 1200+11\\n AND d1.d_date_sk = ss_sold_date_sk\\n AND s_store_sk = ss_store_sk\\n AND s_state IN\\n (SELECT s_state\\n FROM\\n (SELECT s_state AS s_state,\\n rank() OVER (PARTITION BY s_state\\n ORDER BY sum(ss_net_profit) DESC) AS ranking\\n FROM testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.store,\\n testdata.tpcds_tiny.date_dim\\n WHERE d_month_seq BETWEEN 1200 AND 1200+11\\n AND d_date_sk = ss_sold_date_sk\\n AND s_store_sk = ss_store_sk\\n GROUP BY s_state) tmp1\\n WHERE ranking <= 5 )\\nGROUP BY rollup(s_state,s_county)\\nORDER BY lochierarchy DESC ,\\n CASE\\n WHEN grouping(s_state)+grouping(s_county) = 0 THEN s_state\\n END ,\\n rank_within_parent\\nLIMIT 100;',\n", " 'Q71': \"SELECT i_brand_id brand_id,\\n i_brand brand,\\n t_hour,\\n t_minute,\\n sum(ext_price) ext_price\\nFROM testdata.tpcds_tiny.item,\\n (SELECT ws_ext_sales_price AS ext_price,\\n ws_sold_date_sk AS sold_date_sk,\\n ws_item_sk AS sold_item_sk,\\n ws_sold_time_sk AS time_sk\\n FROM testdata.tpcds_tiny.web_sales,\\n testdata.tpcds_tiny.date_dim\\n WHERE d_date_sk = ws_sold_date_sk\\n AND d_moy=11\\n AND d_year=1999\\n UNION ALL SELECT cs_ext_sales_price AS ext_price,\\n cs_sold_date_sk AS sold_date_sk,\\n cs_item_sk AS sold_item_sk,\\n cs_sold_time_sk AS time_sk\\n FROM testdata.tpcds_tiny.catalog_sales,\\n testdata.tpcds_tiny.date_dim\\n WHERE d_date_sk = cs_sold_date_sk\\n AND d_moy=11\\n AND d_year=1999\\n UNION ALL SELECT ss_ext_sales_price AS ext_price,\\n ss_sold_date_sk AS sold_date_sk,\\n ss_item_sk AS sold_item_sk,\\n ss_sold_time_sk AS time_sk\\n FROM testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.date_dim\\n WHERE d_date_sk = ss_sold_date_sk\\n AND d_moy=11\\n AND d_year=1999 ) tmp,\\n testdata.tpcds_tiny.time_dim\\nWHERE sold_item_sk = i_item_sk\\n AND i_manager_id=1\\n AND time_sk = t_time_sk\\n AND (t_meal_time = 'breakfast'\\n OR t_meal_time = 'dinner')\\nGROUP BY i_brand,\\n i_brand_id,\\n t_hour,\\n t_minute\\nORDER BY ext_price DESC NULLS FIRST,\\n i_brand_id NULLS FIRST,\\n t_hour NULLS FIRST;\",\n", " 'Q72': \"SELECT i_item_desc,\\n w_warehouse_name,\\n d1.d_week_seq,\\n sum(CASE\\n WHEN p_promo_sk IS NULL THEN 1\\n ELSE 0\\n END) no_promo,\\n sum(CASE\\n WHEN p_promo_sk IS NOT NULL THEN 1\\n ELSE 0\\n END) promo,\\n count(*) total_cnt\\nFROM testdata.tpcds_tiny.catalog_sales\\nJOIN testdata.tpcds_tiny.inventory ON (cs_item_sk = inv_item_sk)\\nJOIN testdata.tpcds_tiny.warehouse ON (w_warehouse_sk=inv_warehouse_sk)\\nJOIN testdata.tpcds_tiny.item ON (i_item_sk = cs_item_sk)\\nJOIN testdata.tpcds_tiny.customer_demographics ON (cs_bill_cdemo_sk = cd_demo_sk)\\nJOIN testdata.tpcds_tiny.household_demographics ON (cs_bill_hdemo_sk = hd_demo_sk)\\nJOIN testdata.tpcds_tiny.date_dim d1 ON (cs_sold_date_sk = d1.d_date_sk)\\nJOIN testdata.tpcds_tiny.date_dim d2 ON (inv_date_sk = d2.d_date_sk)\\nJOIN testdata.tpcds_tiny.date_dim d3 ON (cs_ship_date_sk = d3.d_date_sk)\\nLEFT OUTER JOIN testdata.tpcds_tiny.promotion ON (cs_promo_sk=p_promo_sk)\\nLEFT OUTER JOIN testdata.tpcds_tiny.catalog_returns ON (cr_item_sk = cs_item_sk\\n AND cr_order_number = cs_order_number)\\nWHERE d1.d_week_seq = d2.d_week_seq\\n AND inv_quantity_on_hand < cs_quantity\\n AND d3.d_date > d1.d_date + INTERVAL '5' DAY -- SQL Server: DATEADD(day, 5, d1.d_date)\\n AND hd_buy_potential = '>10000'\\n AND d1.d_year = 1999\\n AND cd_marital_status = 'D'\\nGROUP BY i_item_desc,\\n w_warehouse_name,\\n d1.d_week_seq\\nORDER BY total_cnt DESC NULLS FIRST,\\n i_item_desc NULLS FIRST,\\n w_warehouse_name NULLS FIRST,\\n d1.d_week_seq NULLS FIRST\\nLIMIT 100;\",\n", " 'Q73': \"SELECT c_last_name,\\n c_first_name,\\n c_salutation,\\n c_preferred_cust_flag,\\n ss_ticket_number,\\n cnt\\nFROM\\n (SELECT ss_ticket_number,\\n ss_customer_sk,\\n count(*) cnt\\n FROM testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.date_dim,\\n testdata.tpcds_tiny.store,\\n testdata.tpcds_tiny.household_demographics\\n WHERE testdata.tpcds_tiny.store_sales.ss_sold_date_sk = testdata.tpcds_tiny.date_dim.d_date_sk\\n AND testdata.tpcds_tiny.store_sales.ss_store_sk = testdata.tpcds_tiny.store.s_store_sk\\n AND testdata.tpcds_tiny.store_sales.ss_hdemo_sk = testdata.tpcds_tiny.household_demographics.hd_demo_sk\\n AND testdata.tpcds_tiny.date_dim.d_dom BETWEEN 1 AND 2\\n AND (testdata.tpcds_tiny.household_demographics.hd_buy_potential = 'Unknown'\\n OR testdata.tpcds_tiny.household_demographics.hd_buy_potential = '>10000')\\n AND testdata.tpcds_tiny.household_demographics.hd_vehicle_count > 0\\n AND CASE\\n WHEN testdata.tpcds_tiny.household_demographics.hd_vehicle_count > 0 THEN (testdata.tpcds_tiny.household_demographics.hd_dep_count*1.000)/ testdata.tpcds_tiny.household_demographics.hd_vehicle_count\\n ELSE NULL\\n END > 1\\n AND testdata.tpcds_tiny.date_dim.d_year IN (1999,\\n 1999+1,\\n 1999+2)\\n AND testdata.tpcds_tiny.store.s_county IN ('Orange County',\\n 'Bronx County',\\n 'Franklin Parish',\\n 'Williamson County')\\n GROUP BY ss_ticket_number,\\n ss_customer_sk) dj,\\n testdata.tpcds_tiny.customer\\nWHERE ss_customer_sk = c_customer_sk\\n AND cnt BETWEEN 1 AND 5\\nORDER BY cnt DESC,\\n c_last_name ASC;\",\n", " 'Q74': \"WITH year_total AS\\n (SELECT c_customer_id customer_id,\\n c_first_name customer_first_name,\\n c_last_name customer_last_name,\\n d_year AS year_,\\n sum(ss_net_paid) year_total,\\n 's' sale_type\\n FROM testdata.tpcds_tiny.customer,\\n testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.date_dim\\n WHERE c_customer_sk = ss_customer_sk\\n AND ss_sold_date_sk = d_date_sk\\n AND d_year IN (2001,\\n 2001+1)\\n GROUP BY c_customer_id,\\n c_first_name,\\n c_last_name,\\n d_year\\n UNION ALL SELECT c_customer_id customer_id,\\n c_first_name customer_first_name,\\n c_last_name customer_last_name,\\n d_year AS year_,\\n sum(ws_net_paid) year_total,\\n 'w' sale_type\\n FROM testdata.tpcds_tiny.customer,\\n testdata.tpcds_tiny.web_sales,\\n testdata.tpcds_tiny.date_dim\\n WHERE c_customer_sk = ws_bill_customer_sk\\n AND ws_sold_date_sk = d_date_sk\\n AND d_year IN (2001,\\n 2001+1)\\n GROUP BY c_customer_id,\\n c_first_name,\\n c_last_name,\\n d_year)\\nSELECT t_s_secyear.customer_id,\\n t_s_secyear.customer_first_name,\\n t_s_secyear.customer_last_name\\nFROM year_total t_s_firstyear,\\n year_total t_s_secyear,\\n year_total t_w_firstyear,\\n year_total t_w_secyear\\nWHERE t_s_secyear.customer_id = t_s_firstyear.customer_id\\n AND t_s_firstyear.customer_id = t_w_secyear.customer_id\\n AND t_s_firstyear.customer_id = t_w_firstyear.customer_id\\n AND t_s_firstyear.sale_type = 's'\\n AND t_w_firstyear.sale_type = 'w'\\n AND t_s_secyear.sale_type = 's'\\n AND t_w_secyear.sale_type = 'w'\\n AND t_s_firstyear.year_ = 2001\\n AND t_s_secyear.year_ = 2001+1\\n AND t_w_firstyear.year_ = 2001\\n AND t_w_secyear.year_ = 2001+1\\n AND t_s_firstyear.year_total > 0\\n AND t_w_firstyear.year_total > 0\\n AND CASE\\n WHEN t_w_firstyear.year_total > 0 THEN t_w_secyear.year_total / t_w_firstyear.year_total\\n ELSE NULL\\n END > CASE\\n WHEN t_s_firstyear.year_total > 0 THEN t_s_secyear.year_total / t_s_firstyear.year_total\\n ELSE NULL\\n END\\nORDER BY 1 NULLS FIRST\\nLIMIT 100;\",\n", " 'Q75': \"WITH all_sales AS\\n ( SELECT d_year ,\\n i_brand_id ,\\n i_class_id ,\\n i_category_id ,\\n i_manufact_id ,\\n SUM(sales_cnt) AS sales_cnt ,\\n SUM(sales_amt) AS sales_amt\\n FROM\\n (SELECT d_year ,\\n i_brand_id ,\\n i_class_id ,\\n i_category_id ,\\n i_manufact_id ,\\n cs_quantity - COALESCE(cr_return_quantity,0) AS sales_cnt ,\\n cs_ext_sales_price - COALESCE(cr_return_amount,0.0) AS sales_amt\\n FROM testdata.tpcds_tiny.catalog_sales\\n JOIN testdata.tpcds_tiny.item ON i_item_sk=cs_item_sk\\n JOIN testdata.tpcds_tiny.date_dim ON d_date_sk=cs_sold_date_sk\\n LEFT JOIN testdata.tpcds_tiny.catalog_returns ON (cs_order_number=cr_order_number\\n AND cs_item_sk=cr_item_sk)\\n WHERE i_category='Books'\\n UNION SELECT d_year ,\\n i_brand_id ,\\n i_class_id ,\\n i_category_id ,\\n i_manufact_id ,\\n ss_quantity - COALESCE(sr_return_quantity,0) AS sales_cnt ,\\n ss_ext_sales_price - COALESCE(sr_return_amt,0.0) AS sales_amt\\n FROM testdata.tpcds_tiny.store_sales\\n JOIN testdata.tpcds_tiny.item ON i_item_sk=ss_item_sk\\n JOIN testdata.tpcds_tiny.date_dim ON d_date_sk=ss_sold_date_sk\\n LEFT JOIN testdata.tpcds_tiny.store_returns ON (ss_ticket_number=sr_ticket_number\\n AND ss_item_sk=sr_item_sk)\\n WHERE i_category='Books'\\n UNION SELECT d_year ,\\n i_brand_id ,\\n i_class_id ,\\n i_category_id ,\\n i_manufact_id ,\\n ws_quantity - COALESCE(wr_return_quantity,0) AS sales_cnt ,\\n ws_ext_sales_price - COALESCE(wr_return_amt,0.0) AS sales_amt\\n FROM testdata.tpcds_tiny.web_sales\\n JOIN testdata.tpcds_tiny.item ON i_item_sk=ws_item_sk\\n JOIN testdata.tpcds_tiny.date_dim ON d_date_sk=ws_sold_date_sk\\n LEFT JOIN testdata.tpcds_tiny.web_returns ON (ws_order_number=wr_order_number\\n AND ws_item_sk=wr_item_sk)\\n WHERE i_category='Books') sales_detail\\n GROUP BY d_year,\\n i_brand_id,\\n i_class_id,\\n i_category_id,\\n i_manufact_id)\\nSELECT prev_yr.d_year AS prev_year ,\\n curr_yr.d_year AS year_ ,\\n curr_yr.i_brand_id ,\\n curr_yr.i_class_id ,\\n curr_yr.i_category_id ,\\n curr_yr.i_manufact_id ,\\n prev_yr.sales_cnt AS prev_yr_cnt ,\\n curr_yr.sales_cnt AS curr_yr_cnt ,\\n curr_yr.sales_cnt-prev_yr.sales_cnt AS sales_cnt_diff ,\\n curr_yr.sales_amt-prev_yr.sales_amt AS sales_amt_diff\\nFROM all_sales curr_yr,\\n all_sales prev_yr\\nWHERE curr_yr.i_brand_id=prev_yr.i_brand_id\\n AND curr_yr.i_class_id=prev_yr.i_class_id\\n AND curr_yr.i_category_id=prev_yr.i_category_id\\n AND curr_yr.i_manufact_id=prev_yr.i_manufact_id\\n AND curr_yr.d_year=2002\\n AND prev_yr.d_year=2002-1\\n AND CAST(curr_yr.sales_cnt AS DECIMAL(17,2))/CAST(prev_yr.sales_cnt AS DECIMAL(17,2))<0.9\\nORDER BY sales_cnt_diff,\\n sales_amt_diff\\nLIMIT 100;\",\n", " 'Q76': \"SELECT channel,\\n col_name,\\n d_year,\\n d_qoy,\\n i_category,\\n COUNT(*) sales_cnt,\\n SUM(ext_sales_price) sales_amt\\nFROM\\n ( SELECT 'store' AS channel,\\n 'ss_store_sk' col_name,\\n d_year,\\n d_qoy,\\n i_category,\\n ss_ext_sales_price ext_sales_price\\n FROM testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.item,\\n testdata.tpcds_tiny.date_dim\\n WHERE ss_store_sk IS NULL\\n AND ss_sold_date_sk=d_date_sk\\n AND ss_item_sk=i_item_sk\\n UNION ALL SELECT 'web' AS channel,\\n 'ws_ship_customer_sk' col_name,\\n d_year,\\n d_qoy,\\n i_category,\\n ws_ext_sales_price ext_sales_price\\n FROM testdata.tpcds_tiny.web_sales,\\n testdata.tpcds_tiny.item,\\n testdata.tpcds_tiny.date_dim\\n WHERE ws_ship_customer_sk IS NULL\\n AND ws_sold_date_sk=d_date_sk\\n AND ws_item_sk=i_item_sk\\n UNION ALL SELECT 'catalog' AS channel,\\n 'cs_ship_addr_sk' col_name,\\n d_year,\\n d_qoy,\\n i_category,\\n cs_ext_sales_price ext_sales_price\\n FROM testdata.tpcds_tiny.catalog_sales,\\n testdata.tpcds_tiny.item,\\n testdata.tpcds_tiny.date_dim\\n WHERE cs_ship_addr_sk IS NULL\\n AND cs_sold_date_sk=d_date_sk\\n AND cs_item_sk=i_item_sk) foo\\nGROUP BY channel,\\n col_name,\\n d_year,\\n d_qoy,\\n i_category\\nORDER BY channel NULLS FIRST,\\n col_name NULLS FIRST,\\n d_year NULLS FIRST,\\n d_qoy NULLS FIRST,\\n i_category NULLS FIRST\\nLIMIT 100;\",\n", " 'Q77': \"WITH ss AS\\n (SELECT s_store_sk,\\n sum(ss_ext_sales_price) AS sales,\\n sum(ss_net_profit) AS profit\\n FROM testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.date_dim,\\n testdata.tpcds_tiny.store\\n WHERE ss_sold_date_sk = d_date_sk\\n AND d_date BETWEEN cast('2000-08-23' AS date) AND cast('2000-09-22' AS date)\\n AND ss_store_sk = s_store_sk\\n GROUP BY s_store_sk) ,\\n sr AS\\n (SELECT s_store_sk,\\n sum(sr_return_amt) AS returns_,\\n sum(sr_net_loss) AS profit_loss\\n FROM testdata.tpcds_tiny.store_returns,\\n testdata.tpcds_tiny.date_dim,\\n testdata.tpcds_tiny.store\\n WHERE sr_returned_date_sk = d_date_sk\\n AND d_date BETWEEN cast('2000-08-23' AS date) AND cast('2000-09-22' AS date)\\n AND sr_store_sk = s_store_sk\\n GROUP BY s_store_sk),\\n cs AS\\n (SELECT cs_call_center_sk,\\n sum(cs_ext_sales_price) AS sales,\\n sum(cs_net_profit) AS profit\\n FROM testdata.tpcds_tiny.catalog_sales,\\n testdata.tpcds_tiny.date_dim\\n WHERE cs_sold_date_sk = d_date_sk\\n AND d_date BETWEEN cast('2000-08-23' AS date) AND cast('2000-09-22' AS date)\\n GROUP BY cs_call_center_sk),\\n cr AS\\n (SELECT cr_call_center_sk,\\n sum(cr_return_amount) AS returns_,\\n sum(cr_net_loss) AS profit_loss\\n FROM testdata.tpcds_tiny.catalog_returns,\\n testdata.tpcds_tiny.date_dim\\n WHERE cr_returned_date_sk = d_date_sk\\n AND d_date BETWEEN cast('2000-08-23' AS date) AND cast('2000-09-22' AS date)\\n GROUP BY cr_call_center_sk ),\\n ws AS\\n (SELECT wp_web_page_sk,\\n sum(ws_ext_sales_price) AS sales,\\n sum(ws_net_profit) AS profit\\n FROM testdata.tpcds_tiny.web_sales,\\n testdata.tpcds_tiny.date_dim,\\n testdata.tpcds_tiny.web_page\\n WHERE ws_sold_date_sk = d_date_sk\\n AND d_date BETWEEN cast('2000-08-23' AS date) AND cast('2000-09-22' AS date)\\n AND ws_web_page_sk = wp_web_page_sk\\n GROUP BY wp_web_page_sk),\\n wr AS\\n (SELECT wp_web_page_sk,\\n sum(wr_return_amt) AS returns_,\\n sum(wr_net_loss) AS profit_loss\\n FROM testdata.tpcds_tiny.web_returns,\\n testdata.tpcds_tiny.date_dim,\\n testdata.tpcds_tiny.web_page\\n WHERE wr_returned_date_sk = d_date_sk\\n AND d_date BETWEEN cast('2000-08-23' AS date) AND cast('2000-09-22' AS date)\\n AND wr_web_page_sk = wp_web_page_sk\\n GROUP BY wp_web_page_sk)\\nSELECT channel ,\\n id ,\\n sum(sales) AS sales ,\\n sum(returns_) AS returns_ ,\\n sum(profit) AS profit\\nFROM\\n (SELECT 'store channel' AS channel ,\\n ss.s_store_sk AS id ,\\n sales ,\\n coalesce(returns_, 0) AS returns_ ,\\n (profit - coalesce(profit_loss,0)) AS profit\\n FROM ss\\n LEFT JOIN sr ON ss.s_store_sk = sr.s_store_sk\\n UNION ALL SELECT 'catalog channel' AS channel ,\\n cs_call_center_sk AS id ,\\n sales ,\\n returns_ ,\\n (profit - profit_loss) AS profit\\n FROM cs ,\\n cr\\n UNION ALL SELECT 'web channel' AS channel ,\\n ws.wp_web_page_sk AS id ,\\n sales ,\\n coalesce(returns_, 0) returns_ ,\\n (profit - coalesce(profit_loss,0)) AS profit\\n FROM ws\\n LEFT JOIN wr ON ws.wp_web_page_sk = wr.wp_web_page_sk ) x\\nGROUP BY ROLLUP (channel,\\n id)\\nORDER BY channel NULLS FIRST,\\n id NULLS FIRST,\\n returns_ DESC\\nLIMIT 100;\",\n", " 'Q78': 'WITH ws AS\\n (SELECT d_year AS ws_sold_year,\\n ws_item_sk,\\n ws_bill_customer_sk ws_customer_sk,\\n sum(ws_quantity) ws_qty,\\n sum(ws_wholesale_cost) ws_wc,\\n sum(ws_sales_price) ws_sp\\n FROM testdata.tpcds_tiny.web_sales\\n LEFT JOIN testdata.tpcds_tiny.web_returns ON wr_order_number=ws_order_number\\n AND ws_item_sk=wr_item_sk\\n JOIN testdata.tpcds_tiny.date_dim ON ws_sold_date_sk = d_date_sk\\n WHERE wr_order_number IS NULL\\n GROUP BY d_year,\\n ws_item_sk,\\n ws_bill_customer_sk ),\\n cs AS\\n (SELECT d_year AS cs_sold_year,\\n cs_item_sk,\\n cs_bill_customer_sk cs_customer_sk,\\n sum(cs_quantity) cs_qty,\\n sum(cs_wholesale_cost) cs_wc,\\n sum(cs_sales_price) cs_sp\\n FROM testdata.tpcds_tiny.catalog_sales\\n LEFT JOIN testdata.tpcds_tiny.catalog_returns ON cr_order_number=cs_order_number\\n AND cs_item_sk=cr_item_sk\\n JOIN testdata.tpcds_tiny.date_dim ON cs_sold_date_sk = d_date_sk\\n WHERE cr_order_number IS NULL\\n GROUP BY d_year,\\n cs_item_sk,\\n cs_bill_customer_sk ),\\n ss AS\\n (SELECT d_year AS ss_sold_year,\\n ss_item_sk,\\n ss_customer_sk,\\n sum(ss_quantity) ss_qty,\\n sum(ss_wholesale_cost) ss_wc,\\n sum(ss_sales_price) ss_sp\\n FROM testdata.tpcds_tiny.store_sales\\n LEFT JOIN testdata.tpcds_tiny.store_returns ON sr_ticket_number=ss_ticket_number\\n AND ss_item_sk=sr_item_sk\\n JOIN testdata.tpcds_tiny.date_dim ON ss_sold_date_sk = d_date_sk\\n WHERE sr_ticket_number IS NULL\\n GROUP BY d_year,\\n ss_item_sk,\\n ss_customer_sk )\\nSELECT ss_sold_year,\\n ss_item_sk,\\n ss_customer_sk,\\n round((ss_qty*1.00)/(coalesce(ws_qty,0)+coalesce(cs_qty,0)),2) ratio,\\n ss_qty store_qty,\\n ss_wc store_wholesale_cost,\\n ss_sp store_sales_price,\\n coalesce(ws_qty,0)+coalesce(cs_qty,0) other_chan_qty,\\n coalesce(ws_wc,0)+coalesce(cs_wc,0) other_chan_wholesale_cost,\\n coalesce(ws_sp,0)+coalesce(cs_sp,0) other_chan_sales_price\\nFROM ss\\nLEFT JOIN ws ON (ws_sold_year=ss_sold_year\\n AND ws_item_sk=ss_item_sk\\n AND ws_customer_sk=ss_customer_sk)\\nLEFT JOIN cs ON (cs_sold_year=ss_sold_year\\n AND cs_item_sk=ss_item_sk\\n AND cs_customer_sk=ss_customer_sk)\\nWHERE (coalesce(ws_qty,0)>0\\n OR coalesce(cs_qty, 0)>0)\\n AND ss_sold_year=2000\\nORDER BY ss_sold_year,\\n ss_item_sk,\\n ss_customer_sk,\\n ss_qty DESC,\\n ss_wc DESC,\\n ss_sp DESC,\\n other_chan_qty,\\n other_chan_wholesale_cost,\\n other_chan_sales_price,\\n ratio\\nLIMIT 100;',\n", " 'Q79': 'SELECT c_last_name,\\n c_first_name,\\n SUBSTRING(s_city,1,30),\\n ss_ticket_number,\\n amt,\\n profit\\nFROM\\n (SELECT ss_ticket_number ,\\n ss_customer_sk ,\\n testdata.tpcds_tiny.store.s_city ,\\n sum(ss_coupon_amt) amt ,\\n sum(ss_net_profit) profit\\n FROM testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.date_dim,\\n testdata.tpcds_tiny.store,\\n testdata.tpcds_tiny.household_demographics\\n WHERE testdata.tpcds_tiny.store_sales.ss_sold_date_sk = testdata.tpcds_tiny.date_dim.d_date_sk\\n AND testdata.tpcds_tiny.store_sales.ss_store_sk = testdata.tpcds_tiny.store.s_store_sk\\n AND testdata.tpcds_tiny.store_sales.ss_hdemo_sk = testdata.tpcds_tiny.household_demographics.hd_demo_sk\\n AND (testdata.tpcds_tiny.household_demographics.hd_dep_count = 6\\n OR testdata.tpcds_tiny.household_demographics.hd_vehicle_count > 2)\\n AND testdata.tpcds_tiny.date_dim.d_dow = 1\\n AND testdata.tpcds_tiny.date_dim.d_year IN (1999,\\n 1999+1,\\n 1999+2)\\n AND testdata.tpcds_tiny.store.s_number_employees BETWEEN 200 AND 295\\n GROUP BY ss_ticket_number,\\n ss_customer_sk,\\n ss_addr_sk,\\n testdata.tpcds_tiny.store.s_city) ms,\\n testdata.tpcds_tiny.customer\\nWHERE ss_customer_sk = c_customer_sk\\nORDER BY c_last_name NULLS FIRST,\\n c_first_name NULLS FIRST,\\n SUBSTRING(s_city,1,30) NULLS FIRST,\\n profit NULLS FIRST,\\n ss_ticket_number\\nLIMIT 100;',\n", " 'Q80': \"WITH ssr AS\\n (SELECT s_store_id AS store_id,\\n sum(ss_ext_sales_price) AS sales,\\n sum(coalesce(sr_return_amt, 0)) AS returns_,\\n sum(ss_net_profit - coalesce(sr_net_loss, 0)) AS profit\\n FROM testdata.tpcds_tiny.store_sales\\n LEFT OUTER JOIN testdata.tpcds_tiny.store_returns ON (ss_item_sk = sr_item_sk\\n AND ss_ticket_number = sr_ticket_number), testdata.tpcds_tiny.date_dim,\\n testdata.tpcds_tiny.store,\\n testdata.tpcds_tiny.item,\\n testdata.tpcds_tiny.promotion\\n WHERE ss_sold_date_sk = d_date_sk\\n AND d_date BETWEEN cast('2000-08-23' AS date) AND cast('2000-09-22' AS date)\\n AND ss_store_sk = s_store_sk\\n AND ss_item_sk = i_item_sk\\n AND i_current_price > 50\\n AND ss_promo_sk = p_promo_sk\\n AND p_channel_tv = 'N'\\n GROUP BY s_store_id) ,\\n csr AS\\n (SELECT cp_catalog_page_id AS catalog_page_id,\\n sum(cs_ext_sales_price) AS sales,\\n sum(coalesce(cr_return_amount, 0)) AS returns_,\\n sum(cs_net_profit - coalesce(cr_net_loss, 0)) AS profit\\n FROM testdata.tpcds_tiny.catalog_sales\\n LEFT OUTER JOIN testdata.tpcds_tiny.catalog_returns ON (cs_item_sk = cr_item_sk\\n AND cs_order_number = cr_order_number), testdata.tpcds_tiny.date_dim,\\n testdata.tpcds_tiny.catalog_page,\\n testdata.tpcds_tiny.item,\\n testdata.tpcds_tiny.promotion\\n WHERE cs_sold_date_sk = d_date_sk\\n AND d_date BETWEEN cast('2000-08-23' AS date) AND cast('2000-09-22' AS date)\\n AND cs_catalog_page_sk = cp_catalog_page_sk\\n AND cs_item_sk = i_item_sk\\n AND i_current_price > 50\\n AND cs_promo_sk = p_promo_sk\\n AND p_channel_tv = 'N'\\n GROUP BY cp_catalog_page_id) ,\\n wsr AS\\n (SELECT web_site_id,\\n sum(ws_ext_sales_price) AS sales,\\n sum(coalesce(wr_return_amt, 0)) AS returns_,\\n sum(ws_net_profit - coalesce(wr_net_loss, 0)) AS profit\\n FROM testdata.tpcds_tiny.web_sales\\n LEFT OUTER JOIN testdata.tpcds_tiny.web_returns ON (ws_item_sk = wr_item_sk\\n AND ws_order_number = wr_order_number), testdata.tpcds_tiny.date_dim,\\n testdata.tpcds_tiny.web_site,\\n testdata.tpcds_tiny.item,\\n testdata.tpcds_tiny.promotion\\n WHERE ws_sold_date_sk = d_date_sk\\n AND d_date BETWEEN cast('2000-08-23' AS date) AND cast('2000-09-22' AS date)\\n AND ws_web_site_sk = web_site_sk\\n AND ws_item_sk = i_item_sk\\n AND i_current_price > 50\\n AND ws_promo_sk = p_promo_sk\\n AND p_channel_tv = 'N'\\n GROUP BY web_site_id)\\nSELECT channel ,\\n id ,\\n sum(sales) AS sales ,\\n sum(returns_) AS returns_ ,\\n sum(profit) AS profit\\nFROM\\n (SELECT 'store channel' AS channel ,\\n concat('store', store_id) AS id ,\\n sales ,\\n returns_ ,\\n profit\\n FROM ssr\\n UNION ALL SELECT 'catalog channel' AS channel ,\\n concat('catalog_page', catalog_page_id) AS id ,\\n sales ,\\n returns_ ,\\n profit\\n FROM csr\\n UNION ALL SELECT 'web channel' AS channel ,\\n concat('web_site', web_site_id) AS id ,\\n sales ,\\n returns_ ,\\n profit\\n FROM wsr ) x\\nGROUP BY ROLLUP (channel,\\n id)\\nORDER BY channel NULLS FIRST,\\n id NULLS FIRST\\nLIMIT 100;\",\n", " 'Q81': \"WITH customer_total_return AS\\n (SELECT cr_returning_customer_sk AS ctr_customer_sk ,\\n ca_state AS ctr_state,\\n sum(cr_return_amt_inc_tax) AS ctr_total_return\\n FROM testdata.tpcds_tiny.catalog_returns ,\\n testdata.tpcds_tiny.date_dim ,\\n testdata.tpcds_tiny.customer_address\\n WHERE cr_returned_date_sk = d_date_sk\\n AND d_year = 2000\\n AND cr_returning_addr_sk = ca_address_sk\\n GROUP BY cr_returning_customer_sk ,\\n ca_state)\\nSELECT c_customer_id,\\n c_salutation,\\n c_first_name,\\n c_last_name,\\n ca_street_number,\\n ca_street_name ,\\n ca_street_type,\\n ca_suite_number,\\n ca_city,\\n ca_county,\\n ca_state,\\n ca_zip,\\n ca_country,\\n ca_gmt_offset ,\\n ca_location_type,\\n ctr_total_return\\nFROM customer_total_return ctr1 ,\\n testdata.tpcds_tiny.customer_address ,\\n testdata.tpcds_tiny.customer\\nWHERE ctr1.ctr_total_return >\\n (SELECT avg(ctr_total_return)*1.2\\n FROM customer_total_return ctr2\\n WHERE ctr1.ctr_state = ctr2.ctr_state)\\n AND ca_address_sk = c_current_addr_sk\\n AND ca_state = 'GA'\\n AND ctr1.ctr_customer_sk = c_customer_sk\\nORDER BY c_customer_id,\\n c_salutation,\\n c_first_name,\\n c_last_name,\\n ca_street_number,\\n ca_street_name ,\\n ca_street_type,\\n ca_suite_number,\\n ca_city,\\n ca_county,\\n ca_state,\\n ca_zip,\\n ca_country,\\n ca_gmt_offset ,\\n ca_location_type,\\n ctr_total_return\\nLIMIT 100;\",\n", " 'Q82': \"SELECT i_item_id ,\\n i_item_desc ,\\n i_current_price\\nFROM testdata.tpcds_tiny.item,\\n testdata.tpcds_tiny.inventory,\\n testdata.tpcds_tiny.date_dim,\\n testdata.tpcds_tiny.store_sales\\nWHERE i_current_price BETWEEN 62 AND 62+30\\n AND inv_item_sk = i_item_sk\\n AND d_date_sk=inv_date_sk\\n AND d_date BETWEEN cast('2000-05-25' AS date) AND cast('2000-07-24' AS date)\\n AND i_manufact_id IN (129,\\n 270,\\n 821,\\n 423)\\n AND inv_quantity_on_hand BETWEEN 100 AND 500\\n AND ss_item_sk = i_item_sk\\nGROUP BY i_item_id,\\n i_item_desc,\\n i_current_price\\nORDER BY i_item_id\\nLIMIT 100;\",\n", " 'Q83': \"WITH sr_items AS\\n (SELECT i_item_id item_id,\\n sum(sr_return_quantity) sr_item_qty\\n FROM testdata.tpcds_tiny.store_returns,\\n testdata.tpcds_tiny.item,\\n testdata.tpcds_tiny.date_dim\\n WHERE sr_item_sk = i_item_sk\\n AND d_date IN\\n (SELECT d_date\\n FROM testdata.tpcds_tiny.date_dim\\n WHERE d_week_seq IN\\n (SELECT d_week_seq\\n FROM testdata.tpcds_tiny.date_dim\\n WHERE d_date IN (cast('2000-06-30' AS date),\\n cast('2000-09-27' AS date),\\n cast('2000-11-17' AS date))))\\n AND sr_returned_date_sk = d_date_sk\\n GROUP BY i_item_id),\\n cr_items AS\\n (SELECT i_item_id item_id,\\n sum(cr_return_quantity) cr_item_qty\\n FROM testdata.tpcds_tiny.catalog_returns,\\n testdata.tpcds_tiny.item,\\n testdata.tpcds_tiny.date_dim\\n WHERE cr_item_sk = i_item_sk\\n AND d_date IN\\n (SELECT d_date\\n FROM testdata.tpcds_tiny.date_dim\\n WHERE d_week_seq IN\\n (SELECT d_week_seq\\n FROM testdata.tpcds_tiny.date_dim\\n WHERE d_date IN (cast('2000-06-30' AS date),\\n cast('2000-09-27' AS date),\\n cast('2000-11-17' AS date))))\\n AND cr_returned_date_sk = d_date_sk\\n GROUP BY i_item_id),\\n wr_items AS\\n (SELECT i_item_id item_id,\\n sum(wr_return_quantity) wr_item_qty\\n FROM testdata.tpcds_tiny.web_returns,\\n testdata.tpcds_tiny.item,\\n testdata.tpcds_tiny.date_dim\\n WHERE wr_item_sk = i_item_sk\\n AND d_date IN\\n (SELECT d_date\\n FROM testdata.tpcds_tiny.date_dim\\n WHERE d_week_seq IN\\n (SELECT d_week_seq\\n FROM testdata.tpcds_tiny.date_dim\\n WHERE d_date IN (cast('2000-06-30' AS date),\\n cast('2000-09-27' AS date),\\n cast('2000-11-17' AS date))))\\n AND wr_returned_date_sk = d_date_sk\\n GROUP BY i_item_id)\\nSELECT sr_items.item_id ,\\n sr_item_qty ,\\n (sr_item_qty*1.0000)/(sr_item_qty+cr_item_qty+wr_item_qty)/3.0000 * 100 sr_dev ,\\n cr_item_qty ,\\n (cr_item_qty*1.0000)/(sr_item_qty+cr_item_qty+wr_item_qty)/3.0000 * 100 cr_dev ,\\n wr_item_qty ,\\n (wr_item_qty*1.0000)/(sr_item_qty+cr_item_qty+wr_item_qty)/3.0000 * 100 wr_dev ,\\n (sr_item_qty+cr_item_qty+wr_item_qty)/3.0 average\\nFROM sr_items ,\\n cr_items ,\\n wr_items\\nWHERE sr_items.item_id=cr_items.item_id\\n AND sr_items.item_id=wr_items.item_id\\nORDER BY sr_items.item_id NULLS FIRST,\\n sr_item_qty NULLS FIRST\\nLIMIT 100;\",\n", " 'Q84': \"SELECT c_customer_id AS customer_id ,\\n concat(concat(coalesce(c_last_name, '') , ', '), coalesce(c_first_name, '')) AS customername\\nFROM testdata.tpcds_tiny.customer ,\\n testdata.tpcds_tiny.customer_address ,\\n testdata.tpcds_tiny.customer_demographics ,\\n testdata.tpcds_tiny.household_demographics ,\\n testdata.tpcds_tiny.income_band ,\\n testdata.tpcds_tiny.store_returns\\nWHERE ca_city = 'Edgewood'\\n AND c_current_addr_sk = ca_address_sk\\n AND ib_lower_bound >= 38128\\n AND ib_upper_bound <= 38128 + 50000\\n AND ib_income_band_sk = hd_income_band_sk\\n AND cd_demo_sk = c_current_cdemo_sk\\n AND hd_demo_sk = c_current_hdemo_sk\\n AND sr_cdemo_sk = cd_demo_sk\\nORDER BY c_customer_id NULLS FIRST\\nLIMIT 100;\",\n", " 'Q85': \"SELECT SUBSTRING(r_reason_desc,1,20) ,\\n avg(ws_quantity) avg1,\\n avg(wr_refunded_cash) avg2,\\n avg(wr_fee)\\nFROM testdata.tpcds_tiny.web_sales,\\n testdata.tpcds_tiny.web_returns,\\n testdata.tpcds_tiny.web_page,\\n testdata.tpcds_tiny.customer_demographics cd1,\\n testdata.tpcds_tiny.customer_demographics cd2,\\n testdata.tpcds_tiny.customer_address,\\n testdata.tpcds_tiny.date_dim,\\n testdata.tpcds_tiny.reason\\nWHERE ws_web_page_sk = wp_web_page_sk\\n AND ws_item_sk = wr_item_sk\\n AND ws_order_number = wr_order_number\\n AND ws_sold_date_sk = d_date_sk\\n AND d_year = 2000\\n AND cd1.cd_demo_sk = wr_refunded_cdemo_sk\\n AND cd2.cd_demo_sk = wr_returning_cdemo_sk\\n AND ca_address_sk = wr_refunded_addr_sk\\n AND r_reason_sk = wr_reason_sk\\n AND ( ( cd1.cd_marital_status = 'M'\\n AND cd1.cd_marital_status = cd2.cd_marital_status\\n AND cd1.cd_education_status = 'Advanced Degree'\\n AND cd1.cd_education_status = cd2.cd_education_status\\n AND ws_sales_price BETWEEN 100.00 AND 150.00 )\\n OR ( cd1.cd_marital_status = 'S'\\n AND cd1.cd_marital_status = cd2.cd_marital_status\\n AND cd1.cd_education_status = 'College'\\n AND cd1.cd_education_status = cd2.cd_education_status\\n AND ws_sales_price BETWEEN 50.00 AND 100.00 )\\n OR ( cd1.cd_marital_status = 'W'\\n AND cd1.cd_marital_status = cd2.cd_marital_status\\n AND cd1.cd_education_status = '2 yr Degree'\\n AND cd1.cd_education_status = cd2.cd_education_status\\n AND ws_sales_price BETWEEN 150.00 AND 200.00 ) )\\n AND ( ( ca_country = 'United States'\\n AND ca_state IN ('IN',\\n 'OH',\\n 'NJ')\\n AND ws_net_profit BETWEEN 100 AND 200)\\n OR ( ca_country = 'United States'\\n AND ca_state IN ('WI',\\n 'CT',\\n 'KY')\\n AND ws_net_profit BETWEEN 150 AND 300)\\n OR ( ca_country = 'United States'\\n AND ca_state IN ('LA',\\n 'IA',\\n 'AR')\\n AND ws_net_profit BETWEEN 50 AND 250) )\\nGROUP BY r_reason_desc\\nORDER BY SUBSTRING(r_reason_desc,1,20) ,\\n avg(ws_quantity) ,\\n avg(wr_refunded_cash) ,\\n avg(wr_fee)\\nLIMIT 100;\",\n", " 'Q86': 'SELECT sum(ws_net_paid) AS total_sum ,\\n i_category ,\\n i_class ,\\n grouping(i_category)+grouping(i_class) AS lochierarchy ,\\n rank() OVER ( PARTITION BY grouping(i_category)+grouping(i_class),\\n CASE\\n WHEN grouping(i_class) = 0 THEN i_category\\n END\\n ORDER BY sum(ws_net_paid) DESC) AS rank_within_parent\\nFROM testdata.tpcds_tiny.web_sales ,\\n testdata.tpcds_tiny.date_dim d1 ,\\n testdata.tpcds_tiny.item\\nWHERE d1.d_month_seq BETWEEN 1200 AND 1200+11\\n AND d1.d_date_sk = ws_sold_date_sk\\n AND i_item_sk = ws_item_sk\\nGROUP BY rollup(i_category,i_class)\\nORDER BY lochierarchy DESC NULLS FIRST,\\n CASE\\n WHEN grouping(i_category)+grouping(i_class) = 0 THEN i_category\\n END NULLS FIRST,\\n rank_within_parent NULLS FIRST\\nLIMIT 100;',\n", " 'Q87': 'SELECT count(*)\\nFROM ((SELECT DISTINCT c_last_name,\\n c_first_name,\\n d_date\\n FROM testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.date_dim,\\n testdata.tpcds_tiny.customer\\n WHERE testdata.tpcds_tiny.store_sales.ss_sold_date_sk = testdata.tpcds_tiny.date_dim.d_date_sk\\n AND testdata.tpcds_tiny.store_sales.ss_customer_sk = testdata.tpcds_tiny.customer.c_customer_sk\\n AND d_month_seq BETWEEN 1200 AND 1200+11)\\n EXCEPT\\n (SELECT DISTINCT c_last_name,\\n c_first_name,\\n d_date\\n FROM testdata.tpcds_tiny.catalog_sales,\\n testdata.tpcds_tiny.date_dim,\\n testdata.tpcds_tiny.customer\\n WHERE testdata.tpcds_tiny.catalog_sales.cs_sold_date_sk = testdata.tpcds_tiny.date_dim.d_date_sk\\n AND testdata.tpcds_tiny.catalog_sales.cs_bill_customer_sk = testdata.tpcds_tiny.customer.c_customer_sk\\n AND d_month_seq BETWEEN 1200 AND 1200+11)\\n EXCEPT\\n (SELECT DISTINCT c_last_name,\\n c_first_name,\\n d_date\\n FROM testdata.tpcds_tiny.web_sales,\\n testdata.tpcds_tiny.date_dim,\\n testdata.tpcds_tiny.customer\\n WHERE testdata.tpcds_tiny.web_sales.ws_sold_date_sk = testdata.tpcds_tiny.date_dim.d_date_sk\\n AND testdata.tpcds_tiny.web_sales.ws_bill_customer_sk = testdata.tpcds_tiny.customer.c_customer_sk\\n AND d_month_seq BETWEEN 1200 AND 1200+11)) cool_cust ;',\n", " 'Q88': \"SELECT *\\nFROM\\n (SELECT count(*) h8_30_to_9\\n FROM testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.household_demographics,\\n testdata.tpcds_tiny.time_dim,\\n testdata.tpcds_tiny.store\\n WHERE ss_sold_time_sk = testdata.tpcds_tiny.time_dim.t_time_sk\\n AND ss_hdemo_sk = testdata.tpcds_tiny.household_demographics.hd_demo_sk\\n AND ss_store_sk = s_store_sk\\n AND testdata.tpcds_tiny.time_dim.t_hour = 8\\n AND testdata.tpcds_tiny.time_dim.t_minute >= 30\\n AND ((testdata.tpcds_tiny.household_demographics.hd_dep_count = 4\\n AND testdata.tpcds_tiny.household_demographics.hd_vehicle_count<=4+2)\\n OR (testdata.tpcds_tiny.household_demographics.hd_dep_count = 2\\n AND testdata.tpcds_tiny.household_demographics.hd_vehicle_count<=2+2)\\n OR (testdata.tpcds_tiny.household_demographics.hd_dep_count = 0\\n AND testdata.tpcds_tiny.household_demographics.hd_vehicle_count<=0+2))\\n AND testdata.tpcds_tiny.store.s_store_name = 'ese') s1,\\n (SELECT count(*) h9_to_9_30\\n FROM testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.household_demographics,\\n testdata.tpcds_tiny.time_dim,\\n testdata.tpcds_tiny.store\\n WHERE ss_sold_time_sk = testdata.tpcds_tiny.time_dim.t_time_sk\\n AND ss_hdemo_sk = testdata.tpcds_tiny.household_demographics.hd_demo_sk\\n AND ss_store_sk = s_store_sk\\n AND testdata.tpcds_tiny.time_dim.t_hour = 9\\n AND testdata.tpcds_tiny.time_dim.t_minute < 30\\n AND ((testdata.tpcds_tiny.household_demographics.hd_dep_count = 4\\n AND testdata.tpcds_tiny.household_demographics.hd_vehicle_count<=4+2)\\n OR (testdata.tpcds_tiny.household_demographics.hd_dep_count = 2\\n AND testdata.tpcds_tiny.household_demographics.hd_vehicle_count<=2+2)\\n OR (testdata.tpcds_tiny.household_demographics.hd_dep_count = 0\\n AND testdata.tpcds_tiny.household_demographics.hd_vehicle_count<=0+2))\\n AND testdata.tpcds_tiny.store.s_store_name = 'ese') s2,\\n (SELECT count(*) h9_30_to_10\\n FROM testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.household_demographics,\\n testdata.tpcds_tiny.time_dim,\\n testdata.tpcds_tiny.store\\n WHERE ss_sold_time_sk = testdata.tpcds_tiny.time_dim.t_time_sk\\n AND ss_hdemo_sk = testdata.tpcds_tiny.household_demographics.hd_demo_sk\\n AND ss_store_sk = s_store_sk\\n AND testdata.tpcds_tiny.time_dim.t_hour = 9\\n AND testdata.tpcds_tiny.time_dim.t_minute >= 30\\n AND ((testdata.tpcds_tiny.household_demographics.hd_dep_count = 4\\n AND testdata.tpcds_tiny.household_demographics.hd_vehicle_count<=4+2)\\n OR (testdata.tpcds_tiny.household_demographics.hd_dep_count = 2\\n AND testdata.tpcds_tiny.household_demographics.hd_vehicle_count<=2+2)\\n OR (testdata.tpcds_tiny.household_demographics.hd_dep_count = 0\\n AND testdata.tpcds_tiny.household_demographics.hd_vehicle_count<=0+2))\\n AND testdata.tpcds_tiny.store.s_store_name = 'ese') s3,\\n (SELECT count(*) h10_to_10_30\\n FROM testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.household_demographics,\\n testdata.tpcds_tiny.time_dim,\\n testdata.tpcds_tiny.store\\n WHERE ss_sold_time_sk = testdata.tpcds_tiny.time_dim.t_time_sk\\n AND ss_hdemo_sk = testdata.tpcds_tiny.household_demographics.hd_demo_sk\\n AND ss_store_sk = s_store_sk\\n AND testdata.tpcds_tiny.time_dim.t_hour = 10\\n AND testdata.tpcds_tiny.time_dim.t_minute < 30\\n AND ((testdata.tpcds_tiny.household_demographics.hd_dep_count = 4\\n AND testdata.tpcds_tiny.household_demographics.hd_vehicle_count<=4+2)\\n OR (testdata.tpcds_tiny.household_demographics.hd_dep_count = 2\\n AND testdata.tpcds_tiny.household_demographics.hd_vehicle_count<=2+2)\\n OR (testdata.tpcds_tiny.household_demographics.hd_dep_count = 0\\n AND testdata.tpcds_tiny.household_demographics.hd_vehicle_count<=0+2))\\n AND testdata.tpcds_tiny.store.s_store_name = 'ese') s4,\\n (SELECT count(*) h10_30_to_11\\n FROM testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.household_demographics,\\n testdata.tpcds_tiny.time_dim,\\n testdata.tpcds_tiny.store\\n WHERE ss_sold_time_sk = testdata.tpcds_tiny.time_dim.t_time_sk\\n AND ss_hdemo_sk = testdata.tpcds_tiny.household_demographics.hd_demo_sk\\n AND ss_store_sk = s_store_sk\\n AND testdata.tpcds_tiny.time_dim.t_hour = 10\\n AND testdata.tpcds_tiny.time_dim.t_minute >= 30\\n AND ((testdata.tpcds_tiny.household_demographics.hd_dep_count = 4\\n AND testdata.tpcds_tiny.household_demographics.hd_vehicle_count<=4+2)\\n OR (testdata.tpcds_tiny.household_demographics.hd_dep_count = 2\\n AND testdata.tpcds_tiny.household_demographics.hd_vehicle_count<=2+2)\\n OR (testdata.tpcds_tiny.household_demographics.hd_dep_count = 0\\n AND testdata.tpcds_tiny.household_demographics.hd_vehicle_count<=0+2))\\n AND testdata.tpcds_tiny.store.s_store_name = 'ese') s5,\\n (SELECT count(*) h11_to_11_30\\n FROM testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.household_demographics,\\n testdata.tpcds_tiny.time_dim,\\n testdata.tpcds_tiny.store\\n WHERE ss_sold_time_sk = testdata.tpcds_tiny.time_dim.t_time_sk\\n AND ss_hdemo_sk = testdata.tpcds_tiny.household_demographics.hd_demo_sk\\n AND ss_store_sk = s_store_sk\\n AND testdata.tpcds_tiny.time_dim.t_hour = 11\\n AND testdata.tpcds_tiny.time_dim.t_minute < 30\\n AND ((testdata.tpcds_tiny.household_demographics.hd_dep_count = 4\\n AND testdata.tpcds_tiny.household_demographics.hd_vehicle_count<=4+2)\\n OR (testdata.tpcds_tiny.household_demographics.hd_dep_count = 2\\n AND testdata.tpcds_tiny.household_demographics.hd_vehicle_count<=2+2)\\n OR (testdata.tpcds_tiny.household_demographics.hd_dep_count = 0\\n AND testdata.tpcds_tiny.household_demographics.hd_vehicle_count<=0+2))\\n AND testdata.tpcds_tiny.store.s_store_name = 'ese') s6,\\n (SELECT count(*) h11_30_to_12\\n FROM testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.household_demographics,\\n testdata.tpcds_tiny.time_dim,\\n testdata.tpcds_tiny.store\\n WHERE ss_sold_time_sk = testdata.tpcds_tiny.time_dim.t_time_sk\\n AND ss_hdemo_sk = testdata.tpcds_tiny.household_demographics.hd_demo_sk\\n AND ss_store_sk = s_store_sk\\n AND testdata.tpcds_tiny.time_dim.t_hour = 11\\n AND testdata.tpcds_tiny.time_dim.t_minute >= 30\\n AND ((testdata.tpcds_tiny.household_demographics.hd_dep_count = 4\\n AND testdata.tpcds_tiny.household_demographics.hd_vehicle_count<=4+2)\\n OR (testdata.tpcds_tiny.household_demographics.hd_dep_count = 2\\n AND testdata.tpcds_tiny.household_demographics.hd_vehicle_count<=2+2)\\n OR (testdata.tpcds_tiny.household_demographics.hd_dep_count = 0\\n AND testdata.tpcds_tiny.household_demographics.hd_vehicle_count<=0+2))\\n AND testdata.tpcds_tiny.store.s_store_name = 'ese') s7,\\n (SELECT count(*) h12_to_12_30\\n FROM testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.household_demographics,\\n testdata.tpcds_tiny.time_dim,\\n testdata.tpcds_tiny.store\\n WHERE ss_sold_time_sk = testdata.tpcds_tiny.time_dim.t_time_sk\\n AND ss_hdemo_sk = testdata.tpcds_tiny.household_demographics.hd_demo_sk\\n AND ss_store_sk = s_store_sk\\n AND testdata.tpcds_tiny.time_dim.t_hour = 12\\n AND testdata.tpcds_tiny.time_dim.t_minute < 30\\n AND ((testdata.tpcds_tiny.household_demographics.hd_dep_count = 4\\n AND testdata.tpcds_tiny.household_demographics.hd_vehicle_count<=4+2)\\n OR (testdata.tpcds_tiny.household_demographics.hd_dep_count = 2\\n AND testdata.tpcds_tiny.household_demographics.hd_vehicle_count<=2+2)\\n OR (testdata.tpcds_tiny.household_demographics.hd_dep_count = 0\\n AND testdata.tpcds_tiny.household_demographics.hd_vehicle_count<=0+2))\\n AND testdata.tpcds_tiny.store.s_store_name = 'ese') s8 ;\",\n", " 'Q89': \"SELECT * from\\n (SELECT i_category, i_class, i_brand, s_store_name, s_company_name, d_moy, sum(ss_sales_price) sum_sales, avg(sum(ss_sales_price)) OVER (PARTITION BY i_category, i_brand, s_store_name, s_company_name) avg_monthly_sales\\n FROM testdata.tpcds_tiny.item, testdata.tpcds_tiny.store_sales, testdata.tpcds_tiny.date_dim, testdata.tpcds_tiny.store\\n WHERE ss_item_sk = i_item_sk\\n AND ss_sold_date_sk = d_date_sk\\n AND ss_store_sk = s_store_sk\\n AND d_year = 1999\\n AND ((i_category IN ('Books','Electronics','Sports')\\n AND i_class IN ('computers','stereo','football') )\\n OR (i_category IN ('Men','Jewelry','Women')\\n AND i_class IN ('shirts','birdal','dresses')))\\n GROUP BY i_category, i_class, i_brand, s_store_name, s_company_name, d_moy) tmp1\\nWHERE CASE\\n WHEN (avg_monthly_sales <> 0) THEN (abs(sum_sales - avg_monthly_sales) / avg_monthly_sales)\\n ELSE NULL\\n END > 0.1\\nORDER BY sum_sales - avg_monthly_sales,\\n s_store_name, 1, 2, 3, 5, 6, 7, 8\\nLIMIT 100;\",\n", " 'Q90': 'SELECT case when pmc=0 then null else cast(amc AS decimal(15,4))/cast(pmc AS decimal(15,4)) end am_pm_ratio\\nFROM\\n (SELECT count(*) amc\\n FROM testdata.tpcds_tiny.web_sales,\\n testdata.tpcds_tiny.household_demographics,\\n testdata.tpcds_tiny.time_dim,\\n testdata.tpcds_tiny.web_page\\n WHERE ws_sold_time_sk = testdata.tpcds_tiny.time_dim.t_time_sk\\n AND ws_ship_hdemo_sk = testdata.tpcds_tiny.household_demographics.hd_demo_sk\\n AND ws_web_page_sk = testdata.tpcds_tiny.web_page.wp_web_page_sk\\n AND testdata.tpcds_tiny.time_dim.t_hour BETWEEN 8 AND 8+1\\n AND testdata.tpcds_tiny.household_demographics.hd_dep_count = 6\\n AND testdata.tpcds_tiny.web_page.wp_char_count BETWEEN 5000 AND 5200) at_,\\n (SELECT count(*) pmc\\n FROM testdata.tpcds_tiny.web_sales,\\n testdata.tpcds_tiny.household_demographics,\\n testdata.tpcds_tiny.time_dim,\\n testdata.tpcds_tiny.web_page\\n WHERE ws_sold_time_sk = testdata.tpcds_tiny.time_dim.t_time_sk\\n AND ws_ship_hdemo_sk = testdata.tpcds_tiny.household_demographics.hd_demo_sk\\n AND ws_web_page_sk = testdata.tpcds_tiny.web_page.wp_web_page_sk\\n AND testdata.tpcds_tiny.time_dim.t_hour BETWEEN 19 AND 19+1\\n AND testdata.tpcds_tiny.household_demographics.hd_dep_count = 6\\n AND testdata.tpcds_tiny.web_page.wp_char_count BETWEEN 5000 AND 5200) pt\\nORDER BY am_pm_ratio\\nLIMIT 100;',\n", " 'Q91': \"SELECT cc_call_center_id Call_Center,\\n cc_name Call_Center_Name,\\n cc_manager Manager,\\n sum(cr_net_loss) Returns_Loss\\nFROM testdata.tpcds_tiny.call_center,\\n testdata.tpcds_tiny.catalog_returns,\\n testdata.tpcds_tiny.date_dim,\\n testdata.tpcds_tiny.customer,\\n testdata.tpcds_tiny.customer_address,\\n testdata.tpcds_tiny.customer_demographics,\\n testdata.tpcds_tiny.household_demographics\\nWHERE cr_call_center_sk = cc_call_center_sk\\n AND cr_returned_date_sk = d_date_sk\\n AND cr_returning_customer_sk= c_customer_sk\\n AND cd_demo_sk = c_current_cdemo_sk\\n AND hd_demo_sk = c_current_hdemo_sk\\n AND ca_address_sk = c_current_addr_sk\\n AND d_year = 1998\\n AND d_moy = 11\\n AND ((cd_marital_status = 'M'\\n AND cd_education_status = 'Unknown') or(cd_marital_status = 'W'\\n AND cd_education_status = 'Advanced Degree'))\\n AND hd_buy_potential LIKE 'Unknown%'\\n AND ca_gmt_offset = -7\\nGROUP BY cc_call_center_id,\\n cc_name,\\n cc_manager,\\n cd_marital_status,\\n cd_education_status\\nORDER BY sum(cr_net_loss) DESC;\",\n", " 'Q92': 'SELECT sum(ws_ext_discount_amt) AS \"Excess Discount Amount\"\\nFROM testdata.tpcds_tiny.web_sales,\\n testdata.tpcds_tiny.item,\\n testdata.tpcds_tiny.date_dim\\nWHERE i_manufact_id = 350\\n AND i_item_sk = ws_item_sk\\n AND d_date BETWEEN cast(\\'2000-01-27\\' AS date) AND cast(\\'2000-04-26\\' AS date)\\n AND d_date_sk = ws_sold_date_sk\\n AND ws_ext_discount_amt >\\n (SELECT 1.3 * avg(ws_ext_discount_amt)\\n FROM testdata.tpcds_tiny.web_sales,\\n testdata.tpcds_tiny.date_dim\\n WHERE ws_item_sk = i_item_sk\\n AND d_date BETWEEN cast(\\'2000-01-27\\' AS date) AND cast(\\'2000-04-26\\' AS date)\\n AND d_date_sk = ws_sold_date_sk )\\nORDER BY sum(ws_ext_discount_amt)\\nLIMIT 100;',\n", " 'Q93': \"SELECT ss_customer_sk,\\n sum(act_sales) sumsales\\nFROM\\n (SELECT ss_item_sk,\\n ss_ticket_number,\\n ss_customer_sk,\\n CASE\\n WHEN sr_return_quantity IS NOT NULL THEN (ss_quantity-sr_return_quantity)*ss_sales_price\\n ELSE (ss_quantity*ss_sales_price)\\n END act_sales\\n FROM testdata.tpcds_tiny.store_sales\\n LEFT OUTER JOIN testdata.tpcds_tiny.store_returns ON (sr_item_sk = ss_item_sk\\n AND sr_ticket_number = ss_ticket_number) ,testdata.tpcds_tiny.reason\\n WHERE sr_reason_sk = r_reason_sk\\n AND r_reason_desc = 'reason 28') t\\nGROUP BY ss_customer_sk\\nORDER BY sumsales NULLS FIRST,\\n ss_customer_sk NULLS FIRST\\nLIMIT 100;\",\n", " 'Q94': 'SELECT count(DISTINCT ws_order_number) AS \"order count\" ,\\n sum(ws_ext_ship_cost) AS \"total shipping cost\" ,\\n sum(ws_net_profit) AS \"total net profit\"\\nFROM testdata.tpcds_tiny.web_sales ws1 ,\\n testdata.tpcds_tiny.date_dim ,\\n testdata.tpcds_tiny.customer_address ,\\n testdata.tpcds_tiny.web_site\\nWHERE d_date BETWEEN cast(\\'1999-02-01\\' AS date) AND cast(\\'1999-04-02\\' AS date)\\n AND ws1.ws_ship_date_sk = d_date_sk\\n AND ws1.ws_ship_addr_sk = ca_address_sk\\n AND ca_state = \\'IL\\'\\n AND ws1.ws_web_site_sk = web_site_sk\\n AND web_company_name = \\'pri\\'\\n AND EXISTS\\n (SELECT *\\n FROM testdata.tpcds_tiny.web_sales ws2\\n WHERE ws1.ws_order_number = ws2.ws_order_number\\n AND ws1.ws_warehouse_sk <> ws2.ws_warehouse_sk)\\n AND NOT exists\\n (SELECT *\\n FROM testdata.tpcds_tiny.web_returns wr1\\n WHERE ws1.ws_order_number = wr1.wr_order_number)\\nORDER BY count(DISTINCT ws_order_number)\\nLIMIT 100;',\n", " 'Q95': 'WITH ws_wh AS\\n (SELECT ws1.ws_order_number,\\n ws1.ws_warehouse_sk wh1,\\n ws2.ws_warehouse_sk wh2\\n FROM testdata.tpcds_tiny.web_sales ws1,\\n testdata.tpcds_tiny.web_sales ws2\\n WHERE ws1.ws_order_number = ws2.ws_order_number\\n AND ws1.ws_warehouse_sk <> ws2.ws_warehouse_sk)\\nSELECT count(DISTINCT ws_order_number) AS \"order count\" ,\\n sum(ws_ext_ship_cost) AS \"total shipping cost\" ,\\n sum(ws_net_profit) AS \"total net profit\"\\nFROM testdata.tpcds_tiny.web_sales ws1 ,\\n testdata.tpcds_tiny.date_dim ,\\n testdata.tpcds_tiny.customer_address ,\\n testdata.tpcds_tiny.web_site\\nWHERE d_date BETWEEN cast(\\'1999-02-01\\' AS date) AND cast(\\'1999-04-02\\' AS date)\\n AND ws1.ws_ship_date_sk = d_date_sk\\n AND ws1.ws_ship_addr_sk = ca_address_sk\\n AND ca_state = \\'IL\\'\\n AND ws1.ws_web_site_sk = web_site_sk\\n AND web_company_name = \\'pri\\'\\n AND ws1.ws_order_number IN\\n (SELECT ws_order_number\\n FROM ws_wh)\\n AND ws1.ws_order_number IN\\n (SELECT wr_order_number\\n FROM testdata.tpcds_tiny.web_returns,\\n ws_wh\\n WHERE wr_order_number = ws_wh.ws_order_number)\\nORDER BY count(DISTINCT ws_order_number)\\nLIMIT 100;',\n", " 'Q96': \"SELECT count(*)\\nFROM testdata.tpcds_tiny.store_sales ,\\n testdata.tpcds_tiny.household_demographics,\\n testdata.tpcds_tiny.time_dim,\\n testdata.tpcds_tiny.store\\nWHERE ss_sold_time_sk = testdata.tpcds_tiny.time_dim.t_time_sk\\n AND ss_hdemo_sk = testdata.tpcds_tiny.household_demographics.hd_demo_sk\\n AND ss_store_sk = s_store_sk\\n AND testdata.tpcds_tiny.time_dim.t_hour = 20\\n AND testdata.tpcds_tiny.time_dim.t_minute >= 30\\n AND testdata.tpcds_tiny.household_demographics.hd_dep_count = 7\\n AND testdata.tpcds_tiny.store.s_store_name = 'ese'\\nORDER BY count(*)\\nLIMIT 100;\",\n", " 'Q97': 'WITH ssci AS\\n (SELECT ss_customer_sk customer_sk ,\\n ss_item_sk item_sk\\n FROM testdata.tpcds_tiny.store_sales,\\n testdata.tpcds_tiny.date_dim\\n WHERE ss_sold_date_sk = d_date_sk\\n AND d_month_seq BETWEEN 1200 AND 1200 + 11\\n GROUP BY ss_customer_sk ,\\n ss_item_sk),\\n csci as\\n ( SELECT cs_bill_customer_sk customer_sk ,cs_item_sk item_sk\\n FROM testdata.tpcds_tiny.catalog_sales,testdata.tpcds_tiny.date_dim\\n WHERE cs_sold_date_sk = d_date_sk\\n AND d_month_seq BETWEEN 1200 AND 1200 + 11\\n GROUP BY cs_bill_customer_sk ,cs_item_sk)\\nSELECT sum(CASE\\n WHEN ssci.customer_sk IS NOT NULL\\n AND csci.customer_sk IS NULL THEN 1\\n ELSE 0\\n END) store_only ,\\n sum(CASE\\n WHEN ssci.customer_sk IS NULL\\n AND csci.customer_sk IS NOT NULL THEN 1\\n ELSE 0\\n END) catalog_only ,\\n sum(CASE\\n WHEN ssci.customer_sk IS NOT NULL\\n AND csci.customer_sk IS NOT NULL THEN 1\\n ELSE 0\\n END) store_and_catalog\\nFROM ssci\\nFULL OUTER JOIN csci ON (ssci.customer_sk=csci.customer_sk\\n AND ssci.item_sk = csci.item_sk)\\nLIMIT 100;',\n", " 'Q98': \"SELECT i_item_id ,\\n i_item_desc,\\n i_category,\\n i_class,\\n i_current_price ,\\n sum(ss_ext_sales_price) AS itemrevenue,\\n sum(ss_ext_sales_price)*100.0000/sum(sum(ss_ext_sales_price)) OVER (PARTITION BY i_class) AS revenueratio\\nFROM testdata.tpcds_tiny.store_sales ,\\n testdata.tpcds_tiny.item,\\n testdata.tpcds_tiny.date_dim\\nWHERE ss_item_sk = i_item_sk\\n AND i_category IN ('Sports',\\n 'Books',\\n 'Home')\\n AND ss_sold_date_sk = d_date_sk\\n AND d_date BETWEEN cast('1999-02-22' AS date) AND cast('1999-03-24' AS date)\\nGROUP BY i_item_id ,\\n i_item_desc,\\n i_category ,\\n i_class ,\\n i_current_price\\nORDER BY i_category NULLS FIRST,\\n i_class NULLS FIRST,\\n i_item_id NULLS FIRST,\\n i_item_desc NULLS FIRST,\\n revenueratio NULLS FIRST;\",\n", " 'Q99': 'SELECT w_substr ,\\n sm_type ,\\n LOWER(cc_name) cc_name_lower ,\\n sum(CASE\\n WHEN (cs_ship_date_sk - cs_sold_date_sk <= 30) THEN 1\\n ELSE 0\\n END) AS \"30 days\",\\n sum(CASE\\n WHEN (cs_ship_date_sk - cs_sold_date_sk > 30)\\n AND (cs_ship_date_sk - cs_sold_date_sk <= 60) THEN 1\\n ELSE 0\\n END) AS \"31-60 days\",\\n sum(CASE\\n WHEN (cs_ship_date_sk - cs_sold_date_sk > 60)\\n AND (cs_ship_date_sk - cs_sold_date_sk <= 90) THEN 1\\n ELSE 0\\n END) AS \"61-90 days\",\\n sum(CASE\\n WHEN (cs_ship_date_sk - cs_sold_date_sk > 90)\\n AND (cs_ship_date_sk - cs_sold_date_sk <= 120) THEN 1\\n ELSE 0\\n END) AS \"91-120 days\",\\n sum(CASE\\n WHEN (cs_ship_date_sk - cs_sold_date_sk > 120) THEN 1\\n ELSE 0\\n END) AS \">120 days\"\\nFROM testdata.tpcds_tiny.catalog_sales ,\\n (SELECT testdata.tpcds_tiny.warehouse.*, SUBSTRING(w_warehouse_name,1,20) w_substr\\n FROM testdata.tpcds_tiny.warehouse) AS sq1 ,\\n testdata.tpcds_tiny.ship_mode ,\\n testdata.tpcds_tiny.call_center ,\\n testdata.tpcds_tiny.date_dim\\nWHERE d_month_seq BETWEEN 1200 AND 1200 + 11\\n AND cs_ship_date_sk = d_date_sk\\n AND cs_warehouse_sk = w_warehouse_sk\\n AND cs_ship_mode_sk = sm_ship_mode_sk\\n AND cs_call_center_sk = cc_call_center_sk\\nGROUP BY w_substr ,\\n sm_type ,\\n cc_name\\nORDER BY w_substr NULLS FIRST,\\n sm_type NULLS FIRST,\\n cc_name_lower NULLS FIRST\\nLIMIT 100;',\n", "}\n", "\n", "import json\n", "\n", "with open(\"queries.json\", \"w\") as fh:\n", " json.dump(QUERIES, fh)\n", "\n", "print(f\"{len(QUERIES)} queries saved: {list(QUERIES)[0]} .. {list(QUERIES)[-1]}\")" ] }, { "cell_type": "markdown", "id": "60a0012e", "metadata": {}, "source": [ "## 3. Benchmark runner\n", "\n", "The benchmark is a script, and each engine runs it in **its own fresh process**, the way the [Opteryx performance suites](https://docs.opteryx.app/docs/reference/internals/benchmarking#method) do. That means neither engine inherits anything from the data generation done in this notebook.\n", "\n", "Inside the script:\n", "\n", "- **Warm-up first.** The first query in a fresh process pays one-off costs: loading native libraries, starting thread pools, and the first read of each file from disk. On a new runtime that can take seconds. Each engine runs a `COUNT(*)` over `store_sales` first and reports it separately as the cold start, so the cost isn't charged to whichever query happens to run first.\n", "- **DuckDB** gets one in-memory connection with a view over each table's Parquet files, reused across all queries, which is normal embedded-engine usage.\n", "- **Opteryx** opens a fresh session per iteration and drains the result as morsels without rendering it. It leaves its worker pools at their defaults, which it sizes to the machine. Pinning them higher than the runtime's core count makes it slower, not faster.\n", "- Each query runs `ITERATIONS` warm iterations with a garbage collection before each, and the **minimum** time is reported.\n", "- A query that raises an error is recorded with that error, and the script moves on to the next query.\n", "\n", "Each finished query is appended to a results file as one JSON line, and the script skips queries already in that file. That is what makes the time limit work. Some TPC-DS queries can run far longer than the rest, and a query stuck in native code cannot be interrupted from inside its own process. So step 4 watches the script, and if a query has not finished within `QUERY_TIMEOUT_S` per iteration, it kills the process, records that query as timed out, and starts a fresh process that carries on with the next query. The Opteryx TPC-DS runner handles timeouts the same way. The fresh process repeats the warm-up, so its cold start is not charged to the next query either.\n", "\n", "Row counts are recorded from both engines as a correctness check. Both engines query the same data, so they should return the same number of rows for every query both complete." ] }, { "cell_type": "code", "execution_count": null, "id": "9c07366c", "metadata": {}, "outputs": [], "source": [ "%%writefile bench_tpcds.py\n", "\"\"\"Run the TPC-DS queries in queries.json on one engine, appending one JSON line per query.\n", "\n", "Usage: python bench_tpcds.py {duckdb|opteryx} \n", "\n", "Queries already in the results file are skipped, so a run that was stopped carries\n", "on with the next query.\n", "\"\"\"\n", "import gc\n", "import json\n", "import os\n", "import statistics\n", "import sys\n", "import time\n", "\n", "engine, iterations, out_path = sys.argv[1], int(sys.argv[2]), sys.argv[3]\n", "with open(\"queries.json\") as fh:\n", " QUERIES = json.load(fh)\n", "\n", "done = set()\n", "if os.path.exists(out_path):\n", " with open(out_path) as fh:\n", " done = {json.loads(line)[\"query\"] for line in fh if line.strip()}\n", "\n", "if engine == \"duckdb\":\n", " import duckdb\n", "\n", " con = duckdb.connect()\n", " # One view per table over the same Parquet files Opteryx reads.\n", " for t in sorted(os.listdir(\"data/tpcds\")):\n", " con.execute(f\"CREATE VIEW {t} AS SELECT * FROM read_parquet('data/tpcds/{t}/*.parquet')\")\n", " prefix = \"\"\n", "\n", " def run(sql):\n", " t0 = time.perf_counter()\n", " rows = len(con.execute(sql).fetchall())\n", " return (time.perf_counter() - t0) * 1000, rows\n", "\n", "elif engine == \"opteryx\":\n", " import opteryx\n", " from opteryx.connectors import DiskConnector\n", "\n", " # `data.tpcds.
` resolves to ./data/tpcds/
/, the Parquet files from step 1.\n", " opteryx.register_workspace(\"data\", DiskConnector)\n", " prefix = \"data.tpcds.\"\n", "\n", " def run(sql):\n", " session = opteryx.session()\n", " try:\n", " rows = 0\n", " t0 = time.perf_counter()\n", " for morsel in session.execute_to_morsels(sql):\n", " if morsel is not None and hasattr(morsel, \"num_rows\"):\n", " rows += morsel.num_rows\n", " return (time.perf_counter() - t0) * 1000, rows\n", " finally:\n", " session.close()\n", "\n", "else:\n", " raise SystemExit(f\"unknown engine: {engine}\")\n", "\n", "t0 = time.perf_counter()\n", "run(f\"SELECT COUNT(*) FROM {prefix}store_sales\")\n", "print(f\"{engine} cold start: {(time.perf_counter() - t0) * 1000:,.1f}ms\", flush=True)\n", "\n", "suite_start = time.perf_counter()\n", "for name, raw_sql in QUERIES.items():\n", " if name in done:\n", " continue\n", " sql = raw_sql.replace(\"testdata.tpcds_tiny.\", prefix)\n", " times = []\n", " try:\n", " for _ in range(iterations):\n", " gc.collect()\n", " elapsed_ms, rows = run(sql)\n", " times.append(elapsed_ms)\n", " record = {\"query\": name, \"status\": \"ok\", \"ms\": min(times),\n", " \"median_ms\": statistics.median(times), \"rows\": rows, \"error\": None}\n", " message = f\"{min(times):>10,.1f}ms\"\n", " except Exception as exc:\n", " record = {\"query\": name, \"status\": \"error\", \"ms\": None, \"median_ms\": None,\n", " \"rows\": None, \"error\": f\"{type(exc).__name__}: {exc}\"}\n", " message = f\"FAILED {record['error'][:120]}\"\n", " # Write the result before reporting it: the notebook reads this file to find\n", " # which query was in flight if it has to stop the process.\n", " with open(out_path, \"a\") as fh:\n", " fh.write(json.dumps(record) + \"\\n\")\n", " print(f\" {name} {message}\", flush=True)\n", "\n", "print(f\"{engine} done in {time.perf_counter() - suite_start:.1f}s\", flush=True)" ] }, { "cell_type": "markdown", "id": "d7c645fd", "metadata": {}, "source": [ "## 4. Run the benchmark\n", "\n", "This cell takes the longest. It prints a line as each query finishes, DuckDB's pass first and then Opteryx's. A query that times out or takes its process down is reported as it happens, followed by the cold start of the fresh process that replaces it." ] }, { "cell_type": "code", "execution_count": null, "id": "47939eab", "metadata": {}, "outputs": [], "source": [ "import queue\n", "import subprocess\n", "import sys\n", "import threading\n", "\n", "STARTUP_TIMEOUT_S = 300 # interpreter start-up plus the warm-up query\n", "\n", "\n", "def read_results(path):\n", " if not os.path.exists(path):\n", " return {}\n", " with open(path) as fh:\n", " return {r[\"query\"]: r for r in (json.loads(line) for line in fh if line.strip())}\n", "\n", "\n", "def run_engine(engine):\n", " \"\"\"Run bench_tpcds.py for one engine until every query has a result.\n", "\n", " A process that stops reporting for QUERY_TIMEOUT_S per iteration is killed, the query\n", " it was running is recorded as timed out, and a fresh process carries on from there.\n", " \"\"\"\n", " out_path = f\"results_{engine}.jsonl\"\n", " if os.path.exists(out_path):\n", " os.remove(out_path)\n", " starts = 0\n", " while len(read_results(out_path)) < len(QUERIES):\n", " starts += 1\n", " proc = subprocess.Popen(\n", " [sys.executable, \"bench_tpcds.py\", engine, str(ITERATIONS), out_path],\n", " stdout=subprocess.PIPE,\n", " stderr=subprocess.STDOUT,\n", " text=True,\n", " )\n", " # Read output on a thread so the wait for each line can have a deadline.\n", " lines = queue.Queue()\n", " def pump(out=proc.stdout):\n", " for line in out:\n", " lines.put(line)\n", " lines.put(None)\n", "\n", " threading.Thread(target=pump, daemon=True).start()\n", "\n", " warmed_up, outcome = False, None\n", " while True:\n", " try:\n", " line = lines.get(timeout=QUERY_TIMEOUT_S * ITERATIONS if warmed_up else STARTUP_TIMEOUT_S)\n", " except queue.Empty:\n", " proc.kill()\n", " proc.wait()\n", " outcome = (\"timeout\", f\"exceeded {QUERY_TIMEOUT_S}s per iteration\")\n", " break\n", " if line is None:\n", " code = proc.wait()\n", " if code != 0:\n", " outcome = (\"error\", f\"process exited with code {code}\")\n", " break\n", " print(line, end=\"\")\n", " warmed_up = warmed_up or \"cold start\" in line\n", "\n", " if outcome is None:\n", " if len(read_results(out_path)) < len(QUERIES):\n", " raise RuntimeError(f\"{engine} benchmark exited before running every query\")\n", " break\n", " if not warmed_up:\n", " raise RuntimeError(f\"{engine} benchmark did not start: {outcome[1]}\")\n", " # The query in flight is the first one without a result.\n", " done = read_results(out_path)\n", " query = next(q for q in QUERIES if q not in done)\n", " status, error = outcome\n", " with open(out_path, \"a\") as fh:\n", " fh.write(json.dumps({\"query\": query, \"status\": status, \"ms\": None, \"median_ms\": None,\n", " \"rows\": None, \"error\": error}) + \"\\n\")\n", " print(f\" {query} {status.upper()} {error}; starting a fresh {engine} process\")\n", " return read_results(out_path), starts\n", "\n", "\n", "by_engine = {}\n", "for engine in (\"duckdb\", \"opteryx\"):\n", " by_engine[engine], starts = run_engine(engine)\n", " print(f\"{engine}: {starts} process start(s)\\n\")\n", "\n", "results = [] # rows of dicts, one per query\n", "for name in QUERIES:\n", " row = {\"query\": name}\n", " for engine, engine_results in by_engine.items():\n", " for key, value in engine_results[name].items():\n", " if key != \"query\":\n", " row[f\"{engine}_{key}\"] = value\n", " results.append(row)" ] }, { "cell_type": "markdown", "id": "3028e78b", "metadata": {}, "source": [ "## 5. Coverage\n", "\n", "This is the headline result for TPC-DS. A query counts as covered when the engine planned it and ran it to completion inside the time limit. A query that fails to parse or plan is a gap in SQL coverage, not a performance result." ] }, { "cell_type": "code", "execution_count": null, "id": "6495507a", "metadata": {}, "outputs": [], "source": [ "import pandas as pd\n", "\n", "df = pd.DataFrame(results).set_index(\"query\")\n", "ENGINES = {\"duckdb\": \"DuckDB\", \"opteryx\": \"Opteryx\"}\n", "\n", "coverage = pd.DataFrame({\n", " label: {\n", " \"completed\": int((df[f\"{engine}_status\"] == \"ok\").sum()),\n", " \"failed\": int((df[f\"{engine}_status\"] == \"error\").sum()),\n", " \"timed out\": int((df[f\"{engine}_status\"] == \"timeout\").sum()),\n", " }\n", " for engine, label in ENGINES.items()\n", "}).T\n", "coverage[\"of\"] = len(df)\n", "display(coverage)\n", "\n", "for engine, label in ENGINES.items():\n", " print(f\"{label:<8} planned and ran {coverage.loc[label, 'completed']} of {len(df)} queries\")\n", "\n", "# Failures, grouped by error type so one gap that breaks several queries reads as one line.\n", "failures = []\n", "for engine, label in ENGINES.items():\n", " failed = df[df[f\"{engine}_status\"] != \"ok\"]\n", " for query, r in failed.iterrows():\n", " message = r[f\"{engine}_error\"]\n", " failures.append({\n", " \"engine\": label,\n", " \"status\": r[f\"{engine}_status\"],\n", " \"error type\": message.split(\":\", 1)[0],\n", " \"query\": query,\n", " \"error\": message[:300],\n", " })\n", "failures = pd.DataFrame(failures, columns=[\"engine\", \"status\", \"error type\", \"query\", \"error\"])\n", "\n", "if failures.empty:\n", " print(\"\\nNo failures on either engine.\")\n", "else:\n", " print(\"\\nFailures by error type:\")\n", " display(\n", " failures.groupby([\"engine\", \"error type\"])[\"query\"]\n", " .agg(count=\"count\", queries=lambda q: \", \".join(q))\n", " .sort_values(\"count\", ascending=False)\n", " )\n", " print(\"\\nEvery failure, with its error message:\")\n", " with pd.option_context(\"display.max_colwidth\", 300, \"display.max_rows\", 200):\n", " display(failures.set_index([\"engine\", \"query\"]))" ] }, { "cell_type": "markdown", "id": "7e7ff759", "metadata": {}, "source": [ "## 6. Results on the queries both engines completed\n", "\n", "Timings are compared only where both engines ran the query, and only query by query. There is deliberately no overall speed-up figure. As the [Benchmarking](https://docs.opteryx.app/docs/reference/internals/benchmarking#tpc-ds) page explains, a ratio taken over whichever subset of queries both engines happen to support changes meaning as coverage changes, so two such figures would not be measuring the same thing. The `opteryx / duckdb` column is per query: below 1 means Opteryx was faster on that query, above 1 means DuckDB was." ] }, { "cell_type": "code", "execution_count": null, "id": "ef8ef4ed", "metadata": {}, "outputs": [], "source": [ "both = df[(df[\"duckdb_status\"] == \"ok\") & (df[\"opteryx_status\"] == \"ok\")].copy()\n", "print(f\"{len(both)} of {len(df)} queries completed on both engines.\")\n", "\n", "# Row-count cross-check: the engines should agree, query by query.\n", "row_cols = [\"duckdb_rows\", \"opteryx_rows\"]\n", "mismatches = both[\"duckdb_rows\"] != both[\"opteryx_rows\"]\n", "if mismatches.any():\n", " print(\"ROW COUNT MISMATCH on:\", list(both.index[mismatches]))\n", " display(both.loc[mismatches, row_cols])\n", "else:\n", " print(\"Row counts agree across both engines on every query both completed.\")\n", "\n", "timing_cols = [\"duckdb_ms\", \"opteryx_ms\"]\n", "both[row_cols] = both[row_cols].astype(int)\n", "both[\"opteryx / duckdb\"] = both[\"opteryx_ms\"] / both[\"duckdb_ms\"]\n", "with pd.option_context(\"display.max_rows\", 200):\n", " display(both[timing_cols + [\"opteryx / duckdb\"] + row_cols].round(2))\n", "\n", "faster = (both[\"opteryx_ms\"] < both[\"duckdb_ms\"]).sum()\n", "print(f\"Opteryx faster on {faster} of {len(both)} shared queries; DuckDB faster on {len(both) - faster}.\")" ] }, { "cell_type": "markdown", "id": "ad3b6b68", "metadata": {}, "source": [ "## 7. Charts" ] }, { "cell_type": "code", "execution_count": null, "id": "218c83fe", "metadata": {}, "outputs": [], "source": [ "import matplotlib.pyplot as plt\n", "import numpy as np\n", "\n", "colors = {\"duckdb\": \"#f0b429\", \"opteryx\": \"#20bf6b\"}\n", "labels = {\"duckdb\": \"DuckDB\", \"opteryx\": \"Opteryx\"}\n", "\n", "# Coverage: completed / failed / timed out, out of 99. Completed is in each engine's colour.\n", "fig, ax = plt.subplots(figsize=(9, 3))\n", "left = np.zeros(len(coverage))\n", "segments = ((\"completed\", [colors[e] for e in ENGINES]), (\"failed\", \"#d64545\"), (\"timed out\", \"#9aa5b1\"))\n", "for status, color in segments:\n", " values = coverage[status].values\n", " ax.barh(coverage.index, values, left=left, color=color, label=None if status == \"completed\" else status)\n", " left += values\n", "ax.set_yticks(range(len(coverage)))\n", "ax.set_yticklabels([\n", " f\"{label}\\n{r['completed']} ok, {r['failed']} failed, {r['timed out']} timed out\"\n", " for label, r in coverage.iterrows()\n", "])\n", "ax.set_xlim(0, len(df))\n", "ax.set_xlabel(f\"queries (of {len(df)})\")\n", "ax.set_title(f\"TPC-DS SF{SCALE_FACTOR}: coverage\")\n", "ax.invert_yaxis()\n", "ax.legend(loc=\"upper center\", bbox_to_anchor=(0.5, -0.3), ncol=2, fontsize=\"small\", frameon=False)\n", "fig.tight_layout()\n", "plt.show()\n", "\n", "if len(both):\n", " # Per-query min time on the queries both engines completed.\n", " fig, ax = plt.subplots(figsize=(max(14, len(both) * 0.22), 6))\n", " x = np.arange(len(both))\n", " width = 0.4\n", " for i, engine in enumerate(ENGINES):\n", " offset = (i - 0.5) * width\n", " ax.bar(x + offset, both[f\"{engine}_ms\"], width, label=labels[engine], color=colors[engine])\n", " ax.set_yscale(\"log\")\n", " ax.set_xticks(x)\n", " ax.set_xticklabels(both.index, rotation=90, fontsize=\"small\")\n", " ax.set_xlim(-0.6, len(both) - 0.4)\n", " ax.set_ylabel(\"min time (ms, log scale)\")\n", " ax.set_title(f\"TPC-DS SF{SCALE_FACTOR}: per-query min time across {ITERATIONS} warm runs, \"\n", " f\"{len(both)} queries both engines completed\")\n", " ax.legend()\n", " ax.grid(axis=\"y\", alpha=0.3, which=\"both\")\n", " fig.tight_layout()\n", " plt.show()\n", "\n", " # Per-query ratio, sorted: the spread, not an average.\n", " ratio = both[\"opteryx / duckdb\"].sort_values()\n", " fig, ax = plt.subplots(figsize=(max(14, len(ratio) * 0.22), 5))\n", " bar_colors = [colors[\"opteryx\"] if r < 1 else colors[\"duckdb\"] for r in ratio]\n", " ax.bar(np.arange(len(ratio)), ratio, color=bar_colors)\n", " ax.axhline(1, color=\"black\", linewidth=0.8)\n", " ax.set_yscale(\"log\")\n", " ax.set_xticks(np.arange(len(ratio)))\n", " ax.set_xticklabels(ratio.index, rotation=90, fontsize=\"small\")\n", " ax.set_xlim(-0.6, len(ratio) - 0.4)\n", " ax.set_ylabel(\"Opteryx time / DuckDB time (log scale)\")\n", " ax.set_title(f\"TPC-DS SF{SCALE_FACTOR}: per-query ratio, sorted. \"\n", " \"Below 1, Opteryx was faster; above 1, DuckDB was\")\n", " ax.grid(axis=\"y\", alpha=0.3, which=\"both\")\n", " fig.tight_layout()\n", " plt.show()" ] }, { "cell_type": "markdown", "id": "d89e4d41", "metadata": {}, "source": [ "## Notes and caveats\n", "\n", "- **Coverage first.** A failed query on either engine is reported in §5, not dropped silently. The Opteryx TPC-DS suite tracks how many of the 99 plan and run as a number in its own right, and expects it to rise as the engine's SQL coverage grows. A run on a newer `opteryx-core` may cover more queries than the copy of the query set in §2 suggests.\n", "- **One process per engine, no concurrency.** There is no cold-cache control. The OS page cache warms naturally after the first iteration of each query, and each engine keeps its in-process caches from one query to the next, as it would in a server. The exception is the query after a timeout, which runs in a fresh process after its own warm-up.\n", "- **Timeouts.** A query that has not finished within `QUERY_TIMEOUT_S` per iteration is recorded as timed out and is not retried. Raise the limit in the configuration cell if a query is close to it.\n", "- **Same files, DuckDB's writer.** Both engines read Parquet written by DuckDB at its default settings, so the file layout (row group size, compression, encodings and statistics) is DuckDB's choice. Neither engine reads its own native storage here: DuckDB's `.duckdb` format and Parquet written by Opteryx's writer, rugo, are both faster for their own engine than this shared file.\n", "- **Row-count cross-check.** The check in §6 catches query-translation bugs. If the engines disagree on row counts for a query, treat that query's timing as suspect.\n", "- **Scaling up.** Raise `SCALE_FACTOR` in the configuration cell, for example to `10` for SF10. Generating and querying SF10 takes considerably longer, and it may exceed the memory of a free Colab runtime.\n", "- **Not a published result.** As the [Benchmarking](https://docs.opteryx.app/docs/reference/internals/benchmarking#what-the-numbers-are-not) page says, numbers from one machine say nothing about another. This notebook exists so you can measure for yourself." ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "name": "python" } }, "nbformat": 4, "nbformat_minor": 5 }