| ==== |
| ---- QUERY: TPCDS-Q30 |
| with customer_total_return as |
| (select wr_returning_customer_sk as ctr_customer_sk |
| ,ca_state as ctr_state, |
| sum(wr_return_amt) as ctr_total_return |
| from web_returns |
| ,date_dim |
| ,customer_address |
| where wr_returned_date_sk = d_date_sk |
| and d_year =2002 |
| and wr_returning_addr_sk = ca_address_sk |
| group by wr_returning_customer_sk |
| ,ca_state) |
| select c_customer_id,c_salutation,c_first_name,c_last_name,c_preferred_cust_flag |
| ,c_birth_day,c_birth_month,c_birth_year,c_birth_country,c_login,c_email_address |
| ,c_last_review_date,ctr_total_return |
| from customer_total_return ctr1 |
| ,customer_address |
| ,customer |
| where ctr1.ctr_total_return > (select avg(ctr_total_return)*1.2 |
| from customer_total_return ctr2 |
| where ctr1.ctr_state = ctr2.ctr_state) |
| and ca_address_sk = c_current_addr_sk |
| and ca_state = 'IL' |
| and ctr1.ctr_customer_sk = c_customer_sk |
| order by c_customer_id,c_salutation,c_first_name,c_last_name,c_preferred_cust_flag |
| ,c_birth_day,c_birth_month,c_birth_year,c_birth_country,c_login,c_email_address |
| ,c_last_review_date,ctr_total_return |
| limit 100; |
| ---- RESULTS |
| 'AAAAAAAAACBFAAAA','Dr.','Clifton','Carr','N',4,4,1950,'INDIA','NULL','Clifton.Carr@mOm0RG.com','2452385',2232.42 |
| 'AAAAAAAAADCCAAAA','Sir','Elmer','Southerland','Y',14,3,1967,'NICARAGUA','NULL','Elmer.Southerland@dG9HZHGjU8.edu','2452623',9604.53 |
| 'AAAAAAAAADFDBAAA','Dr.','Stanley','Smith','N',23,4,1928,'SPAIN','NULL','Stanley.Smith@Yxk9F0obDmkvN.org','2452381',2287.41 |
| 'AAAAAAAAAEHLAAAA','Ms.','Anita','Vance','Y',14,3,1988,'ZIMBABWE','NULL','Anita.Vance@SCZrSvm7MpKZ.org','2452362',4263.60 |
| 'AAAAAAAAAIJOAAAA','Dr.','Elise','Cook','Y',25,1,1957,'SAN MARINO','NULL','Elise.Cook@zgV4U.com','2452537',1940.00 |
| 'AAAAAAAAAJLEBAAA','Mrs.','Judy','Wells','NULL',31,8,NULL,'JERSEY','NULL','NULL','NULL',1764.00 |
| 'AAAAAAAAAMHMAAAA','Mrs.','Angel','Farrington','N',5,5,1992,'NICARAGUA','NULL','Angel.Farrington@ESvsPDBzslNpvaZ.org','2452593',3722.86 |
| 'AAAAAAAABEGGAAAA','Ms.','Linda','Bishop','N',25,5,1945,'ANDORRA','NULL','Linda.Bishop@SsgvULplErri.org','2452369',5049.28 |
| 'AAAAAAAABLPMAAAA','Sir','Peter','Hogue','N',24,4,1979,'FRENCH GUIANA','NULL','Peter.Hogue@XZhvIIKsznzvj.com','2452640',1582.00 |
| 'AAAAAAAABNBKAAAA','Dr.','Chelsea','Wellman','Y',5,2,1989,'NIGERIA','NULL','Chelsea.Wellman@RY0lIpgj89SBPI.com','2452454',4034.24 |
| 'AAAAAAAABNECAAAA','Dr.','Thomas','Hollingsworth','N',30,9,1960,'AFGHANISTAN','NULL','Thomas.Hollingsworth@GM6CNHy.org','2452581',6428.07 |
| 'AAAAAAAABNMAAAAA','Miss','Martha','Moore','N',5,7,1985,'BOUVET ISLAND','NULL','Martha.Moore@s.org','2452584',7708.40 |
| 'AAAAAAAABNMEBAAA','Dr.','Sara','Mcmahon','N',23,10,1926,'BARBADOS','NULL','Sara.Mcmahon@DGmEehYquCX.edu','2452584',1290.24 |
| 'AAAAAAAACDKGAAAA','Dr.','Martha','Mason','N',20,6,1970,'MOROCCO','NULL','Martha.Mason@F.org','2452359',3861.33 |
| 'AAAAAAAACEONAAAA','Ms.','Sandra','Lopez','Y',31,3,1979,'EGYPT','NULL','Sandra.Lopez@pJc3Or8NbSu8MX.com','2452311',4688.40 |
| 'AAAAAAAACGBPAAAA','Ms.','Florence','Cabrera','Y',30,6,1934,'GHANA','NULL','Florence.Cabrera@cm.com','2452610',1608.64 |
| 'AAAAAAAACIGIBAAA','Dr.','Charles','Lopez','Y',11,10,1979,'URUGUAY','NULL','Charles.Lopez@gOyo.com','2452424',2025.00 |
| 'AAAAAAAACIOGBAAA','Dr.','Gregorio','Lee','N',3,10,1970,'LESOTHO','NULL','Gregorio.Lee@iteZa836XrfF.edu','2452438',1590.30 |
| 'AAAAAAAACJLIAAAA','Mr.','Philip','Kinder','N',1,10,1987,'BOLIVIA','NULL','Philip.Kinder@t4B5veuzCR390MP.com','2452595',2526.44 |
| 'AAAAAAAACKDBAAAA','Miss','Kathy','Kelly','N',15,9,1988,'ANTIGUA AND BARBUDA','NULL','Kathy.Kelly@o.org','2452339',2719.92 |
| 'AAAAAAAACPLIAAAA','Mr.','John','Williams','Y',11,9,1960,'COMOROS','NULL','John.Williams@Gn.com','2452392',4542.08 |
| 'AAAAAAAACPOIAAAA','Dr.','Melvin','Phillips','Y',16,2,1987,'LESOTHO','NULL','Melvin.Phillips@MinR9.com','2452402',2647.26 |
| 'AAAAAAAADADNAAAA','Dr.','Willie','Vitale','Y',30,11,1978,'SLOVAKIA','NULL','Willie.Vitale@rUnNJoaskyn6aLA.edu','2452642',6185.28 |
| 'AAAAAAAADAGBAAAA','Ms.','Helen','Barker','N',10,5,1933,'ZAMBIA','NULL','Helen.Barker@V8ZT4ncYBuGSK.com','2452378',2791.32 |
| 'AAAAAAAADNLLAAAA','Mrs.','Mae','Jackson','N',15,8,1948,'SYRIAN ARAB REPUBLIC','NULL','Mae.Jackson@Eh2TmyLnCy.org','2452379',5097.08 |
| 'AAAAAAAADPCIAAAA','Miss','Wilma','Sanborn','N',15,6,1952,'BURUNDI','NULL','Wilma.Sanborn@2jbbN0ubazbL.org','2452333',2457.60 |
| 'AAAAAAAAEHFFBAAA','Dr.','Michelle','Reed','N',1,3,1982,'ZAMBIA','NULL','Michelle.Reed@9yNA4PLMEjB.edu','2452546',5864.34 |
| 'AAAAAAAAFABKAAAA','Dr.','Nancy','Duke','N',10,1,1961,'ERITREA','NULL','Nancy.Duke@h.edu','2452379',1607.04 |
| 'AAAAAAAAFCKHBAAA','Mrs.','Helen','Ramsay','N',6,1,1971,'LIECHTENSTEIN','NULL','Helen.Ramsay@ZC1VJrVtx7It3Iz.com','2452626',2868.48 |
| 'AAAAAAAAFGBABAAA','Mrs.','Louise','Douglas','N',12,10,1944,'ARUBA','NULL','Louise.Douglas@VzomaEVuSe.edu','2452630',9067.96 |
| 'AAAAAAAAFPAIBAAA','Dr.','Michael','Roberge','N',29,1,1959,'VIET NAM','NULL','Michael.Roberge@YTN9dUXCpJmz5.org','2452378',4120.84 |
| 'AAAAAAAAGAMHAAAA','Miss','Clementine','Darby','N',5,2,1952,'SAN MARINO','NULL','Clementine.Darby@8HfgoxcVueO.com','2452573',2829.50 |
| 'AAAAAAAAGBJKAAAA','Dr.','Amy','Baker','Y',5,6,1970,'SAUDI ARABIA','NULL','Amy.Baker@U8marrVO5XN9oMK.com','2452370',2981.21 |
| 'AAAAAAAAGDFJAAAA','Ms.','Margaret','Edmonds','NULL',24,NULL,NULL,'ARUBA','NULL','NULL','NULL',3097.44 |
| 'AAAAAAAAGHKPAAAA','Miss','Kimberly','Clark','N',15,11,1937,'TONGA','NULL','Kimberly.Clark@xYl6FQTOMkQPi.edu','2452477',1852.52 |
| 'AAAAAAAAGKPGAAAA','Mrs.','Rachel','Reed','Y',13,3,1950,'MALAWI','NULL','Rachel.Reed@x.com','2452574',2254.35 |
| 'AAAAAAAAGNKPAAAA','Dr.','Marcus','Davis','N',12,2,1968,'KENYA','NULL','Marcus.Davis@ksKq.com','2452633',2388.68 |
| 'AAAAAAAAGNKPAAAA','Dr.','Marcus','Davis','N',12,2,1968,'KENYA','NULL','Marcus.Davis@ksKq.com','2452633',5162.50 |
| 'AAAAAAAAGPBFAAAA','Dr.','Lorenzo','Grace','Y',5,2,1984,'UNITED STATES','NULL','Lorenzo.Grace@M8CTliq9t0H6.edu','2452613',2904.66 |
| 'AAAAAAAAHDBLAAAA','Mr.','Michael','Dupre','Y',5,9,1961,'TIMOR-LESTE','NULL','Michael.Dupre@AhCfsll84OUMVNZ.com','2452438',3593.72 |
| 'AAAAAAAAHDKHBAAA','Sir','NULL','Free','N',6,NULL,1956,'NULL','NULL','NULL','2452293',2100.64 |
| 'AAAAAAAAHEFEAAAA','Dr.','James','Martin','Y',28,9,1963,'PERU','NULL','James.Martin@31H8tf1eym.org','2452321',2928.24 |
| 'AAAAAAAAHKCEBAAA','Mrs.','Stella','Osorio','N',2,10,1966,'ANGUILLA','NULL','Stella.Osorio@6M8UcU9r.org','2452421',2095.08 |
| 'AAAAAAAAHNIFBAAA','Mrs.','Jodi','Hickman','N',15,9,1973,'MALAYSIA','NULL','Jodi.Hickman@OdAHHq4.com','2452312',8251.12 |
| 'AAAAAAAAIFNHBAAA','Sir','Victor','Mccloskey','N',24,2,1940,'MONTSERRAT','NULL','Victor.Mccloskey@RymmApu9aLZ.com','2452604',1819.48 |
| 'AAAAAAAAIGJGAAAA','Sir','Edward','Britton','Y',NULL,NULL,1981,'NULL','NULL','Edward.Britton@G.edu','NULL',3791.04 |
| 'AAAAAAAAIHLGAAAA','Miss','Jennifer','Smith','N',31,10,1969,'TRINIDAD AND TOBAGO','NULL','Jennifer.Smith@vYc7AUOo7.org','2452564',7360.74 |
| 'AAAAAAAAIIABAAAA','Miss','Rosa','Phillips','Y',19,3,1949,'SURINAME','NULL','Rosa.Phillips@ECXsk.com','2452488',6261.20 |
| 'AAAAAAAAIIHMAAAA','Mr.','Ricky','Gerber','N',10,11,1973,'WALLIS AND FUTUNA','NULL','Ricky.Gerber@5dsb4vhLdmZm.edu','2452402',1707.12 |
| 'AAAAAAAAIKABAAAA','Dr.','James','Taylor','Y',2,1,1967,'LEBANON','NULL','James.Taylor@eVoZuvYcqACN.org','2452537',5759.52 |
| 'AAAAAAAAIMNPAAAA','Dr.','John','Mendoza','Y',7,1,1940,'HAITI','NULL','John.Mendoza@XDX7Ku8gVsR1LHn.org','2452462',3580.20 |
| 'AAAAAAAAIMPAAAAA','Ms.','Lois','Washington','N',21,8,1973,'TONGA','NULL','Lois.Washington@uvUAMB9DuxcK.edu','2452319',1756.80 |
| 'AAAAAAAAJAMHBAAA','Miss','Kathleen','Dupre','Y',7,10,1951,'TURKEY','NULL','Kathleen.Dupre@hfQ.edu','2452349',6127.60 |
| 'AAAAAAAAJEIFBAAA','NULL','NULL','NULL','N',NULL,NULL,1967,'NULL','NULL','Michael.Lee@k0QIl6mntspX.org','2452411',3304.87 |
| 'AAAAAAAAJGJEBAAA','Mr.','Charles','Jackson','N',25,3,1945,'LIBERIA','NULL','Charles.Jackson@ziLxUxp0.edu','2452585',2983.56 |
| 'AAAAAAAAJHBDBAAA','Mr.','Edgar','Hunt','Y',27,12,1932,'CAMEROON','NULL','Edgar.Hunt@Z.edu','2452284',2066.48 |
| 'AAAAAAAAJJKMAAAA','Sir','Jimmy','Mcinnis','Y',25,1,1964,'SINGAPORE','NULL','Jimmy.Mcinnis@9QXGY2Fn18bZ.org','2452483',3542.97 |
| 'AAAAAAAAJKACAAAA','Dr.','Arthur','Harris','N',9,3,1925,'SEYCHELLES','NULL','Arthur.Harris@Sj3QkYzfu.com','2452622',4920.90 |
| 'AAAAAAAAKBOHAAAA','Mr.','NULL','Christian','NULL',NULL,10,NULL,'GUERNSEY','NULL','James.Christian@8r5T.com','2452522',2824.24 |
| 'AAAAAAAAKGFEBAAA','Mr.','Federico','Bradshaw','N',11,5,1978,'MONTSERRAT','NULL','Federico.Bradshaw@uzthL55V.com','2452453',1633.59 |
| 'AAAAAAAAKJDMAAAA','Miss','Andrew','Osborne','N',13,5,1954,'SUDAN','NULL','Andrew.Osborne@D.edu','2452495',2858.60 |
| 'AAAAAAAAKKCMAAAA','Sir','Randall','Oliver','Y',15,6,1969,'GREECE','NULL','Randall.Oliver@lr7eEGq2qUT8sD2.edu','2452305',3917.80 |
| 'AAAAAAAAKMJBAAAA','Sir','John','Kruse','N',25,9,1950,'NEW ZEALAND','NULL','John.Kruse@BgZ6x.edu','2452516',4032.72 |
| 'AAAAAAAAKPPEBAAA','Mr.','Thomas','Kurtz','N',23,1,1953,'WALLIS AND FUTUNA','NULL','Thomas.Kurtz@Xd5JPu7M1.edu','2452571',2123.82 |
| 'AAAAAAAALCECAAAA','Sir','Dennis','Silva','N',29,10,1972,'TAJIKISTAN','NULL','Dennis.Silva@cjk.com','2452425',1934.64 |
| 'AAAAAAAALFAGBAAA','Sir','Alvin','Young','Y',24,10,1973,'MONACO','NULL','Alvin.Young@iit.org','2452518',4766.65 |
| 'AAAAAAAALJMHBAAA','Dr.','Stephanie','Maloney','Y',19,12,1935,'MOZAMBIQUE','NULL','Stephanie.Maloney@xlkHMrqUSFcrHG.com','2452572',14950.15 |
| 'AAAAAAAALKHEAAAA','Mr.','Neil','Delarosa','Y',6,1,1933,'SEYCHELLES','NULL','Neil.Delarosa@F26REbIyVtpkY.org','2452299',1575.84 |
| 'AAAAAAAALPIOAAAA','Mr.','Kendrick','Swank','N',10,9,1983,'NIGERIA','NULL','Kendrick.Swank@KiHF1kfJUPMgrkxAtGtI.com','2452629',6283.76 |
| 'AAAAAAAAMAHMAAAA','Sir','John','Nunez','Y',29,7,1963,'GIBRALTAR','NULL','John.Nunez@NXXNFCCxOFqDq6B.com','2452400',8665.51 |
| 'AAAAAAAAMBIEAAAA','Dr.','Timothy','Bruce','Y',25,3,1938,'FIJI','NULL','Timothy.Bruce@gO.org','2452530',3006.00 |
| 'AAAAAAAAMFGLAAAA','Dr.','Ellis','Stringer','N',30,3,1985,'VIET NAM','NULL','Ellis.Stringer@dLx2T0e7gAp.com','2452520',1646.55 |
| 'AAAAAAAAMFNCAAAA','Miss','Sandra','Chavez','N',15,10,1936,'MAURITANIA','NULL','Sandra.Chavez@jNLsx1jyV7C.org','2452610',7062.75 |
| 'AAAAAAAAMKHOAAAA','Miss','Kristie','Ross','N',6,1,1966,'BARBADOS','NULL','Kristie.Ross@p.edu','2452439',1842.95 |
| 'AAAAAAAANDGCBAAA','Ms.','Sheila','Hull','N',23,11,1992,'EQUATORIAL GUINEA','NULL','Sheila.Hull@eDEnMk74Ukoo.com','2452416',3453.32 |
| 'AAAAAAAANIAFBAAA','Dr.','Adrian','Holt','Y',27,3,1990,'SEYCHELLES','NULL','Adrian.Holt@eQCL.org','2452529',1233.10 |
| 'AAAAAAAANJJGAAAA','Dr.','David','Smith','Y',20,7,1939,'GUINEA-BISSAU','NULL','David.Smith@q8KF56o7peqsXNf3I.com','2452371',3186.92 |
| 'AAAAAAAANNBABAAA','Ms.','Cordelia','Carpenter','Y',19,8,1990,'PALAU','NULL','Cordelia.Carpenter@nsyrjxnu.com','2452487',5297.04 |
| 'AAAAAAAANONGAAAA','Dr.','Joel','Rucker','N',15,2,1947,'BOLIVIA','NULL','Joel.Rucker@CaD.edu','2452289',1910.50 |
| 'AAAAAAAAOCGFAAAA','Miss','Adam','Martin','Y',20,9,1970,'ARMENIA','NULL','Adam.Martin@0Sb2XqFA9.edu','2452626',3373.66 |
| 'AAAAAAAAOFGEAAAA','Sir','John','Hopkins','N',9,4,1953,'MALTA','NULL','John.Hopkins@zsuCL.edu','2452508',5102.37 |
| 'AAAAAAAAOFICAAAA','NULL','Ramona','Moore','N',NULL,NULL,1949,'NULL','NULL','Ramona.Moore@Zk.edu','NULL',1641.74 |
| 'AAAAAAAAOFIHBAAA','Mr.','Francis','Wilhelm','Y',28,1,1985,'LEBANON','NULL','Francis.Wilhelm@R3.edu','2452291',11642.40 |
| 'AAAAAAAAOLBHBAAA','Dr.','Nathaniel','Downs','Y',2,7,1944,'DOMINICA','NULL','Nathaniel.Downs@sdfiKzLFg7Et.org','2452514',7484.11 |
| 'AAAAAAAAOLBHBAAA','Dr.','Nathaniel','Downs','Y',2,7,1944,'DOMINICA','NULL','Nathaniel.Downs@sdfiKzLFg7Et.org','2452514',8594.42 |
| 'AAAAAAAAOMMCAAAA','Dr.','Roberta','Johnson','Y',15,3,1976,'ANTIGUA AND BARBUDA','NULL','Roberta.Johnson@uAvA75ZjM.com','2452581',1803.33 |
| 'AAAAAAAAPEAIAAAA','Sir','William','Mason','Y',1,5,1977,'KYRGYZSTAN','NULL','William.Mason@RD4e6I1JfQkEL9.org','2452495',3683.03 |
| 'AAAAAAAAPIHKAAAA','Dr.','Felix','Bailey','Y',8,7,1959,'TIMOR-LESTE','NULL','Felix.Bailey@0dKxiSYTnCjo5ZPe1.com','2452328',2856.00 |
| 'AAAAAAAAPINOAAAA','Dr.','Michael','Johnson','Y',3,1,1976,'URUGUAY','NULL','Michael.Johnson@5l6aamKSUv6BV.org','2452531',8948.80 |
| 'AAAAAAAAPJAGBAAA','Dr.','Michael','Stevens','N',28,2,1943,'GEORGIA','NULL','Michael.Stevens@c9ygsUdHa3J1XG.com','2452349',4491.28 |
| 'AAAAAAAAPKCMAAAA','Dr.','Harold','Williams','N',13,9,1976,'QATAR','NULL','Harold.Williams@U44mtEQN0Qrm.org','2452409',1760.85 |
| 'AAAAAAAAPNDDAAAA','Mr.','Tomas','Menendez','N',26,9,1934,'FINLAND','NULL','Tomas.Menendez@qibyGN3Ior.edu','2452573',1945.48 |
| ---- TYPES |
| STRING, STRING, STRING, STRING, STRING, INT, INT, INT, STRING, STRING, STRING, STRING, DECIMAL |
| ==== |
