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_returns']. Below is the join graph for these fact tables: ['web_sales(ws_ship_mode_sk) references ship_mode(sm_ship_mode_sk);', 'web_sales(ws_item_sk) references item(i_item_sk);', 'web_sales(ws_bill_hdemo_sk) references household_demographics(hd_demo_sk);'] ['catalog_returns(cr_ship_mode_sk) references ship_mode(sm_ship_mode_sk);', 'catalog_returns(cr_returning_addr_sk) references customer_address(ca_address_sk);', 'catalog_returns(cr_catalog_page_sk) references catalog_page(cp_catalog_page_sk);']
- The original query must employ 3 joins.
- The original query must employ the following aggregate functions: ['sum'].
- The original query must employ the following operators in its WHERE clause: ['sum'].
- 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
Certainly! Below is the original and its corresponding rewritten query pair following the specified criteria and similar pattern as demonstrated:
[ORIGINAL QUERY]
WITH web_sales_agg AS ( SELECT ws_item_sk, SUM(ws_quantity) as total_quantity, SUM(ws_sales_price) as total_sales FROM web_sales GROUP BY ws_item_sk ), catalog_returns_agg AS ( SELECT cr_item_sk, SUM(cr_return_quantity) as total_return_quantity, SUM(cr_return_amount) as total_return_amount FROM catalog_returns GROUP BY cr_item_sk ) SELECT i.i_item_desc, sm.sm_type, CASE WHEN (SELECT SUM(total_quantity) FROM web_sales_agg WHERE ws_item_sk = i.i_item_sk) > 100 THEN (SELECT SUM(total_sales) FROM web_sales_agg WHERE ws_item_sk = i.i_item_sk) ELSE (SELECT SUM(total_return_amount) FROM catalog_returns_agg WHERE cr_item_sk = i.i_item_sk) END AS total_revenue FROM item i JOIN web_sales ws ON i.i_item_sk = ws.ws_item_sk JOIN ship_mode sm ON ws.ws_ship_mode_sk = sm.sm_ship_mode_sk JOIN catalog_returns cr ON i.i_item_sk = cr.cr_item_sk WHERE ws.ws_item_sk IS NOT NULL;[REWRITTEN QUERY]
WITH sales_returns_union AS ( SELECT i.i_item_sk, i.i_item_desc, sm.sm_type, SUM(CASE WHEN ws.ws_item_sk IS NOT NULL THEN ws.ws_quantity ELSE 0 END) AS total_quantity, SUM(CASE WHEN ws.ws_item_sk IS NOT NULL THEN ws.ws_sales_price ELSE 0 END) AS total_sales, SUM(CASE WHEN cr.cr_item_sk IS NOT NULL THEN cr.cr_return_quantity ELSE 0 END) AS total_return_quantity, SUM(CASE WHEN cr.cr_item_sk IS NOT NULL THEN cr.cr_return_amount ELSE 0 END) AS total_return_amount FROM item i LEFT JOIN web_sales ws ON i.i_item_sk = ws.ws_item_sk LEFT JOIN ship_mode sm ON ws.ws_ship_mode_sk = sm.sm_ship_mode_sk LEFT JOIN catalog_returns cr ON i.i_item_sk = cr.cr_item_sk GROUP BY i.i_item_sk, i.i_item_desc, sm.sm_type ) SELECT i_item_desc, sm_type, CASE WHEN total_quantity > 100 THEN total_sales ELSE total_return_amount END AS total_revenue FROM sales_returns_union WHERE i_item_sk IS NOT NULL;Explanation:
- The original query uses two CTEs, one for
web_salesaggregating quantities and sales, and another forcatalog_returnsaggregating return quantities and amounts. - It then uses scalar subqueries to decide which value (total sales or return amount) to select based on a condition involving a
SUM. - The rewritten query combines the logic into a single CTE that calculates all necessary sums in a single aggregation step and simplifies the final selection logic without scalar subqueries.
- This rewrite improves performance by reducing repetitive subquery evaluations, similar to the provided example.
- The original query uses two CTEs, one for