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 promotions,total,cast(promotions as decimal(15,4))/cast(total as decimal(15,4))*100 from (select sum(ss_ext_sales_price) promotions from store_sales ,store ,promotion ,date_dim ,customer ,customer_address ,item where ss_sold_date_sk = d_date_sk and ss_store_sk = s_store_sk and ss_promo_sk = p_promo_sk and ss_customer_sk= c_customer_sk and ca_address_sk = c_current_addr_sk and ss_item_sk = i_item_sk and ca_gmt_offset = -7 and i_category = 'Home' and (p_channel_dmail = 'Y' or p_channel_email = 'Y' or p_channel_tv = 'Y') and s_gmt_offset = -7 and d_year = 2000 and d_moy = 12) promotional_sales, (select sum(ss_ext_sales_price) total from store_sales ,store ,date_dim ,customer ,customer_address ,item where ss_sold_date_sk = d_date_sk and ss_store_sk = s_store_sk and ss_customer_sk= c_customer_sk and ca_address_sk = c_current_addr_sk and ss_item_sk = i_item_sk and ca_gmt_offset = -7 and i_category = 'Home' and s_gmt_offset = -7 and d_year = 2000 and d_moy = 12) all_sales order by promotions, total limit 100;[REWRITTEN QUERY]
select promotions, total, (promotions / total) * 100 as promotion_percentage from ( select sum(case when p_channel_dmail = 'Y' or p_channel_email = 'Y' or p_channel_tv = 'Y' then ss_ext_sales_price else 0 end) as promotions, sum(ss_ext_sales_price) as total from store_sales join store on ss_store_sk = s_store_sk join promotion on ss_promo_sk = p_promo_sk join date_dim on ss_sold_date_sk = d_date_sk join customer on ss_customer_sk = c_customer_sk join customer_address on ca_address_sk = c_current_addr_sk join item on ss_item_sk = i_item_sk where ca_gmt_offset = -7 and i_category = 'Home' and s_gmt_offset = -7 and d_year = 2000 and d_moy = 12 ) as sales_data order by promotions, total 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 contain CTEs.
- The original query must make use of the following fact tables: ['web_sales']. Below is the join graph for these fact tables: ['web_sales(ws_web_page_sk) references web_page(wp_web_page_sk);', 'web_sales(ws_ship_addr_sk) references customer_address(ca_address_sk);', 'web_sales(ws_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: ['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
[ORIGINAL QUERY]
with max_revenue as ( select ws_warehouse_sk, max(ws_ext_discount_amt) as max_discount from web_sales join web_page on ws_web_page_sk = wp_web_page_sk join customer_address on ws_ship_addr_sk = ca_address_sk where ws_ext_discount_amt > (select min(ws_ext_discount_amt) from web_sales) group by ws_warehouse_sk ), max_quantity as ( select ws_warehouse_sk, max(ws_quantity) as max_qty from web_sales join web_page on ws_web_page_sk = wp_web_page_sk join warehouse on ws_warehouse_sk = w_warehouse_sk where ws_quantity > (select min(ws_quantity) from web_sales) group by ws_warehouse_sk ) select m.max_discount, q.max_qty from max_revenue m join max_quantity q on m.ws_warehouse_sk = q.ws_warehouse_sk order by m.max_discount desc, q.max_qty desc limit 50;[REWRITTEN QUERY]
select ws_warehouse_sk, max(case when ws_ext_discount_amt > (select min(ws_ext_discount_amt) from web_sales) then ws_ext_discount_amt else 0 end) as max_discount, max(case when ws_quantity > (select min(ws_quantity) from web_sales) then ws_quantity else 0 end) as max_qty from web_sales join web_page on ws_web_page_sk = wp_web_page_sk join customer_address on ws_ship_addr_sk = ca_address_sk join warehouse on ws_warehouse_sk = w_warehouse_sk group by ws_warehouse_sk order by max_discount desc, max_qty desc limit 50;In this new query pair:
- The
ORIGINAL QUERYuses CTEsmax_revenueandmax_quantityto separately compute the maximum discount amount and quantity byws_warehouse_skafter filtering with a minimum value using nested queries. - The
REWRITTEN QUERYeliminates the CTEs by usingCASEstatements within theMAXfunction to compute both themax_discountandmax_qtyvalues directly within a single aggregation context, which simplifies processing and potentially improves performance. - Both queries are logically equivalent, performing same aggregations and filtering, but the rewritten query reduces intermediate result complexity.
- The