| ==== |
| ---- QUERY: TPCDS-Q4 |
| with year_total as ( |
| select c_customer_id customer_id, |
| c_first_name customer_first_name, |
| c_last_name customer_last_name, |
| c_preferred_cust_flag customer_preferred_cust_flag, |
| c_birth_country customer_birth_country, |
| c_login customer_login, |
| c_email_address customer_email_address, |
| d_year dyear, |
| sum(((ss_ext_list_price-ss_ext_wholesale_cost-ss_ext_discount_amt)+ss_ext_sales_price)/2) year_total, |
| 's' sale_type |
| from customer, |
| store_sales, |
| date_dim |
| where c_customer_sk = ss_customer_sk |
| and ss_sold_date_sk = d_date_sk |
| group by c_customer_id, |
| c_first_name, |
| c_last_name, |
| c_preferred_cust_flag, |
| c_birth_country, |
| c_login, |
| c_email_address, |
| d_year |
| union all |
| select c_customer_id customer_id, |
| c_first_name customer_first_name, |
| c_last_name customer_last_name, |
| c_preferred_cust_flag customer_preferred_cust_flag, |
| c_birth_country customer_birth_country, |
| c_login customer_login, |
| c_email_address customer_email_address, |
| d_year dyear, |
| sum((((cs_ext_list_price-cs_ext_wholesale_cost-cs_ext_discount_amt)+cs_ext_sales_price)/2) ) year_total, |
| 'c' sale_type |
| from customer, |
| catalog_sales, |
| date_dim |
| where c_customer_sk = cs_bill_customer_sk |
| and cs_sold_date_sk = d_date_sk |
| group by c_customer_id, |
| c_first_name, |
| c_last_name, |
| c_preferred_cust_flag, |
| c_birth_country, |
| c_login, |
| c_email_address, |
| d_year |
| union all |
| select c_customer_id customer_id, |
| c_first_name customer_first_name, |
| c_last_name customer_last_name, |
| c_preferred_cust_flag customer_preferred_cust_flag, |
| c_birth_country customer_birth_country, |
| c_login customer_login, |
| c_email_address customer_email_address, |
| d_year dyear, |
| sum((((ws_ext_list_price-ws_ext_wholesale_cost-ws_ext_discount_amt)+ws_ext_sales_price)/2) ) year_total, |
| 'w' sale_type |
| from customer, |
| web_sales, |
| date_dim |
| where c_customer_sk = ws_bill_customer_sk |
| and ws_sold_date_sk = d_date_sk |
| group by c_customer_id, |
| c_first_name, |
| c_last_name, |
| c_preferred_cust_flag, |
| c_birth_country, |
| c_login, |
| c_email_address, |
| d_year |
| ) |
| select t_s_secyear.customer_id, |
| t_s_secyear.customer_first_name, |
| t_s_secyear.customer_last_name, |
| t_s_secyear.customer_preferred_cust_flag |
| from year_total t_s_firstyear, |
| year_total t_s_secyear, |
| year_total t_c_firstyear, |
| year_total t_c_secyear, |
| year_total t_w_firstyear, |
| year_total t_w_secyear |
| where t_s_secyear.customer_id = t_s_firstyear.customer_id |
| and t_s_firstyear.customer_id = t_c_secyear.customer_id |
| and t_s_firstyear.customer_id = t_c_firstyear.customer_id |
| and t_s_firstyear.customer_id = t_w_firstyear.customer_id |
| and t_s_firstyear.customer_id = t_w_secyear.customer_id |
| and t_s_firstyear.sale_type = 's' |
| and t_c_firstyear.sale_type = 'c' |
| and t_w_firstyear.sale_type = 'w' |
| and t_s_secyear.sale_type = 's' |
| and t_c_secyear.sale_type = 'c' |
| and t_w_secyear.sale_type = 'w' |
| and t_s_firstyear.dyear = 2001 |
| and t_s_secyear.dyear = 2001+1 |
| and t_c_firstyear.dyear = 2001 |
| and t_c_secyear.dyear = 2001+1 |
| and t_w_firstyear.dyear = 2001 |
| and t_w_secyear.dyear = 2001+1 |
| and t_s_firstyear.year_total > 0 |
| and t_c_firstyear.year_total > 0 |
| and t_w_firstyear.year_total > 0 |
| and case when t_c_firstyear.year_total > 0 then t_c_secyear.year_total / t_c_firstyear.year_total else null end |
| > case when t_s_firstyear.year_total > 0 then t_s_secyear.year_total / t_s_firstyear.year_total else null end |
| and case when t_c_firstyear.year_total > 0 then t_c_secyear.year_total / t_c_firstyear.year_total else null end |
| > case when t_w_firstyear.year_total > 0 then t_w_secyear.year_total / t_w_firstyear.year_total else null end |
| order by t_s_secyear.customer_id, |
| t_s_secyear.customer_first_name, |
| t_s_secyear.customer_last_name, |
| t_s_secyear.customer_email_address |
| limit 100; |
| ---- RESULTS |
| 'AAAAAAAABIJBAAAA','James','White','N' |
| 'AAAAAAAADIIOAAAA','David','Carroll','Y' |
| 'AAAAAAAAIJCIBAAA','Thomas','Oneal','Y' |
| 'AAAAAAAAKJBLAAAA','Kerry','Davis','Y' |
| 'AAAAAAAAMFPFBAAA','Jamie','Woods','Y' |
| 'AAAAAAAANJAMAAAA','Thaddeus','Griffin','N' |
| 'AAAAAAAANJOLAAAA','Debra','Underwood','Y' |
| 'AAAAAAAAPEHEBAAA','Edith','Molina','Y' |
| ---- TYPES |
| STRING,STRING,STRING,STRING |
| ==== |