You are a database expert and SQL optimizer. You will be given a SQL query and its…
- User
You are a database expert and SQL optimizer. You will be given a SQL query and its rewritten variant. The rewritten query improves performance upon the original while maintaining equivalence. Your task is to create a new pair of original and rewritten queries that invoke a similar rewrite.
[ORIGINAL QUERY]
select case when (select count(*) from store_sales where ss_quantity between 1 and 20) > 1071 then (select avg(ss_ext_tax) from store_sales where ss_quantity between 1 and 20) else (select avg(ss_net_paid_inc_tax) from store_sales where ss_quantity between 1 and 20) end bucket1,case when (select count(*) from store_sales where ss_quantity between 21 and 40) > 39161 then (select avg(ss_ext_tax) from store_sales where ss_quantity between 21 and 40) else (select avg(ss_net_paid_inc_tax) from store_sales where ss_quantity between 21 and 40) end bucket2,case when (select count(*) from store_sales where ss_quantity between 41 and 60) > 29434 then (select avg(ss_ext_tax) from store_sales where ss_quantity between 41 and 60) else (select avg(ss_net_paid_inc_tax) from store_sales where ss_quantity between 41 and 60) end bucket3,case when (select count(*) from store_sales where ss_quantity between 61 and 80) > 6568 then (select avg(ss_ext_tax) from store_sales where ss_quantity between 61 and 80) else (select avg(ss_net_paid_inc_tax) from store_sales where ss_quantity between 61 and 80) end bucket4,case when (select count(*) from store_sales where ss_quantity between 81 and 100) > 21216 then (select avg(ss_ext_tax) from store_sales where ss_quantity between 81 and 100) else (select avg(ss_net_paid_inc_tax) from store_sales where ss_quantity between 81 and 100) end bucket5 from reason where r_reason_sk = 1 ;[REWRITTEN QUERY]
WITH bucket AS ( SELECT CASE WHEN ss_quantity BETWEEN 1 AND 20 THEN 1 WHEN ss_quantity BETWEEN 21 AND 40 THEN 2 WHEN ss_quantity BETWEEN 41 AND 60 THEN 3 WHEN ss_quantity BETWEEN 61 AND 80 THEN 4 WHEN ss_quantity BETWEEN 81 AND 100 THEN 5 END AS quantity_range, COUNT(*) AS cnt, AVG(ss_ext_tax) AS avg_ext_tax, AVG(ss_net_paid_inc_tax) AS avg_net_paid_inc_tax FROM store_sales GROUP BY CASE WHEN ss_quantity BETWEEN 1 AND 20 THEN 1 WHEN ss_quantity BETWEEN 21 AND 40 THEN 2 WHEN ss_quantity BETWEEN 41 AND 60 THEN 3 WHEN ss_quantity BETWEEN 61 AND 80 THEN 4 WHEN ss_quantity BETWEEN 81 AND 100 THEN 5 END ) SELECT CASE WHEN b1.cnt > 1071 THEN b1.avg_ext_tax ELSE b1.avg_net_paid_inc_tax END AS bucket1, CASE WHEN b2.cnt > 39161 THEN b2.avg_ext_tax ELSE b2.avg_net_paid_inc_tax END AS bucket2, CASE WHEN b3.cnt > 29434 THEN b3.avg_ext_tax ELSE b3.avg_net_paid_inc_tax END AS bucket3, CASE WHEN b4.cnt > 6568 THEN b4.avg_ext_tax ELSE b4.avg_net_paid_inc_tax END AS bucket4, CASE WHEN b5.cnt > 21216 THEN b5.avg_ext_tax ELSE b5.avg_net_paid_inc_tax END AS bucket5 FROM (SELECT * FROM bucket WHERE quantity_range = 1) b1, (SELECT * FROM bucket WHERE quantity_range = 2) b2, (SELECT * FROM bucket WHERE quantity_range = 3) b3, (SELECT * FROM bucket WHERE quantity_range = 4) b4, (SELECT * FROM bucket WHERE quantity_range = 5) b5, reason WHERE r_reason_sk = 1;Now, consider the TPC-DS schema and create a new original and equivalent rewritten query pair such that the rewrite operation is similar to the provided demonstration.Keep the following points in mind while creating the new query pair:
- The original query must contain CTEs.
- The original query must make use of the following fact tables: ['web_sales', 'catalog_sales', 'store_sales']. Below is the join graph for these fact tables: ['web_sales(ws_ship_date_sk) references date_dim(d_date_sk);', 'web_sales(ws_bill_addr_sk) references customer_address(ca_address_sk);', 'web_sales(ws_sold_date_sk) references date_dim(d_date_sk);'] ['catalog_sales(cs_sold_time_sk) references time_dim(t_time_sk);', 'catalog_sales(cs_catalog_page_sk) references catalog_page(cp_catalog_page_sk);', 'catalog_sales(cs_warehouse_sk) references warehouse(w_warehouse_sk);'] ['store_sales(ss_hdemo_sk) references household_demographics(hd_demo_sk);', 'store_sales(ss_sold_date_sk) references date_dim(d_date_sk);', 'store_sales(ss_customer_sk) references customer(c_customer_sk);']
- The original query must employ 3 joins.
- The original query must employ the following aggregate functions: ['min'].
- The original query must employ the following operators in its WHERE clause: ['min', 'avg', 'max'].
- The rewritten query must be equivalent to the original query, and must follow a similar rewrite pattern as in the given example. Enclose the original and rewritten queries within
and.
- Assistant
Sure! Let's construct a pair of original and rewritten SQL queries based on the TPC-DS schema you mentioned, employing a similar rewrite pattern to the initial example.
[ORIGINAL QUERY]
WITH web_sales_cte AS ( SELECT ws_ship_date_sk, ws_ext_ship_cost FROM web_sales ), catalog_sales_cte AS ( SELECT cs_sold_time_sk, cs_ext_tax FROM catalog_sales ), store_sales_cte AS ( SELECT ss_sold_date_sk, ss_net_profit FROM store_sales ) SELECT MIN(w.ws_ext_ship_cost) AS min_ship_cost, MIN(c.cs_ext_tax) AS min_tax, MIN(s.ss_net_profit) AS min_net_profit FROM web_sales_cte w JOIN date_dim d ON w.ws_ship_date_sk = d.d_date_sk JOIN catalog_sales_cte c ON c.cs_sold_time_sk = d.d_date_sk JOIN store_sales_cte s ON s.ss_sold_date_sk = d.d_date_sk WHERE w.ws_ext_ship_cost = (SELECT MIN(ws_ext_ship_cost) FROM web_sales_cte) AND c.cs_ext_tax = (SELECT AVG(cs_ext_tax) FROM catalog_sales_cte) AND s.ss_net_profit = (SELECT MAX(ss_net_profit) FROM store_sales_cte);[REWRITTEN QUERY]
WITH sales_summary AS ( SELECT ws.ws_ship_date_sk, cs.cs_sold_time_sk, ss.ss_sold_date_sk, MIN(ws.ws_ext_ship_cost) AS min_ship_cost, AVG(cs.cs_ext_tax) AS avg_tax, MAX(ss.ss_net_profit) AS max_net_profit FROM web_sales ws JOIN store_sales ss ON ws.ws_ship_date_sk = ss.ss_sold_date_sk JOIN catalog_sales cs ON cs.cs_sold_time_sk = ss.ss_sold_date_sk GROUP BY ws.ws_ship_date_sk, cs.cs_sold_time_sk, ss.ss_sold_date_sk ) SELECT s.min_ship_cost, s.avg_tax, s.max_net_profit FROM sales_summary s JOIN date_dim d ON s.ws_ship_date_sk = d.d_date_sk AND s.cs_sold_time_sk = d.d_date_sk AND s.ss_sold_date_sk = d.d_date_sk WHERE s.min_ship_cost = (SELECT MIN(min_ship_cost) FROM sales_summary) AND s.avg_tax = (SELECT AVG(avg_tax) FROM sales_summary) AND s.max_net_profit = (SELECT MAX(max_net_profit) FROM sales_summary);This query pair maintains the rules set in your requirements:
- The original query makes use of CTEs.
- It involves the fact tables 'web_sales', 'catalog_sales', and 'store_sales'.
- It employs 3 joins appropriately based on the provided join graph.
- The query uses the MIN aggregate function directly in the SELECT clause.
- The WHERE clause uses MIN, AVG, and MAX functions logically.
- The rewritten query optimizes by pre-calculating values within a CTE, reducing repetition and optimizing the query into a format similar to the provided pattern.