| ==== |
| ---- QUERY: TPCDS-Q38-REWRITE |
| -- This is an unofficial variant that is rewritten to use EXISTS subqueries instead of |
| -- INTERSECT |
| select count(*) from ( |
| select distinct c_last_name, c_first_name, d_date |
| from store_sales, date_dim dd1, customer c1 |
| where store_sales.ss_sold_date_sk = dd1.d_date_sk |
| and store_sales.ss_customer_sk = c1.c_customer_sk |
| and d_month_seq between 1189 and 1189 + 11 |
| and exists ( |
| select distinct c_last_name, c_first_name, d_date |
| from catalog_sales, date_dim dd2, customer c2 |
| where catalog_sales.cs_sold_date_sk = dd2.d_date_sk |
| and catalog_sales.cs_bill_customer_sk = c2.c_customer_sk |
| and d_month_seq between 1189 and 1189 + 11 |
| and c1.c_last_name <=> c2.c_last_name and c1.c_first_name <=> c2.c_first_name and dd1.d_date <=> dd2.d_date |
| ) |
| and exists ( |
| select distinct c_last_name, c_first_name, d_date |
| from web_sales, date_dim dd3, customer c3 |
| where web_sales.ws_sold_date_sk = dd3.d_date_sk |
| and web_sales.ws_bill_customer_sk = c3.c_customer_sk |
| and d_month_seq between 1189 and 1189 + 11 |
| and c1.c_last_name <=> c3.c_last_name and c1.c_first_name <=> c3.c_first_name and dd1.d_date <=> dd3.d_date |
| ) |
| ) hot_cust |
| limit 100 |
| ---- RESULTS |
| 108 |
| ---- TYPES |
| BIGINT |
| ==== |