| ==== |
| ---- QUERY: TPCDS-Q69 |
| select |
| cd_gender, |
| cd_marital_status, |
| cd_education_status, |
| count(*) cnt1, |
| cd_purchase_estimate, |
| count(*) cnt2, |
| cd_credit_rating, |
| count(*) cnt3 |
| from |
| customer c,customer_address ca,customer_demographics |
| where |
| c.c_current_addr_sk = ca.ca_address_sk and |
| ca_state in ('KY','GA','NM') 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 = 2001 and |
| d_moy between 4 and 4+2) and |
| (not exists (select * |
| from web_sales,date_dim |
| where c.c_customer_sk = ws_bill_customer_sk and |
| ws_sold_date_sk = d_date_sk and |
| d_year = 2001 and |
| d_moy between 4 and 4+2) and |
| not exists (select * |
| from catalog_sales,date_dim |
| where c.c_customer_sk = cs_ship_customer_sk and |
| cs_sold_date_sk = d_date_sk and |
| d_year = 2001 and |
| d_moy between 4 and 4+2)) |
| group by cd_gender, |
| cd_marital_status, |
| cd_education_status, |
| cd_purchase_estimate, |
| cd_credit_rating |
| order by cd_gender, |
| cd_marital_status, |
| cd_education_status, |
| cd_purchase_estimate, |
| cd_credit_rating |
| limit 100; |
| ---- RESULTS |
| 'F','D','2 yr Degree',1,2500,1,'Low Risk',1 |
| 'F','D','2 yr Degree',1,4500,1,'Good',1 |
| 'F','D','2 yr Degree',2,4500,2,'Low Risk',2 |
| 'F','D','2 yr Degree',1,9000,1,'Good',1 |
| 'F','D','2 yr Degree',1,9500,1,'Good',1 |
| 'F','D','4 yr Degree',1,1000,1,'Low Risk',1 |
| 'F','D','4 yr Degree',1,1500,1,'Low Risk',1 |
| 'F','D','4 yr Degree',1,1500,1,'Unknown',1 |
| 'F','D','4 yr Degree',1,2000,1,'Good',1 |
| 'F','D','4 yr Degree',1,2500,1,'Unknown',1 |
| 'F','D','4 yr Degree',1,4000,1,'Unknown',1 |
| 'F','D','4 yr Degree',1,5500,1,'Good',1 |
| 'F','D','4 yr Degree',1,7000,1,'Good',1 |
| 'F','D','4 yr Degree',1,7000,1,'High Risk',1 |
| 'F','D','4 yr Degree',1,8000,1,'High Risk',1 |
| 'F','D','4 yr Degree',1,8500,1,'Unknown',1 |
| 'F','D','Advanced Degree',1,1000,1,'Good',1 |
| 'F','D','Advanced Degree',1,3500,1,'Good',1 |
| 'F','D','Advanced Degree',1,3500,1,'High Risk',1 |
| 'F','D','Advanced Degree',1,4000,1,'High Risk',1 |
| 'F','D','Advanced Degree',1,5000,1,'High Risk',1 |
| 'F','D','Advanced Degree',1,5500,1,'Good',1 |
| 'F','D','Advanced Degree',1,5500,1,'Low Risk',1 |
| 'F','D','Advanced Degree',1,6500,1,'Low Risk',1 |
| 'F','D','Advanced Degree',1,7000,1,'High Risk',1 |
| 'F','D','Advanced Degree',1,8000,1,'Low Risk',1 |
| 'F','D','Advanced Degree',1,8500,1,'High Risk',1 |
| 'F','D','Advanced Degree',1,9500,1,'Low Risk',1 |
| 'F','D','Advanced Degree',1,9500,1,'Unknown',1 |
| 'F','D','Advanced Degree',1,10000,1,'Low Risk',1 |
| 'F','D','College',1,1500,1,'Low Risk',1 |
| 'F','D','College',1,2500,1,'Good',1 |
| 'F','D','College',1,4000,1,'High Risk',1 |
| 'F','D','College',1,6000,1,'Good',1 |
| 'F','D','College',1,6000,1,'High Risk',1 |
| 'F','D','College',1,9000,1,'High Risk',1 |
| 'F','D','Primary',1,3000,1,'High Risk',1 |
| 'F','D','Primary',1,4000,1,'Low Risk',1 |
| 'F','D','Primary',1,4000,1,'Unknown',1 |
| 'F','D','Primary',1,4500,1,'Unknown',1 |
| 'F','D','Primary',1,5000,1,'High Risk',1 |
| 'F','D','Primary',1,5000,1,'Unknown',1 |
| 'F','D','Primary',1,6000,1,'Good',1 |
| 'F','D','Primary',1,6000,1,'Unknown',1 |
| 'F','D','Primary',1,8500,1,'Unknown',1 |
| 'F','D','Primary',1,9000,1,'Unknown',1 |
| 'F','D','Primary',1,9500,1,'High Risk',1 |
| 'F','D','Primary',1,9500,1,'Unknown',1 |
| 'F','D','Primary',1,10000,1,'Good',1 |
| 'F','D','Primary',2,10000,2,'Low Risk',2 |
| 'F','D','Secondary',1,500,1,'Low Risk',1 |
| 'F','D','Secondary',1,1000,1,'High Risk',1 |
| 'F','D','Secondary',1,3000,1,'Good',1 |
| 'F','D','Secondary',1,3500,1,'Good',1 |
| 'F','D','Secondary',1,6000,1,'Low Risk',1 |
| 'F','D','Secondary',1,6500,1,'Unknown',1 |
| 'F','D','Secondary',1,8500,1,'High Risk',1 |
| 'F','D','Secondary',1,10000,1,'Low Risk',1 |
| 'F','D','Unknown',1,1500,1,'High Risk',1 |
| 'F','D','Unknown',1,3000,1,'Good',1 |
| 'F','D','Unknown',1,3000,1,'High Risk',1 |
| 'F','D','Unknown',1,6000,1,'Good',1 |
| 'F','D','Unknown',1,6500,1,'High Risk',1 |
| 'F','D','Unknown',2,8000,2,'Low Risk',2 |
| 'F','D','Unknown',1,10000,1,'Unknown',1 |
| 'F','M','2 yr Degree',1,2500,1,'Low Risk',1 |
| 'F','M','2 yr Degree',3,4000,3,'Unknown',3 |
| 'F','M','2 yr Degree',1,4500,1,'Low Risk',1 |
| 'F','M','2 yr Degree',1,8500,1,'High Risk',1 |
| 'F','M','4 yr Degree',1,1000,1,'Good',1 |
| 'F','M','4 yr Degree',1,1500,1,'Good',1 |
| 'F','M','4 yr Degree',1,4000,1,'Low Risk',1 |
| 'F','M','4 yr Degree',1,4000,1,'Unknown',1 |
| 'F','M','4 yr Degree',1,5000,1,'Unknown',1 |
| 'F','M','4 yr Degree',1,5500,1,'Low Risk',1 |
| 'F','M','4 yr Degree',1,6000,1,'Good',1 |
| 'F','M','4 yr Degree',1,8000,1,'High Risk',1 |
| 'F','M','Advanced Degree',1,500,1,'Low Risk',1 |
| 'F','M','Advanced Degree',1,500,1,'Unknown',1 |
| 'F','M','Advanced Degree',1,2500,1,'Good',1 |
| 'F','M','Advanced Degree',1,3500,1,'High Risk',1 |
| 'F','M','Advanced Degree',2,5500,2,'Unknown',2 |
| 'F','M','Advanced Degree',1,6500,1,'Good',1 |
| 'F','M','Advanced Degree',1,6500,1,'Unknown',1 |
| 'F','M','Advanced Degree',1,8000,1,'Low Risk',1 |
| 'F','M','Advanced Degree',1,9500,1,'Good',1 |
| 'F','M','Advanced Degree',1,10000,1,'Low Risk',1 |
| 'F','M','College',1,1000,1,'Unknown',1 |
| 'F','M','College',1,3000,1,'Good',1 |
| 'F','M','College',2,5000,2,'High Risk',2 |
| 'F','M','College',1,6000,1,'Low Risk',1 |
| 'F','M','College',1,7000,1,'High Risk',1 |
| 'F','M','College',1,8000,1,'Good',1 |
| 'F','M','College',1,9000,1,'Good',1 |
| 'F','M','College',2,9000,2,'High Risk',2 |
| 'F','M','College',1,9500,1,'High Risk',1 |
| 'F','M','College',1,10000,1,'High Risk',1 |
| 'F','M','Primary',1,1000,1,'Low Risk',1 |
| 'F','M','Primary',1,1500,1,'High Risk',1 |
| 'F','M','Primary',1,7000,1,'Good',1 |
| ---- TYPES |
| STRING, STRING, STRING, BIGINT, INT, BIGINT, STRING, BIGINT |
| ==== |
