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 not have any CTEs.
- The original query must make use of the following fact tables: ['store_sales', 'store_returns']. Below is the join graph for these fact tables: ['store_sales(ss_sold_time_sk) references time_dim(t_time_sk);', 'store_sales(ss_item_sk) references item(i_item_sk);', 'store_sales(ss_customer_sk) references customer(c_customer_sk);'] ['store_returns(sr_cdemo_sk) references customer_demographics(cd_demo_sk);', 'store_returns(sr_returned_date_sk) references date_dim(d_date_sk);', 'store_returns(sr_customer_sk) references customer(c_customer_sk);']
- The original query must employ 4 joins.
- The original query must employ the following aggregate functions: ['max'].
- The original query must employ the following operators in its WHERE clause: ['count', '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
Here is an original and rewritten query pair that satisfies the given constraints and follows a similar rewrite pattern to the example provided:
[ORIGINAL QUERY]
SELECT CASE WHEN (SELECT COUNT(*) FROM store_sales WHERE ss_item_sk BETWEEN 1 AND 100) > 50000 THEN (SELECT MAX(ss_ext_list_price) FROM store_sales WHERE ss_item_sk BETWEEN 1 AND 100) ELSE (SELECT MAX(ss_net_paid) FROM store_sales WHERE ss_item_sk BETWEEN 1 AND 100) END AS bucket1, CASE WHEN (SELECT COUNT(*) FROM store_returns WHERE sr_customer_sk BETWEEN 101 AND 200) > 30000 THEN (SELECT MAX(sr_return_amt) FROM store_returns WHERE sr_customer_sk BETWEEN 101 AND 200) ELSE (SELECT MAX(sr_ext_tax) FROM store_returns WHERE sr_customer_sk BETWEEN 101 AND 200) END AS bucket2 FROM store_sales JOIN store_returns ON store_sales.ss_customer_sk = store_returns.sr_customer_sk JOIN time_dim ON store_sales.ss_sold_time_sk = time_dim.t_time_sk JOIN customer ON store_sales.ss_customer_sk = customer.c_customer_sk WHERE (SELECT AVG(ss_wholesale_cost) FROM store_sales) > 50 AND (SELECT MAX(cd_income_band_sk) FROM customer_demographics) < 15;[REWRITTEN QUERY]
WITH sales_bucket AS ( SELECT CASE WHEN ss_item_sk BETWEEN 1 AND 100 THEN 1 ELSE 0 END AS item_range, COUNT(*) AS cnt_sales, MAX(ss_ext_list_price) AS max_ext_list_price, MAX(ss_net_paid) AS max_net_paid FROM store_sales GROUP BY CASE WHEN ss_item_sk BETWEEN 1 AND 100 THEN 1 ELSE 0 END ), returns_bucket AS ( SELECT CASE WHEN sr_customer_sk BETWEEN 101 AND 200 THEN 1 ELSE 0 END AS customer_range, COUNT(*) AS cnt_returns, MAX(sr_return_amt) AS max_return_amt, MAX(sr_ext_tax) AS max_ext_tax FROM store_returns GROUP BY CASE WHEN sr_customer_sk BETWEEN 101 AND 200 THEN 1 ELSE 0 END ) SELECT CASE WHEN sb.cnt_sales > 50000 THEN sb.max_ext_list_price ELSE sb.max_net_paid END AS bucket1, CASE WHEN rb.cnt_returns > 30000 THEN rb.max_return_amt ELSE rb.max_ext_tax END AS bucket2 FROM (SELECT * FROM sales_bucket WHERE item_range = 1) sb, (SELECT * FROM returns_bucket WHERE customer_range = 1) rb, time_dim, customer WHERE (SELECT AVG(ss_wholesale_cost) FROM store_sales) > 50 AND (SELECT MAX(cd_income_band_sk) FROM customer_demographics) < 15;Explanation
-
Original Query:
- Performs subqueries for
countandmaxon different ranges instore_salesandstore_returns. - Joins
store_saleswithstore_returns,time_dim, andcustomer. - Uses aggregate functions like
COUNT,MAX, andAVGin different conditions.
- Performs subqueries for
-
Rewritten Query:
- Uses Common Table Expressions (CTEs) to precompute bucketed aggregates.
- Combines results from the CTEs for a simplified, single final query execution.
- Maintains the same logic and conditions as the original query, handling buckets and aggregates, but improves performance by avoiding repeated computations.
-