| ==== |
| ---- QUERY: TPCDS-Q74 |
| with year_total as ( |
| select c_customer_id customer_id |
| ,c_first_name customer_first_name |
| ,c_last_name customer_last_name |
| ,d_year as year |
| ,sum(ss_net_paid) 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 |
| and d_year in (2001,2001+1) |
| group by c_customer_id |
| ,c_first_name |
| ,c_last_name |
| ,d_year |
| union all |
| select c_customer_id customer_id |
| ,c_first_name customer_first_name |
| ,c_last_name customer_last_name |
| ,d_year as year |
| ,sum(ws_net_paid) 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 |
| and d_year in (2001,2001+1) |
| group by c_customer_id |
| ,c_first_name |
| ,c_last_name |
| ,d_year |
| ) |
| select |
| t_s_secyear.customer_id, t_s_secyear.customer_first_name, t_s_secyear.customer_last_name |
| 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.year = 2001 |
| and t_s_secyear.year = 2001+1 |
| and t_w_firstyear.year = 2001 |
| and t_w_secyear.year = 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 null end |
| > case when t_s_firstyear.year_total > 0 then t_s_secyear.year_total / t_s_firstyear.year_total else null end |
| order by 1,1,1 |
| limit 100; |
| ---- RESULTS |
| 'AAAAAAAAAMGDAAAA','Kenneth','Harlan' |
| 'AAAAAAAAANFAAAAA','Philip','Banks' |
| 'AAAAAAAAAOPFBAAA','Jerry','Fields' |
| 'AAAAAAAABLEIBAAA','Paula','Wakefield' |
| 'AAAAAAAABNBBAAAA','Irma','Smith' |
| 'AAAAAAAACADPAAAA','Cristobal','Thomas' |
| 'AAAAAAAACFCGBAAA','Marcus','Sanders' |
| 'AAAAAAAACFENAAAA','Christopher','Dawson' |
| 'AAAAAAAACIJMAAAA','Elizabeth','Thomas' |
| 'AAAAAAAACJDIAAAA','James','Kerr' |
| 'AAAAAAAACNAGBAAA','Virginia','May' |
| 'AAAAAAAADBEFBAAA','Bennie','Bowers' |
| 'AAAAAAAADCKOAAAA','Robert','Gonzalez' |
| 'AAAAAAAADFIEBAAA','John','Gray' |
| 'AAAAAAAADFKABAAA','Latoya','Craft' |
| 'AAAAAAAADIIOAAAA','David','Carroll' |
| 'AAAAAAAADIJGBAAA','Ruth','Sanders' |
| 'AAAAAAAADLHBBAAA','Henry','Bertrand' |
| 'AAAAAAAAEADJAAAA','Ruth','Carroll' |
| 'AAAAAAAAEJDLAAAA','Alice','Wright' |
| 'AAAAAAAAEKFPAAAA','Annika','Chin' |
| 'AAAAAAAAEKJLAAAA','Aisha','Carlson' |
| 'AAAAAAAAEOAKAAAA','Molly','Benjamin' |
| 'AAAAAAAAEPOGAAAA','Felisha','Mendes' |
| 'AAAAAAAAFACEAAAA','Priscilla','Miller' |
| 'AAAAAAAAFBAHAAAA','Michael','Williams' |
| 'AAAAAAAAFGIGAAAA','Eduardo','Miller' |
| 'AAAAAAAAFGPGAAAA','Albert','Wadsworth' |
| 'AAAAAAAAFHACBAAA','NULL','NULL' |
| 'AAAAAAAAFJHFAAAA','Larissa','Roy' |
| 'AAAAAAAAFMHIAAAA','Emilio','Darling' |
| 'AAAAAAAAFOGIAAAA','Michelle','Greene' |
| 'AAAAAAAAFOJAAAAA','Don','Castillo' |
| 'AAAAAAAAGEHIAAAA','Tyler','Miller' |
| 'AAAAAAAAGFMDBAAA','Kathleen','Gibson' |
| 'AAAAAAAAGHPBBAAA','Nick','Mendez' |
| 'AAAAAAAAGNDAAAAA','Terry','Mcdowell' |
| 'AAAAAAAAHGOABAAA','Sonia','White' |
| 'AAAAAAAAHHCABAAA','William','Stewart' |
| 'AAAAAAAAHJLAAAAA','Audrey','Beltran' |
| 'AAAAAAAAHMJNAAAA','Ryan','Baptiste' |
| 'AAAAAAAAHMOIAAAA','Grace','Henderson' |
| 'AAAAAAAAHNFHAAAA','Rebecca','Wilson' |
| 'AAAAAAAAIADEBAAA','Diane','Aldridge' |
| 'AAAAAAAAIBAEBAAA','Sandra','Wilson' |
| 'AAAAAAAAIBFCBAAA','Ruth','Grantham' |
| 'AAAAAAAAIBHHAAAA','Jennifer','Ballard' |
| 'AAAAAAAAICHFAAAA','Linda','Mccoy' |
| 'AAAAAAAAIDKFAAAA','Michael','Mack' |
| 'AAAAAAAAIJEMAAAA','Charlie','Cummings' |
| 'AAAAAAAAIMHBAAAA','Kathy','Knowles' |
| 'AAAAAAAAIMHHBAAA','Lillian','Davidson' |
| 'AAAAAAAAJEKFBAAA','Norma','Burkholder' |
| 'AAAAAAAAJGMMAAAA','Richard','Larson' |
| 'AAAAAAAAJIALAAAA','Santos','Gutierrez' |
| 'AAAAAAAAJKBNAAAA','Julie','Kern' |
| 'AAAAAAAAJONHBAAA','Warren','Orozco' |
| 'AAAAAAAAKAECAAAA','Milton','Mackey' |
| 'AAAAAAAAKBCABAAA','Debra','Bell' |
| 'AAAAAAAAKJBKAAAA','Georgia','Scott' |
| 'AAAAAAAAKJBLAAAA','Kerry','Davis' |
| 'AAAAAAAAKKGEAAAA','Katie','Dunbar' |
| 'AAAAAAAAKLHHBAAA','Manuel','Castaneda' |
| 'AAAAAAAAKNAKAAAA','Gladys','Banks' |
| 'AAAAAAAAKOJJAAAA','Gracie','Mendoza' |
| 'AAAAAAAALFKKAAAA','Ignacio','Miller' |
| 'AAAAAAAALHMCAAAA','Brooke','Nelson' |
| 'AAAAAAAALIOPAAAA','Derek','Allen' |
| 'AAAAAAAALJNCBAAA','George','Gamez' |
| 'AAAAAAAAMDCAAAAA','Louann','Hamel' |
| 'AAAAAAAAMFFLAAAA','Margret','Gray' |
| 'AAAAAAAAMMOBBAAA','Margaret','Smith' |
| 'AAAAAAAANFBDBAAA','Vernice','Fernandez' |
| 'AAAAAAAANGDBBAAA','Carlos','Jewell' |
| 'AAAAAAAANIPLAAAA','Eric','Lawrence' |
| 'AAAAAAAANJAGAAAA','Allen','Hood' |
| 'AAAAAAAANJHCBAAA','Christopher','Schreiber' |
| 'AAAAAAAAOBADBAAA','Elizabeth','Burnham' |
| 'AAAAAAAAOCAJAAAA','Jenna','Staton' |
| 'AAAAAAAAOCDJAAAA','Nina','Sanchez' |
| 'AAAAAAAAOCICAAAA','Zachary','Pennington' |
| 'AAAAAAAAOCLBBAAA','NULL','NULL' |
| 'AAAAAAAAOFLCAAAA','James','Taylor' |
| 'AAAAAAAAOPDLAAAA','Ann','Pence' |
| 'AAAAAAAAPDFBAAAA','Terrance','Banks' |
| 'AAAAAAAAPEHEBAAA','Edith','Molina' |
| 'AAAAAAAAPFCLAAAA','Felicia','Neville' |
| 'AAAAAAAAPJENAAAA','Ashley','Norton' |
| 'AAAAAAAAPKBCBAAA','Andrea','White' |
| 'AAAAAAAAPKIKAAAA','Wendy','Horvath' |
| 'AAAAAAAAPMMBBAAA','Paul','Jordan' |
| 'AAAAAAAAPPIBBAAA','Candice','Lee' |
| ---- TYPES |
| STRING, STRING, STRING |
| ==== |