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]
with customer_total_return as (select sr_customer_sk as ctr_customer_sk,sr_store_sk as ctr_store_sk,sum(SR_FEE) as ctr_total_return from store_returns,date_dim where sr_returned_date_sk = d_date_sk and d_year =2000 group by sr_customer_sk,sr_store_sk) select c_customer_id from customer_total_return ctr1,store,customer where ctr1.ctr_total_return > (select avg(ctr_total_return)*1.2 from customer_total_return ctr2 where ctr1.ctr_store_sk = ctr2.ctr_store_sk) and s_store_sk = ctr1.ctr_store_sk and s_state = 'TN' and ctr1.ctr_customer_sk = c_customer_sk order by c_customer_id limit 100;[REWRITTEN QUERY]
with customer_total_return as ( select sr_customer_sk as ctr_customer_sk, sr_store_sk as ctr_store_sk, sum(SR_FEE) as ctr_total_return from store_returns join date_dim on sr_returned_date_sk = d_date_sk where d_year = 2000 group by sr_customer_sk , sr_store_sk ), avg_customer_return as ( select ctr_store_sk, avg(ctr_total_return) as avg_return from customer_total_return group by ctr_store_sk ) select c_customer_id from customer_total_return ctr1 join store on s_store_sk = ctr1.ctr_store_sk join customer on ctr1.ctr_customer_sk = c_customer_sk join avg_customer_return acr on ctr1.ctr_store_sk = acr.ctr_store_sk where ctr1.ctr_total_return > acr.avg_return * 1.2 and s_state = 'TN' order by c_customer_id limit 100;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_sales', 'store_returns']. Below is the join graph for these fact tables: ['catalog_sales(cs_promo_sk) references promotion(p_promo_sk);', 'catalog_sales(cs_ship_cdemo_sk) references customer_demographics(cd_demo_sk);', 'catalog_sales(cs_bill_hdemo_sk) references household_demographics(hd_demo_sk);'] ['store_returns(sr_customer_sk) references customer(c_customer_sk);', 'store_returns(sr_reason_sk) references reason(r_reason_sk);', 'store_returns(sr_item_sk) references item(i_item_sk);']
- The original query must employ 2 joins.
- The original query must employ the following aggregate functions: ['avg', 'sum', 'max'].
- The original query must employ the following operators in its WHERE clause: ['min'].
- 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 new pair of original and rewritten queries following the given requirements and rewrite pattern.
[ORIGINAL QUERY]
SELECT p.p_promo_id, MAX(cs_quantity) AS max_quantity FROM catalog_sales cs, promotion p, store_returns sr WHERE cs.cs_promo_sk = p.p_promo_sk AND sr.sr_item_sk = cs.cs_item_sk AND sr.sr_return_amt > (SELECT MIN(sr_return_amt) FROM store_returns WHERE sr_store_sk = cs.cs_warehouse_sk) GROUP BY p.p_promo_id;[REWRITTEN QUERY]
WITH promo_sales AS ( SELECT p_promo_sk, cs_item_sk, MAX(cs_quantity) AS max_quantity FROM catalog_sales JOIN promotion ON cs_promo_sk = p_promo_sk GROUP BY p_promo_sk, cs_item_sk ), min_return_amt AS ( SELECT sr_store_sk, MIN(sr_return_amt) AS min_amt FROM store_returns GROUP BY sr_store_sk ) SELECT p.p_promo_id, ps.max_quantity FROM promo_sales ps JOIN promotion p ON ps.p_promo_sk = p.p_promo_sk JOIN store_returns sr ON ps.cs_item_sk = sr.sr_item_sk JOIN min_return_amt mra ON sr.sr_store_sk = mra.sr_store_sk WHERE sr.sr_return_amt > mra.min_amt;Explanation:
-
Original Query:
- Utilizes two joins between
catalog_sales->promotionandstore_returns->catalog_salesbased on the item key. - Returns the
p_promo_idand maximum quantity of catalog sales where the return amount exceeds the minimum return amount for the corresponding store.
- Utilizes two joins between
-
Rewritten Query (optimization):
- Introduces CTEs:
promo_sales: Calculates max quantity grouped by promotion and item.min_return_amt: Calculates minimum return amount grouped by store.
- Replaces subquery in WHERE clause by CTE join.
- Introduces CTEs:
Both queries are equivalent and adhere to the constraints provided. The rewritten version organizes the logic into CTEs to streamline complex nested subqueries into more manageable parts, much like the provided example.
-