| ==== |
| ---- QUERY: TPCDS-Q11 |
| 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_discount_amt) 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(ws_ext_list_price-ws_ext_discount_amt) 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_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_w_secyear.customer_id |
| and t_s_firstyear.customer_id = t_w_firstyear.customer_id |
| and t_s_firstyear.sale_type = 's' |
| and t_w_firstyear.sale_type = 'w' |
| and t_s_secyear.sale_type = 's' |
| and t_w_secyear.sale_type = 'w' |
| and t_s_firstyear.dyear = 2001 |
| and t_s_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_w_firstyear.year_total > 0 |
| and case when t_w_firstyear.year_total > 0 then t_w_secyear.year_total / t_w_firstyear.year_total else 0.0 end |
| > case when t_s_firstyear.year_total > 0 then t_s_secyear.year_total / t_s_firstyear.year_total else 0.0 end |
| order by t_s_secyear.customer_id |
| ,t_s_secyear.customer_first_name |
| ,t_s_secyear.customer_last_name |
| ,t_s_secyear.customer_preferred_cust_flag |
| limit 100; |
| ---- RESULTS |
| 'AAAAAAAAAFGBBAAA','Howard','Major','Y' |
| 'AAAAAAAAAMGDAAAA','Kenneth','Harlan','Y' |
| 'AAAAAAAAAOPFBAAA','Jerry','Fields','Y' |
| 'AAAAAAAABLEIBAAA','Paula','Wakefield','Y' |
| 'AAAAAAAABNBBAAAA','Irma','Smith','Y' |
| 'AAAAAAAACADPAAAA','Cristobal','Thomas','Y' |
| 'AAAAAAAACFENAAAA','Christopher','Dawson','NULL' |
| 'AAAAAAAACIJMAAAA','Elizabeth','Thomas','Y' |
| 'AAAAAAAACJDIAAAA','James','Kerr','N' |
| 'AAAAAAAACNAGBAAA','Virginia','May','N' |
| 'AAAAAAAADBEFBAAA','Bennie','Bowers','N' |
| 'AAAAAAAADCKOAAAA','Robert','Gonzalez','N' |
| 'AAAAAAAADFKABAAA','Latoya','Craft','N' |
| 'AAAAAAAADIIOAAAA','David','Carroll','Y' |
| 'AAAAAAAADIJGBAAA','Ruth','Sanders','N' |
| 'AAAAAAAADLHBBAAA','Henry','Bertrand','N' |
| 'AAAAAAAAEADJAAAA','Ruth','Carroll','N' |
| 'AAAAAAAAEJDLAAAA','Alice','Wright','N' |
| 'AAAAAAAAEKFPAAAA','Annika','Chin','N' |
| 'AAAAAAAAEKJLAAAA','Aisha','Carlson','Y' |
| 'AAAAAAAAEPOGAAAA','Felisha','Mendes','Y' |
| 'AAAAAAAAFACEAAAA','Priscilla','Miller','N' |
| 'AAAAAAAAFBAHAAAA','Michael','Williams','N' |
| 'AAAAAAAAFGIGAAAA','Eduardo','Miller','Y' |
| 'AAAAAAAAFGPGAAAA','Albert','Wadsworth','Y' |
| 'AAAAAAAAFMHIAAAA','Emilio','Darling','Y' |
| 'AAAAAAAAFOGIAAAA','Michelle','Greene','N' |
| 'AAAAAAAAFOJAAAAA','Don','Castillo','Y' |
| 'AAAAAAAAGEHIAAAA','Tyler','Miller','N' |
| 'AAAAAAAAGHPBBAAA','Nick','Mendez','Y' |
| 'AAAAAAAAGNDAAAAA','Terry','Mcdowell','N' |
| 'AAAAAAAAHGOABAAA','Sonia','White','N' |
| 'AAAAAAAAHHCABAAA','William','Stewart','Y' |
| 'AAAAAAAAHJLAAAAA','Audrey','Beltran','Y' |
| 'AAAAAAAAHMJNAAAA','Ryan','Baptiste','Y' |
| 'AAAAAAAAHMOIAAAA','Grace','Henderson','N' |
| 'AAAAAAAAIADEBAAA','Diane','Aldridge','N' |
| 'AAAAAAAAIBAEBAAA','Sandra','Wilson','N' |
| 'AAAAAAAAIBFCBAAA','Ruth','Grantham','N' |
| 'AAAAAAAAIBHHAAAA','Jennifer','Ballard','Y' |
| 'AAAAAAAAICHFAAAA','Linda','Mccoy','N' |
| 'AAAAAAAAIDKFAAAA','Michael','Mack','N' |
| 'AAAAAAAAIJEMAAAA','Charlie','Cummings','Y' |
| 'AAAAAAAAIMHBAAAA','Kathy','Knowles','N' |
| 'AAAAAAAAIMHHBAAA','Lillian','Davidson','Y' |
| 'AAAAAAAAJDBLAAAA','Melvin','Taylor','Y' |
| 'AAAAAAAAJEKFBAAA','Norma','Burkholder','N' |
| 'AAAAAAAAJGMMAAAA','Richard','Larson','Y' |
| 'AAAAAAAAJIALAAAA','Santos','Gutierrez','N' |
| 'AAAAAAAAJKBNAAAA','Julie','Kern','N' |
| 'AAAAAAAAJMHLAAAA','Wanda','Ryan','Y' |
| 'AAAAAAAAJONHBAAA','Warren','Orozco','N' |
| 'AAAAAAAAJPINAAAA','Rose','Waite','Y' |
| 'AAAAAAAAKAECAAAA','Milton','Mackey','N' |
| 'AAAAAAAAKAPPAAAA','Karen','Parker','Y' |
| 'AAAAAAAAKJBKAAAA','Georgia','Scott','N' |
| 'AAAAAAAAKJBLAAAA','Kerry','Davis','Y' |
| 'AAAAAAAAKKGEAAAA','Katie','Dunbar','N' |
| 'AAAAAAAAKLHHBAAA','Manuel','Castaneda','N' |
| 'AAAAAAAAKNAKAAAA','Gladys','Banks','N' |
| 'AAAAAAAALFKKAAAA','Ignacio','Miller','Y' |
| 'AAAAAAAALHMCAAAA','Brooke','Nelson','Y' |
| 'AAAAAAAALIOPAAAA','Derek','Allen','Y' |
| 'AAAAAAAALJNCBAAA','George','Gamez','Y' |
| 'AAAAAAAAMDCAAAAA','Louann','Hamel','Y' |
| 'AAAAAAAAMFFLAAAA','Margret','Gray','Y' |
| 'AAAAAAAAMMOBBAAA','Margaret','Smith','N' |
| 'AAAAAAAANFBDBAAA','Vernice','Fernandez','Y' |
| 'AAAAAAAANGDBBAAA','Carlos','Jewell','N' |
| 'AAAAAAAANIPLAAAA','Eric','Lawrence','Y' |
| 'AAAAAAAANJAGAAAA','Allen','Hood','Y' |
| 'AAAAAAAANJHCBAAA','Christopher','Schreiber','N' |
| 'AAAAAAAANJOLAAAA','Debra','Underwood','Y' |
| 'AAAAAAAAOBADBAAA','Elizabeth','Burnham','N' |
| 'AAAAAAAAOCAJAAAA','Jenna','Staton','N' |
| 'AAAAAAAAOCLBBAAA','NULL','NULL','NULL' |
| 'AAAAAAAAODMMAAAA','Gayla','Cline','N' |
| 'AAAAAAAAOFLCAAAA','James','Taylor','N' |
| 'AAAAAAAAOPDLAAAA','Ann','Pence','N' |
| 'AAAAAAAAPDFBAAAA','Terrance','Banks','Y' |
| 'AAAAAAAAPEHEBAAA','Edith','Molina','Y' |
| 'AAAAAAAAPFCLAAAA','Felicia','Neville','N' |
| 'AAAAAAAAPICEAAAA','Jennifer','Cortez','Y' |
| 'AAAAAAAAPJENAAAA','Ashley','Norton','Y' |
| 'AAAAAAAAPKBCBAAA','Andrea','White','N' |
| 'AAAAAAAAPKIKAAAA','Wendy','Horvath','Y' |
| 'AAAAAAAAPMMBBAAA','Paul','Jordan','N' |
| 'AAAAAAAAPPIBBAAA','Candice','Lee','Y' |
| ---- TYPES |
| STRING, STRING, STRING, STRING |
| ==== |
