0001
0002 with
0003 v1 as (
0004 select
0005 ws_bill_customer_sk as customer_sk
0006 from web_sales,
0007 date_dim
0008 where ws_sold_date_sk = d_date_sk
0009 and d_year = 2002
0010 and d_moy between 4 and 4+3
0011 union all
0012 select
0013 cs_ship_customer_sk as customer_sk
0014 from catalog_sales,
0015 date_dim
0016 where cs_sold_date_sk = d_date_sk
0017 and d_year = 2002
0018 and d_moy between 4 and 4+3
0019 ),
0020 v2 as (
0021 select
0022 ss_customer_sk as customer_sk
0023 from store_sales,
0024 date_dim
0025 where ss_sold_date_sk = d_date_sk
0026 and d_year = 2002
0027 and d_moy between 4 and 4+3
0028 )
0029 select
0030 cd_gender,
0031 cd_marital_status,
0032 cd_education_status,
0033 count(*) cnt1,
0034 cd_purchase_estimate,
0035 count(*) cnt2,
0036 cd_credit_rating,
0037 count(*) cnt3,
0038 cd_dep_count,
0039 count(*) cnt4,
0040 cd_dep_employed_count,
0041 count(*) cnt5,
0042 cd_dep_college_count,
0043 count(*) cnt6
0044 from customer c
0045 join customer_address ca on (c.c_current_addr_sk = ca.ca_address_sk)
0046 join customer_demographics on (cd_demo_sk = c.c_current_cdemo_sk)
0047 left semi join v1 on (v1.customer_sk = c.c_customer_sk)
0048 left semi join v2 on (v2.customer_sk = c.c_customer_sk)
0049 where
0050 ca_county in ('Walker County','Richland County','Gaines County','Douglas County','Dona Ana County')
0051 group by
0052 cd_gender,
0053 cd_marital_status,
0054 cd_education_status,
0055 cd_purchase_estimate,
0056 cd_credit_rating,
0057 cd_dep_count,
0058 cd_dep_employed_count,
0059 cd_dep_college_count
0060 order by
0061 cd_gender,
0062 cd_marital_status,
0063 cd_education_status,
0064 cd_purchase_estimate,
0065 cd_credit_rating,
0066 cd_dep_count,
0067 cd_dep_employed_count,
0068 cd_dep_college_count
0069 limit 100
0070
