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: ['catalog_returns', 'inventory']. Below is the join graph for these fact tables: ['catalog_returns(cr_returned_date_sk) references date_dim(d_date_sk);', 'catalog_returns(cr_refunded_customer_sk) references customer(c_customer_sk);', 'catalog_returns(cr_reason_sk) references reason(r_reason_sk);'] ['inventory(inv_date_sk) references date_dim(d_date_sk);', 'inventory(inv_warehouse_sk) references warehouse(w_warehouse_sk);', 'inventory(inv_item_sk) references item(i_item_sk);']
- The original query must employ 3 joins.
- The original query must employ the following aggregate functions: ['count', 'sum', 'max'].
- 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
Sure! Here is a new query pair following the described requirements:
[ORIGINAL QUERY]
SELECT c_customer_id, CASE WHEN (SELECT COUNT(*) FROM catalog_returns WHERE cr_returned_date_sk BETWEEN 2450815 AND 2451179) > 100 THEN (SELECT SUM(cr_return_amount) FROM catalog_returns WHERE cr_returned_date_sk BETWEEN 2450815 AND 2451179) ELSE (SELECT MAX(cr_return_amount) FROM catalog_returns WHERE cr_returned_date_sk BETWEEN 2450815 AND 2451179) END AS returns_metric1, CASE WHEN (SELECT COUNT(*) FROM inventory WHERE inv_date_sk BETWEEN 2450815 AND 2451179 AND inv_warehouse_sk = 1) > 50 THEN (SELECT SUM(inv_quantity_on_hand) FROM inventory WHERE inv_date_sk BETWEEN 2450815 AND 2451179 AND inv_warehouse_sk = 1) ELSE (SELECT MAX(inv_quantity_on_hand) FROM inventory WHERE inv_date_sk BETWEEN 2450815 AND 2451179 AND inv_warehouse_sk = 1) END AS inventory_metric1 FROM customer, reason WHERE c_customer_sk = (SELECT cr_refunded_customer_sk FROM catalog_returns WHERE cr_reason_sk = r_reason_sk AND r_reason_sk = 1);[REWRITTEN QUERY]
WITH returns_bucket AS ( SELECT COUNT(*) AS cnt, SUM(cr_return_amount) AS sum_return_amount, MAX(cr_return_amount) AS max_return_amount FROM catalog_returns WHERE cr_returned_date_sk BETWEEN 2450815 AND 2451179 ), inventory_bucket AS ( SELECT COUNT(*) AS cnt, SUM(inv_quantity_on_hand) AS sum_quantity_on_hand, MAX(inv_quantity_on_hand) AS max_quantity_on_hand FROM inventory WHERE inv_date_sk BETWEEN 2450815 AND 2451179 AND inv_warehouse_sk = 1 ) SELECT c_customer_id, CASE WHEN rb.cnt > 100 THEN rb.sum_return_amount ELSE rb.max_return_amount END AS returns_metric1, CASE WHEN ib.cnt > 50 THEN ib.sum_quantity_on_hand ELSE ib.max_quantity_on_hand END AS inventory_metric1 FROM customer, reason, returns_bucket rb, inventory_bucket ib WHERE c_customer_sk = (SELECT cr_refunded_customer_sk FROM catalog_returns WHERE cr_reason_sk = r_reason_sk AND r_reason_sk = 1);In the original query:
- We used subqueries to calculate metrics based on conditions, causing the same subqueries to be run multiple times.
- The subqueries used aggregation functions
COUNT,SUM, andMAX. - We utilized the
catalog_returnsandinventorytables, joined withcustomerandreasontables based on the given relationships. - It also has a simple condition in the WHERE clause using
cr_refunded_customer_sk.
In the rewritten query:
- We used CTEs (
returns_bucketandinventory_bucket) to calculate aggregate metrics upfront, minimizing multiple scans of the same data. - We preserved the original join and condition structures.
- These optimizations led to improved performance while maintaining logical equivalency.