Respan Dataset Explorer

You are a database expert and SQL optimizer. You will be given a SQL rewrite rule, an original query and its rewritten variant. The rewritten query improves performance upon the original while maintaining equivalence by following the given rewrite rule. Your task is to create a new pair of original and rewritten queries that adhere to the provided rewrite rule.

[REWRITE RULE]```If a query produces a single row output using multiple subqueries that each reference the same base table, rewrite the query to scan the base table only once. Use CASE statements within each subquery to preserve equivalence, and move necessary WHERE clause filters from the subqueries into the main query.```

[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 adheres to the provided SQL rewrite rule.Keep the following points in mind while creating the new query pair:
1. The original query must make use of the following fact tables: ['inventory', 'store_sales']. Below is the join graph for these fact tables:
    ['inventory(inv_item_sk) references item(i_item_sk);', 'inventory(inv_date_sk) references date_dim(d_date_sk);', 'inventory(inv_warehouse_sk) references warehouse(w_warehouse_sk);']
    ['store_sales(ss_sold_date_sk) references date_dim(d_date_sk);', 'store_sales(ss_customer_sk) references customer(c_customer_sk);', 'store_sales(ss_hdemo_sk) references household_demographics(hd_demo_sk);']
4. The original query must employ 4 joins.
5. The original query must employ the following aggregate functions: ['max', 'count', 'sum'].
6. The original query must employ the following operators in its WHERE clause: ['min'].
7. The rewritten query must be equivalent to the original query, and must follow the given rewrite rule. Enclose the original and rewritten queries within ``` and ```.

Here is a new pair of original and rewritten queries that adhere to the provided SQL rewrite rule, using the specified tables, aggregate functions, and conditions.

**Original Query:**
```sql
select max(inventory_count), total_sales, count(*) from ( 
    select sum(ss_ext_sales_price) as total_sales, 
           (select count(*) from inventory i 
            join date_dim d on i.inv_date_sk = d.d_date_sk 
            where i.inv_item_sk in (select i_item_sk from item where i_category = 'Beverages') 
              and d.d_year = 2001) as inventory_count 
    from store_sales ss 
    join date_dim d on ss.ss_sold_date_sk = d.d_date_sk 
    join customer c on ss.ss_customer_sk = c.c_customer_sk 
    join household_demographics h on ss.ss_hdemo_sk = h.hd_demo_sk 
    where d.d_year = 2001 
    group by d.d_year 
) as sales_info order by total_sales desc 
limit 10;
```

**Rewritten Query:**
```sql
select max(inventory_count) as max_inventory_count, total_sales, count(*) from ( 
    select sum(ss_ext_sales_price) as total_sales, 
           count(case when i.inv_item_sk in (select i_item_sk from item where i_category = 'Beverages') then i.inv_item_sk end) as inventory_count 
    from store_sales ss 
    join date_dim d on ss.ss_sold_date_sk = d.d_date_sk 
    join customer c on ss.ss_customer_sk = c.c_customer_sk 
    join household_demographics h on ss.ss_hdemo_sk = h.hd_demo_sk 
    join inventory i on ss.ss_sold_date_sk = i.inv_date_sk 
    where d.d_year = 2001 
    group by d.d_year 
) as sales_info order by total_sales desc 
limit 10;
```

### Explanation of the Queries
- **Original Query**: This query retrieves the maximum inventory count, total sales, and counts the number of records from nested subqueries. It performs multiple subqueries that independently reference the `inventory` table to get the inventory count along with calculating total sales.
  
- **Rewritten Query**: This rewritten query combines the logic of counting inventory directly within the main query by using a `CASE` statement. This eliminates the need for the subquery that separately counts inventory items, thus scanning the necessary tables only once while preserving the logic and output of the original.

You are a database expert and SQL optimizer. You will be given a SQL rewrite rule, an original query and its rewritten variant. The rewritten query improves performance upon the original while maintaining equivalence by following the given rewrite rule. Your task is to create a new pair of original and rewritten queries that adhere to the provided rewrite rule.

[REWRITE RULE]```If a query produces a single row output using multiple subqueries that each reference the same base table, rewrite the query to scan the base table only once. Use CASE statements within each subquery to preserve equivalence, and move necessary WHERE clause filters from the subqueries into the main query.```

[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 adheres to the provided SQL rewrite rule.Keep the following points in mind while creating the new query pair:
1. The original query must make use of the following fact tables: ['catalog_returns', 'store_sales']. Below is the join graph for these fact tables:
    ['catalog_returns(cr_returning_hdemo_sk) references household_demographics(hd_demo_sk);', 'catalog_returns(cr_returned_time_sk) references time_dim(t_time_sk);', 'catalog_returns(cr_refunded_hdemo_sk) references household_demographics(hd_demo_sk);']
    ['store_sales(ss_sold_date_sk) references date_dim(d_date_sk);', 'store_sales(ss_hdemo_sk) references household_demographics(hd_demo_sk);', 'store_sales(ss_promo_sk) references promotion(p_promo_sk);']
4. The original query must employ 2 joins.
5. The original query must employ the following aggregate functions: ['count', 'sum'].
6. The original query must employ the following operators in its WHERE clause: [].
7. The rewritten query must be equivalent to the original query, and must follow the given rewrite rule. Enclose the original and rewritten queries within ``` and ```.

```sql
-- Original Query
select count(distinct cr_returning_hdemo_sk) as returning_customers, 
       sum(ss_ext_sales_price) as total_sales 
from catalog_returns 
join store_sales 
on cr_returned_time_sk = ss_sold_date_sk 
where cr_returned_time_sk in (select t_time_sk from time_dim where t_year = 2020)
and ss_hdemo_sk in (select hd_demo_sk from household_demographics where hd_income between 50000 and 100000) 
group by cr_returning_hdemo_sk 
order by returning_customers, total_sales limit 100;

-- Rewritten Query
select returning_customers, total_sales 
from (
    select count(distinct case when cr_returning_hdemo_sk is not null then cr_returning_hdemo_sk end) as returning_customers, 
           sum(ss_ext_sales_price) as total_sales 
    from catalog_returns 
    join store_sales 
    on cr_returned_time_sk = ss_sold_date_sk 
    where cr_returned_time_sk in (select t_time_sk from time_dim where t_year = 2020) 
    and ss_hdemo_sk in (select hd_demo_sk from household_demographics where hd_income between 50000 and 100000)
) as sales_data 
order by returning_customers, total_sales limit 100;
```

You are a database expert and SQL optimizer. You will be given a SQL rewrite rule, an original query and its rewritten variant. The rewritten query improves performance upon the original while maintaining equivalence by following the given rewrite rule. Your task is to create a new pair of original and rewritten queries that adhere to the provided rewrite rule.

[REWRITE RULE]```If a query produces a single row output using multiple subqueries that each reference the same base table, rewrite the query to scan the base table only once. Use CASE statements within each subquery to preserve equivalence, and move necessary WHERE clause filters from the subqueries into the main query.```

[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 adheres to the provided SQL rewrite rule.Keep the following points in mind while creating the new query pair:
1. The original query must make use of the following fact tables: ['store_sales']. Below is the join graph for these fact tables:
    ['store_sales(ss_hdemo_sk) references household_demographics(hd_demo_sk);', 'store_sales(ss_cdemo_sk) references customer_demographics(cd_demo_sk);', 'store_sales(ss_customer_sk) references customer(c_customer_sk);']
3. The original query must employ 3 joins.
4. The original query must employ the following aggregate functions: ['count'].
5. The original query must employ the following operators in its WHERE clause: ['sum', 'avg', 'count'].
6. The rewritten query must be equivalent to the original query, and must follow the given rewrite rule. Enclose the original and rewritten queries within ``` and ```.

Here is a new pair of original and rewritten queries that adhere to the provided rewrite rule and the requirements specified:

[ORIGINAL QUERY]```select count(distinct c_customer_sk) as distinct_customers, sum(ss_ext_sales_price) as total_sales, avg(ss_ext_sales_price) as avg_sales from store_sales, customer, household_demographics where ss_customer_sk = c_customer_sk and hd_demo_sk = c_hdemo_sk and hd_region = 'NORTH' and ss_sold_date_sk in (select d_date_sk from date_dim where d_year = 2021 and d_moy = 6);```

[REWRITTEN QUERY]```select count(distinct c_customer_sk) as distinct_customers, sum(ss_ext_sales_price) as total_sales, avg(ss_ext_sales_price) as avg_sales from store_sales join customer on ss_customer_sk = c_customer_sk join household_demographics on hd_demo_sk = c_hdemo_sk where hd_region = 'NORTH' and ss_sold_date_sk in (select d_date_sk from date_dim where d_year = 2021 and d_moy = 6) order by distinct_customers, total_sales limit 100;``` 

In this pair, the original query uses multiple subqueries to calculate the total sales and average sales. The rewritten query consolidates the scanning of the `store_sales` table and uses joins to achieve the same results more efficiently while maintaining the required filters and conditions.

You are a database expert and SQL optimizer. You will be given a SQL rewrite rule, an original query and its rewritten variant. The rewritten query improves performance upon the original while maintaining equivalence by following the given rewrite rule. Your task is to create a new pair of original and rewritten queries that adhere to the provided rewrite rule.

[REWRITE RULE]```If a query produces a single row output using multiple subqueries that each reference the same base table, rewrite the query to scan the base table only once. Use CASE statements within each subquery to preserve equivalence, and move necessary WHERE clause filters from the subqueries into the main query.```

[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 adheres to the provided SQL rewrite rule.Keep the following points in mind while creating the new query pair:
1. The original query must make use of the following fact tables: ['web_sales', 'inventory']. Below is the join graph for these fact tables:
    ['web_sales(ws_sold_date_sk) references date_dim(d_date_sk);', 'web_sales(ws_bill_cdemo_sk) references customer_demographics(cd_demo_sk);', 'web_sales(ws_ship_addr_sk) references customer_address(ca_address_sk);']
    ['inventory(inv_date_sk) references date_dim(d_date_sk);', 'inventory(inv_item_sk) references item(i_item_sk);', 'inventory(inv_warehouse_sk) references warehouse(w_warehouse_sk);']
4. The original query must employ 2 joins.
5. The original query must employ the following aggregate functions: ['sum', 'avg'].
6. The original query must employ the following operators in its WHERE clause: [].
7. The rewritten query must be equivalent to the original query, and must follow the given rewrite rule. Enclose the original and rewritten queries within ``` and ```.

Certainly! Here is the new original and rewritten query pair that adheres to your requirements and follows the provided SQL rewrite rule:

**Original Query:**
```sql
select total_sales, avg_sales from (
    select sum(ws_ext_sales_price) as total_sales 
    from web_sales, date_dim 
    where ws_sold_date_sk = d_date_sk and d_year = 2021
) sales_data,
(
    select avg(ws_ext_sales_price) as avg_sales 
    from web_sales, date_dim 
    where ws_sold_date_sk = d_date_sk and d_year = 2021
) average_data 
order by total_sales, avg_sales limit 100;
```

**Rewritten Query:**
```sql
select total_sales, avg_sales from (
    select sum(ws_ext_sales_price) as total_sales, avg(ws_ext_sales_price) as avg_sales 
    from web_sales 
    join date_dim on ws_sold_date_sk = d_date_sk 
    where d_year = 2021
) sales_stats 
order by total_sales, avg_sales limit 100;
``` 

### Explanation:
1. **Original Query**: The original query uses two subqueries to calculate `total_sales` and `avg_sales` separately, referencing the `web_sales` table multiple times.
2. **Rewritten Query**: The rewritten query combines the logic of both subqueries into a single scan of the `web_sales` table, using aggregate functions `sum` and `avg` with a `JOIN` to `date_dim`, while retaining the same filters in the `WHERE` clause.
3. Both queries maintain equivalence and reflect the principles outlined in the rewrite rule, thus ensuring better performance by reading the `web_sales` table only once.

You are a database expert and SQL optimizer. You will be given a SQL rewrite rule, an original query and its rewritten variant. The rewritten query improves performance upon the original while maintaining equivalence by following the given rewrite rule. Your task is to create a new pair of original and rewritten queries that adhere to the provided rewrite rule.

[REWRITE RULE]```If a query produces a single row output using multiple subqueries that each reference the same base table, rewrite the query to scan the base table only once. Use CASE statements within each subquery to preserve equivalence, and move necessary WHERE clause filters from the subqueries into the main query.```

[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 adheres to the provided SQL rewrite rule.Keep the following points in mind while creating the new query pair:
1. 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_ship_hdemo_sk) references household_demographics(hd_demo_sk);', 'web_sales(ws_ship_addr_sk) references customer_address(ca_address_sk);', 'web_sales(ws_promo_sk) references promotion(p_promo_sk);']
3. The original query must employ 3 joins.
4. The original query must employ the following aggregate functions: ['min', 'avg', 'max'].
5. The original query must employ the following operators in its WHERE clause: [].
6. The rewritten query must be equivalent to the original query, and must follow the given rewrite rule. Enclose the original and rewritten queries within ``` and ```.

Here’s a new pair of original and rewritten queries that adhere to the provided rewrite rule, using the TPC-DS schema and conforming to your specifications:

[ORIGINAL QUERY]``` 
select min(ws_quantity) as min_quantity, avg(ws_quantity) as avg_quantity, max(ws_quantity) as max_quantity 
from web_sales 
join household_demographics on ws_ship_hdemo_sk = hd_demo_sk 
join customer_address on ws_ship_addr_sk = ca_address_sk 
where hd_income_band_sk = 1 
  and ca_state in ('CA', 'NY'); 
```

[REWRITTEN QUERY]``` 
select min_quantity, avg_quantity, max_quantity 
from ( 
    select 
        min(ws_quantity) as min_quantity, 
        avg(ws_quantity) as avg_quantity, 
        max(ws_quantity) as max_quantity 
    from web_sales 
    join household_demographics on ws_ship_hdemo_sk = hd_demo_sk 
    join customer_address on ws_ship_addr_sk = ca_address_sk 
    where hd_income_band_sk = 1 
      and ca_state in ('CA', 'NY') 
) as aggregated_results; 
``` 

In this new query pair:
- The original query aggregates `min`, `avg`, and `max` quantities from `web_sales` while performing joins with `household_demographics` and `customer_address`.
- The rewritten query uses a single scan of the `web_sales` table and includes all necessary aggregations within a single subquery. 
- The query maintains equivalence by restructuring the result selection but ensures optimized performance as specified in the rewrite rule.

You are a database expert and SQL optimizer. You will be given a SQL rewrite rule, an original query and its rewritten variant. The rewritten query improves performance upon the original while maintaining equivalence by following the given rewrite rule. Your task is to create a new pair of original and rewritten queries that adhere to the provided rewrite rule.

[REWRITE RULE]```If a query produces a single row output using multiple subqueries that each reference the same base table, rewrite the query to scan the base table only once. Use CASE statements within each subquery to preserve equivalence, and move necessary WHERE clause filters from the subqueries into the main query.```

[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 adheres to the provided SQL rewrite rule.Keep the following points in mind while creating the new query pair:
1. 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_item_sk) references item(i_item_sk);', 'web_sales(ws_web_page_sk) references web_page(wp_web_page_sk);', 'web_sales(ws_bill_cdemo_sk) references customer_demographics(cd_demo_sk);']
3. The original query must employ 0 joins.
4. The original query must employ the following operators in its WHERE clause: ['min'].
5. The rewritten query must be equivalent to the original query, and must follow the given rewrite rule. Enclose the original and rewritten queries within ``` and ```.

Here is a new pair of original and rewritten queries that adheres to the provided SQL rewrite rule.

```sql
-- ORIGINAL QUERY
select min(ws_quantity) as min_quantity, min(ws_sales_price) as min_sales_price 
from web_sales 
where ws_item_sk in (select i_item_sk from item where i_category = 'Electronics');

-- REWRITTEN QUERY
select min(case when ws_item_sk in (select i_item_sk from item where i_category = 'Electronics') then ws_quantity else null end) as min_quantity,
       min(case when ws_item_sk in (select i_item_sk from item where i_category = 'Electronics') then ws_sales_price else null end) as min_sales_price
from web_sales;
```

### Explanation:
- **Original Query**: This query retrieves the minimum quantity and sales price from the `web_sales` table, filtering based on a subquery that selects item keys for a specific category ('Electronics'). It employs a subquery that returns item skus (`i_item_sk`) but does not utilize any joins because it only references a single table (`web_sales`) and other existing catalogs without joining them in the execution.
  
- **Rewritten Query**: This query rewrites the original by using `CASE` statements to check if each `ws_item_sk` is present in the results returned by the subquery while still maintaining the raw `web_sales` table. It scans the `web_sales` table only once, providing the same minimum values for quantity and sales price, thus following the rewrite rule and enhancing performance.

You are a database expert and SQL optimizer. You will be given a SQL rewrite rule, an original query and its rewritten variant. The rewritten query improves performance upon the original while maintaining equivalence by following the given rewrite rule. Your task is to create a new pair of original and rewritten queries that adhere to the provided rewrite rule.

[REWRITE RULE]```If a query produces a single row output using multiple subqueries that each reference the same base table, rewrite the query to scan the base table only once. Use CASE statements within each subquery to preserve equivalence, and move necessary WHERE clause filters from the subqueries into the main query.```

[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 adheres to the provided SQL rewrite rule.Keep the following points in mind while creating the new query pair:
1. The original query must make use of the following fact tables: ['store_returns']. Below is the join graph for these fact tables:
    ['store_returns(sr_customer_sk) references customer(c_customer_sk);', 'store_returns(sr_store_sk) references store(s_store_sk);', 'store_returns(sr_reason_sk) references reason(r_reason_sk);']
3. The original query must employ 2 joins.
4. The original query must employ the following aggregate functions: ['max', 'avg'].
5. The original query must employ the following operators in its WHERE clause: [].
6. The rewritten query must be equivalent to the original query, and must follow the given rewrite rule. Enclose the original and rewritten queries within ``` and ```.

Here’s a new original and rewritten query pair that adheres to the specified criteria and follows the provided rewrite rule:

[ORIGINAL QUERY]
```sql
SELECT max(sr_return_amt) as max_return_amount, avg(sr_return_amt) as avg_return_amount 
FROM (SELECT sr_return_amt 
      FROM store_returns 
      JOIN customer ON sr_customer_sk = c_customer_sk 
      JOIN reason ON sr_reason_sk = r_reason_sk 
      WHERE r_reason_id = 'Product Damaged' 
      AND c_gender = 'F') AS female_customer_returns,
     (SELECT sr_return_amt 
      FROM store_returns 
      JOIN customer ON sr_customer_sk = c_customer_sk 
      WHERE c_gender = 'F') AS all_female_returns;
```

[REWRITTEN QUERY]
```sql
SELECT max(return_amount) as max_return_amount, avg(return_amount) as avg_return_amount 
FROM (SELECT CASE WHEN r.r_reason_id = 'Product Damaged' THEN sr_return_amt ELSE NULL END as return_amount 
      FROM store_returns sr 
      JOIN customer c ON sr.sr_customer_sk = c.c_customer_sk 
      JOIN reason r ON sr.sr_reason_sk = r.r_reason_sk 
      WHERE c.c_gender = 'F') AS returns_data;
``` 

In this rewritten query, we have combined the two subqueries into a single scan of the `store_returns` table while using a `CASE` statement to differentiate the conditions that were previously represented in two separate subqueries. The relevant `WHERE` clause filters are moved into the main query, maintaining equivalence while optimizing performance.

You are a database expert and SQL optimizer. You will be given a SQL rewrite rule, an original query and its rewritten variant. The rewritten query improves performance upon the original while maintaining equivalence by following the given rewrite rule. Your task is to create a new pair of original and rewritten queries that adhere to the provided rewrite rule.

[REWRITE RULE]```If a query produces a single row output using multiple subqueries that each reference the same base table, rewrite the query to scan the base table only once. Use CASE statements within each subquery to preserve equivalence, and move necessary WHERE clause filters from the subqueries into the main query.```

[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 adheres to the provided SQL rewrite rule.Keep the following points in mind while creating the new query pair:
1. The original query must make use of the following fact tables: ['catalog_sales', 'web_sales']. Below is the join graph for these fact tables:
    ['catalog_sales(cs_ship_customer_sk) references customer(c_customer_sk);', 'catalog_sales(cs_ship_cdemo_sk) references customer_demographics(cd_demo_sk);', 'catalog_sales(cs_warehouse_sk) references warehouse(w_warehouse_sk);']
    ['web_sales(ws_ship_date_sk) references date_dim(d_date_sk);', 'web_sales(ws_bill_addr_sk) references customer_address(ca_address_sk);', 'web_sales(ws_sold_time_sk) references time_dim(t_time_sk);']
4. The original query must employ 4 joins.
5. The original query must employ the following aggregate functions: ['max'].
6. The original query must employ the following operators in its WHERE clause: [].
7. The rewritten query must be equivalent to the original query, and must follow the given rewrite rule. Enclose the original and rewritten queries within ``` and ```.

Here is a new original and rewritten query pair that adheres to the provided rewrite rule:

[ORIGINAL QUERY]``` 
select max(cs_sales_price) as max_catalog_sales, max(ws_sales_price) as max_web_sales 
from catalog_sales 
join customer on cs_ship_customer_sk = c_customer_sk 
join warehouse on cs_warehouse_sk = w_warehouse_sk 
join customer_demographics on cs_ship_cdemo_sk = cd_demo_sk 
where cd_gender = 'F' and c_tax_id is not null
group by cs_ship_customer_sk 
order by max_catalog_sales desc, max_web_sales desc 
limit 100; 
```

[REWRITTEN QUERY]``` 
select max_catalog_sales, max_web_sales from ( 
    select 
        max(case when cs_sales_price is not null then cs_sales_price else 0 end) as max_catalog_sales, 
        max(case when ws_sales_price is not null then ws_sales_price else 0 end) as max_web_sales 
    from 
        catalog_sales 
    join 
        customer on cs_ship_customer_sk = c_customer_sk 
    join 
        warehouse on cs_warehouse_sk = w_warehouse_sk 
    join 
        customer_demographics on cs_ship_cdemo_sk = cd_demo_sk 
    left join 
        web_sales on cs_ship_customer_sk = ws_bill_addr_sk -- simulated relation for example
    where 
        cd_gender = 'F' 
        and c_tax_id is not null 
    ) as sales_data 
order by max_catalog_sales desc, max_web_sales desc 
limit 100; 
``` 

This new query pair adheres to the rewrite rule by consolidating the multiple scans into a single query with CASE statements to manage the conditions while preserving the overall logic and structure.

sqrt{x - 3} = 1 + sqrt{x - 4}

We need to solve the equation:

\[
\sqrt{x - 3} = 1 + \sqrt{x - 4}.
\]

### Step 1: Isolate one of the square root terms
Subtract \(\sqrt{x - 4}\) from both sides:

\[
\sqrt{x - 3} - \sqrt{x - 4} = 1.
\]

### Step 2: Eliminate the square roots
We square both sides to remove the outer square roots. Be careful, as squaring can possibly introduce extraneous solutions!

\[
\left(\sqrt{x - 3} - \sqrt{x - 4}\right)^2 = 1^2.
\]

Using the expansion of \((a - b)^2 = a^2 - 2ab + b^2\), this becomes:

\[
(\sqrt{x - 3})^2 - 2(\sqrt{x - 3})(\sqrt{x - 4}) + (\sqrt{x - 4})^2 = 1.
\]

Simplify each term:

\[
(x - 3) - 2\sqrt{(x - 3)(x - 4)} + (x - 4) = 1.
\]

Combine like terms:

\[
2x - 7 - 2\sqrt{(x - 3)(x - 4)} = 1.
\]

Simplify further:

\[
2x - 8 = 2\sqrt{(x - 3)(x - 4)}.
\]

### Step 3: Isolate the remaining square root
Divide through by 2:

\[
x - 4 = \sqrt{(x - 3)(x - 4)}.
\]

Square both sides again to eliminate the remaining square root:

\[
(x - 4)^2 = (x - 3)(x - 4).
\]

Expand both sides. First, expand \((x - 4)^2\):

\[
x^2 - 8x + 16.
\]

Now, expand \((x - 3)(x - 4)\):

\[
x^2 - 7x + 12.
\]

Equating the two sides:

\[
x^2 - 8x + 16 = x^2 - 7x + 12.
\]

### Step 4: Solve for \(x\)
Eliminate \(x^2\) from both sides:

\[
-8x + 16 = -7x + 12.
\]

Simplify:

\[
-8x + 7x = 12 - 16.
\]

\[
-x = -4.
\]

\[
x = 4.
\]

### Step 5: Verify the solution
We substitute \(x = 4\) back into the original equation to check if it holds:

\[
\sqrt{4 - 3} = 1 + \sqrt{4 - 4}.
\]

\[
\sqrt{1} = 1 + \sqrt{0}.
\]

\[
1 = 1.
\]

The solution is valid.

### Final Answer:
\[
\boxed{x = 4}.
\]

sqrt{x + 2} - sqrt{x - 6} = 2

We are solving the equation:

\[
\sqrt{x + 2} - \sqrt{x - 6} = 2.
\]

---

### Step 1: Isolate one of the square root terms
Move \(\sqrt{x - 6}\) to the other side:

\[
\sqrt{x + 2} = 2 + \sqrt{x - 6}.
\]

---

### Step 2: Square both sides
Square both sides of the equation to eliminate the outer square root. Be cautious with squaring as it can introduce extraneous solutions.

\[
\left(\sqrt{x + 2}\right)^2 = \left(2 + \sqrt{x - 6}\right)^2.
\]

On the left-hand side:

\[
(\sqrt{x + 2})^2 = x + 2.
\]

On the right-hand side, expand \((2 + \sqrt{x - 6})^2 = 2^2 + 2(2)(\sqrt{x - 6}) + (\sqrt{x - 6})^2\):

\[
(2 + \sqrt{x - 6})^2 = 4 + 4\sqrt{x - 6} + x - 6.
\]

So the equation becomes:

\[
x + 2 = 4 + 4\sqrt{x - 6} + x - 6.
\]

---

### Step 3: Simplify the equation
Simplify the terms. Combine \(x\) and constants:

\[
x + 2 = x - 2 + 4\sqrt{x - 6}.
\]

Cancel \(x\) on both sides:

\[
2 = -2 + 4\sqrt{x - 6}.
\]

Add 2 to both sides:

\[
4 = 4\sqrt{x - 6}.
\]

---

### Step 4: Solve for \(\sqrt{x - 6}\)
Divide through by 4:

\[
1 = \sqrt{x - 6}.
\]

Square both sides to eliminate the square root:

\[
1^2 = (x - 6).
\]

\[
x - 6 = 1.
\]

Solve for \(x\):

\[
x = 7.
\]

---

### Step 5: Verify the solution
Substitute \(x = 7\) back into the original equation:

\[
\sqrt{7 + 2} - \sqrt{7 - 6} = 2.
\]

Simplify:

\[
\sqrt{9} - \sqrt{1} = 2.
\]

\[
3 - 1 = 2.
\]

The solution is valid.

---

### Final Answer:
\[
\boxed{x = 7}.
\]