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]
select case when count(case when ss_quantity between 1 and 20 then 1 end) > 1071 then avg(case when ss_quantity between 1 and 20 then ss_ext_tax end) else avg(case when ss_quantity between 1 and 20 then ss_net_paid_inc_tax end) end as bucket1, case when count(case when ss_quantity between 21 and 40 then 1 end) > 39161 then avg(case when ss_quantity between 21 and 40 then ss_ext_tax end) else avg(case when ss_quantity between 21 and 40 then ss_net_paid_inc_tax end) end as bucket2, case when count(case when ss_quantity between 41 and 60 then 1 end) > 29434 then avg(case when ss_quantity between 41 and 60 then ss_ext_tax end) else avg(case when ss_quantity between 41 and 60 then ss_net_paid_inc_tax end) end as bucket3, case when count(case when ss_quantity between 61 and 80 then 1 end) > 6568 then avg(case when ss_quantity between 61 and 80 then ss_ext_tax end) else avg(case when ss_quantity between 61 and 80 then ss_net_paid_inc_tax end) end as bucket4, case when count(case when ss_quantity between 81 and 100 then 1 end) > 21216 then avg(case when ss_quantity between 81 and 100 then ss_ext_tax end) else avg(case when ss_quantity between 81 and 100 then ss_net_paid_inc_tax end) end as bucket5 from store_sales where exists (select 1 from 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 not have any CTEs.
- The original query must make use of the following fact tables: ['web_sales', 'inventory']. Below is the join graph for these fact tables: ['web_sales(ws_bill_customer_sk) references customer(c_customer_sk);', 'web_sales(ws_ship_customer_sk) references customer(c_customer_sk);', 'web_sales(ws_ship_addr_sk) references customer_address(ca_address_sk);'] ['inventory(inv_item_sk) references item(i_item_sk);', 'inventory(inv_date_sk) references date_dim(d_date_sk);', 'inventory(inv_warehouse_sk) references warehouse(w_warehouse_sk);']
- The original query must employ 3 joins.
- The original query must employ the following aggregate functions: ['max'].
- The original query must employ the following operators in its WHERE clause: ['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
Here is a pair of original and rewritten SQL queries that follow the given requirements and demonstrate a similar optimization pattern to the example provided:
[ORIGINAL QUERY]
SELECT CASE WHEN (SELECT MAX(ws_quantity) FROM web_sales WHERE ws_quantity BETWEEN 10 AND 30) > 100 THEN (SELECT MAX(ws_list_price) FROM web_sales WHERE ws_quantity BETWEEN 10 AND 30) ELSE (SELECT MAX(ws_sales_price) FROM web_sales WHERE ws_quantity BETWEEN 10 AND 30) END AS price_bucket1, CASE WHEN (SELECT MAX(inv_quantity_on_hand) FROM inventory WHERE inv_quantity_on_hand BETWEEN 20 AND 40) > 500 THEN (SELECT MAX(inv_warehouse_sk) FROM inventory WHERE inv_quantity_on_hand BETWEEN 20 AND 40) ELSE (SELECT MAX(inv_date_sk) FROM inventory WHERE inv_quantity_on_hand BETWEEN 20 AND 40) END AS stock_bucket1 FROM web_sales ws JOIN customer c ON ws.ws_bill_customer_sk = c.c_customer_sk JOIN customer_address ca ON ws.ws_ship_addr_sk = ca.ca_address_sk JOIN inventory i ON i.inv_item_sk = ws.ws_item_sk[REWRITTEN QUERY]
SELECT CASE WHEN MAX(CASE WHEN ws_quantity BETWEEN 10 AND 30 THEN ws_quantity END) > 100 THEN MAX(CASE WHEN ws_quantity BETWEEN 10 AND 30 THEN ws_list_price END) ELSE MAX(CASE WHEN ws_quantity BETWEEN 10 AND 30 THEN ws_sales_price END) END AS price_bucket1, CASE WHEN MAX(CASE WHEN inv_quantity_on_hand BETWEEN 20 AND 40 THEN inv_quantity_on_hand END) > 500 THEN MAX(CASE WHEN inv_quantity_on_hand BETWEEN 20 AND 40 THEN inv_warehouse_sk END) ELSE MAX(CASE WHEN inv_quantity_on_hand BETWEEN 20 AND 40 THEN inv_date_sk END) END AS stock_bucket1 FROM web_sales ws JOIN customer c ON ws.ws_bill_customer_sk = c.c_customer_sk JOIN customer_address ca ON ws.ws_ship_addr_sk = ca.ca_address_sk JOIN inventory i ON i.inv_item_sk = ws.ws_item_sk WHERE EXISTS (SELECT 1 FROM web_sales WHERE ws_quantity BETWEEN 10 AND 30) OR EXISTS (SELECT 1 FROM inventory WHERE inv_quantity_on_hand BETWEEN 20 AND 40)Explanation
- Both the original and rewritten queries perform the necessary joins between the
web_sales,customer,customer_address, andinventorytables, thus satisfying the requirement of including 3 joins. - The original query uses subqueries with aggregate functions, while the rewritten query integrates
CASE WHENlogic directly within aggregate functions to optimize repeated expressions. - The rewritten query operates using conditions within
CASEstatements and performs theMAXaggregation which mirrors the performance gains demonstrated in the example you provided.
- Both the original and rewritten queries perform the necessary joins between the