| ==== |
| ---- QUERY: TPCDS-Q10A |
| # Official variant for lack of multiple subqueries in OR predicate. |
| select |
| cd_gender, |
| cd_marital_status, |
| cd_education_status, |
| count(*) cnt1, |
| cd_purchase_estimate, |
| count(*) cnt2, |
| cd_credit_rating, |
| count(*) cnt3, |
| cd_dep_count, |
| count(*) cnt4, |
| cd_dep_employed_count, |
| count(*) cnt5, |
| cd_dep_college_count, |
| count(*) cnt6 |
| from |
| customer c,customer_address ca,customer_demographics |
| where |
| c.c_current_addr_sk = ca.ca_address_sk and |
| ca_county in ('Rush County','Toole County','Jefferson County','Dona Ana County','La Porte County') and |
| cd_demo_sk = c.c_current_cdemo_sk and |
| exists (select * |
| from store_sales,date_dim |
| where c.c_customer_sk = ss_customer_sk and |
| ss_sold_date_sk = d_date_sk and |
| d_year = 2002 and |
| d_moy between 1 and 1+3) and |
| exists (select * |
| from ( |
| select ws_bill_customer_sk as customer_sk, d_year,d_moy |
| from web_sales, date_dim |
| where ws_sold_date_sk = d_date_sk and |
| d_year = 2002 and |
| d_moy between 1 and 1+3 |
| union all |
| select cs_ship_customer_sk as customer_sk, d_year, d_moy |
| from catalog_sales, date_dim |
| where cs_sold_date_sk = d_date_sk and |
| d_year = 2002 and |
| d_moy between 1 and 1+3 |
| ) x |
| where c.c_customer_sk = customer_sk) |
| group by cd_gender, |
| cd_marital_status, |
| cd_education_status, |
| cd_purchase_estimate, |
| cd_credit_rating, |
| cd_dep_count, |
| cd_dep_employed_count, |
| cd_dep_college_count |
| order by cd_gender, |
| cd_marital_status, |
| cd_education_status, |
| cd_purchase_estimate, |
| cd_credit_rating, |
| cd_dep_count, |
| cd_dep_employed_count, |
| cd_dep_college_count |
| limit 100; |
| ---- RESULTS |
| 'F','D','Advanced Degree',1,3000,1,'High Risk',1,2,1,4,1,5,1 |
| 'F','D','Unknown',1,1500,1,'Good',1,6,1,5,1,4,1 |
| 'M','D','College',1,8500,1,'Low Risk',1,3,1,0,1,1,1 |
| 'M','D','Primary',1,7000,1,'Unknown',1,2,1,1,1,1,1 |
| 'M','W','Unknown',1,4500,1,'Good',1,5,1,0,1,1,1 |
| ---- TYPES |
| STRING, STRING, STRING, BIGINT, INT, BIGINT, STRING, BIGINT, INT, BIGINT, INT, BIGINT, INT, BIGINT |
| ==== |