| ==== |
| ---- QUERY: TPCDS-Q81 |
| with customer_total_return as |
| (select cr_returning_customer_sk as ctr_customer_sk |
| ,ca_state as ctr_state, |
| sum(cr_return_amt_inc_tax) as ctr_total_return |
| from catalog_returns |
| ,date_dim |
| ,customer_address |
| where cr_returned_date_sk = d_date_sk |
| and d_year =2000 |
| and cr_returning_addr_sk = ca_address_sk |
| group by cr_returning_customer_sk |
| ,ca_state ) |
| select c_customer_id,c_salutation,c_first_name,c_last_name,ca_street_number,ca_street_name |
| ,ca_street_type,ca_suite_number,ca_city,ca_county,ca_state,ca_zip,ca_country,ca_gmt_offset |
| ,ca_location_type,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 = 'GA' |
| and ctr1.ctr_customer_sk = c_customer_sk |
| order by c_customer_id,c_salutation,c_first_name,c_last_name,ca_street_number,ca_street_name |
| ,ca_street_type,ca_suite_number,ca_city,ca_county,ca_state,ca_zip,ca_country,ca_gmt_offset |
| ,ca_location_type,ctr_total_return |
| limit 100; |
| ---- RESULTS |
| 'AAAAAAAAABBEAAAA','Dr.','Oscar','Fox','183','15th ','Ln','Suite P','Greenwood','Clay County','GA','38828','United States',-5.00,'condo',4607.92 |
| 'AAAAAAAAADCEBAAA','Sir','Billy','Bell','690','4th Fifth','Ct.','Suite 390','Hillcrest','Spalding County','GA','33003','United States',-5.00,'condo',5567.44 |
| 'AAAAAAAAAFBGAAAA','Mrs.','Erika','Buford','527','1st ','Ln','Suite 380','Lakewood','Jasper County','GA','38877','United States',-5.00,'single family',5412.16 |
| 'AAAAAAAAAFLABAAA','Mr.','Richard','James','40','2nd Miller','Road','Suite 190','Arlington','Morgan County','GA','36557','United States',-5.00,'apartment',2249.10 |
| 'AAAAAAAAAHOEAAAA','Mr.','Michael','Yates','349','Elm South','ST','Suite 30','Blanchard','Talbot County','GA','35985','United States',-5.00,'single family',1810.13 |
| 'AAAAAAAAAIEHBAAA','Miss','Tamara','Chestnut','934','13th Cherry','Blvd','Suite O','Antioch','Haralson County','GA','38605','United States',-5.00,'condo',1639.26 |
| 'AAAAAAAAAJLNAAAA','Dr.','Sheree','Bragg','664','Elm 4th','Ave','Suite 320','Glenwood','McDuffie County','GA','33511','United States',-5.00,'condo',3012.45 |
| 'AAAAAAAAAJNEBAAA','Dr.','David','Hamilton','108','Spring Wilson','Ct.','Suite F','Glenwood','Macon County','GA','33511','United States',-5.00,'apartment',2567.79 |
| 'AAAAAAAAAKJOAAAA','Dr.','Gus','Stewart','883','Valley ','Drive','Suite J','Macedonia','Lanier County','GA','31087','United States',-5.00,'single family',3530.28 |
| 'AAAAAAAAAMFBBAAA','Mr.','Jackson','NULL','177','8th Walnut','Ct.','Suite 300','Riverview','Jenkins County','GA','39003','United States',-5.00,'single family',4875.86 |
| 'AAAAAAAAAMPKAAAA','Ms.','Elizabeth','Booth','344','Jackson North','Drive','Suite 100','Woodville','Fayette County','GA','34289','United States',-5.00,'single family',2372.60 |
| 'AAAAAAAAANCLAAAA','Mr.','Raymond','Dixon','189','Jackson 7th','Parkway','Suite 360','Lakewood','Dade County','GA','38877','United States',-5.00,'single family',4655.74 |
| 'AAAAAAAAANJIAAAA','Dr.','Dennis','Roper','600','Walnut ','Pkwy','Suite 140','Plainview','Barrow County','GA','33683','United States',-5.00,'apartment',3500.40 |
| 'AAAAAAAAANLABAAA','Sir','Thomas','Zimmerman','496','Forest ','ST','Suite L','Saratoga','Lumpkin County','GA','32123','United States',-5.00,'apartment',9193.95 |
| 'AAAAAAAAAODFAAAA','Mrs.','Kathy','Villegas','156','4th Sunset','Cir.','Suite I','Red Hill','Appling County','GA','34338','United States',-5.00,'single family',2106.00 |
| 'AAAAAAAAAOEBBAAA','Sir','Benjamin','Parker','698','6th ','Drive','Suite O','Plainview','Screven County','GA','33683','United States',-5.00,'apartment',3891.38 |
| 'AAAAAAAAAOEBBAAA','Sir','Benjamin','Parker','698','6th ','Drive','Suite O','Plainview','Screven County','GA','33683','United States',-5.00,'apartment',9787.55 |
| 'AAAAAAAAAPBBBAAA','Sir','Donald','Littlejohn','467','Lake ','Ln','Suite 20','Riverside','Turner County','GA','39231','United States',-5.00,'single family',4831.52 |
| 'AAAAAAAABABBBAAA','Mr.','David','Roe','521','Jefferson ','Parkway','Suite 120','Enterprise','Wilcox County','GA','31757','United States',-5.00,'apartment',4079.80 |
| 'AAAAAAAABAJHAAAA','Mrs.','Doris','Gillespie','179','Washington Chestnut','Dr.','Suite W','Jackson','Columbia County','GA','39583','United States',-5.00,'condo',4617.58 |
| 'AAAAAAAABANGBAAA','Mr.','Ralph','Condon','755','Davis ','Blvd','Suite 280','Antioch','Pike County','GA','38605','United States',-5.00,'condo',14966.24 |
| 'AAAAAAAABBHKAAAA','Mrs.','Kristin','Grant','870','Ash Park','Ave','Suite 300','Lakeview','Harris County','GA','38579','United States',-5.00,'single family',3580.44 |
| 'AAAAAAAABBOBBAAA','Mr.','Herman','Rivera','396','Walnut ','Cir.','Suite P','Athens','Baldwin County','GA','36796','United States',-5.00,'single family',4608.96 |
| 'AAAAAAAABCAOAAAA','Dr.','Brenda','Bell','562','7th Park','Road','Suite 320','Edgewood','Troup County','GA','30069','United States',-5.00,'apartment',3721.03 |
| 'AAAAAAAABCIMAAAA','Dr.','Becky','Seals','174','Park Park','Ct.','Suite H','Green Acres','Schley County','GA','37683','United States',-5.00,'single family',2980.15 |
| 'AAAAAAAABDHFAAAA','Mr.','Charles','Miller','531','Church 14th','Circle','Suite 60','Ashland','Franklin County','GA','34244','United States',-5.00,'apartment',3241.85 |
| 'AAAAAAAABEHABAAA','Dr.','Gregory','NULL','391','Seventh ','Road','Suite Q','Oakwood','Liberty County','GA','30169','United States',-5.00,'single family',3520.33 |
| 'AAAAAAAABGDGBAAA','Sir','Brian','Adams','915','Jackson ','RD','Suite O','Concord','Columbia County','GA','34107','United States',-5.00,'apartment',2912.27 |
| 'AAAAAAAABGLCBAAA','Dr.','Joseph','Torres','103','Highland 7th','Court','Suite I','Wilson','Jenkins County','GA','36971','United States',-5.00,'single family',1842.89 |
| 'AAAAAAAABHOFAAAA','Ms.','Anthony','NULL','518','Meadow Smith','Ct.','Suite C','Riverdale','Bleckley County','GA','39391','United States',-5.00,'single family',1878.44 |
| 'AAAAAAAABIMMAAAA','Dr.','Shanna','Mcrae','764','Walnut ','Street','Suite B','Brownsville','Lumpkin County','GA','39310','United States',-5.00,'condo',7237.05 |
| 'AAAAAAAABINIAAAA','Dr.','Jose','Reynolds','571','Main ','Ave','Suite B','Mount Zion','Troup County','GA','38054','United States',-5.00,'apartment',2463.65 |
| 'AAAAAAAABJMEAAAA','Miss','Deborah','Sheehan','450','Willow ','Cir.','Suite G','Marion','Franklin County','GA','30399','United States',-5.00,'condo',6623.88 |
| 'AAAAAAAABKAIAAAA','Miss','Ruth','Rodriguez','706','Williams ','Pkwy','Suite Y','Antioch','Lanier County','GA','38605','United States',-5.00,'single family',3025.05 |
| 'AAAAAAAABMBLAAAA','Ms.','Ava','Jackson','601','Fourth Washington','Way','Suite 440','Glendale','Cherokee County','GA','33951','United States',-5.00,'apartment',1637.46 |
| 'AAAAAAAABMBLAAAA','Ms.','Ava','Jackson','601','Fourth Washington','Way','Suite 440','Glendale','Cherokee County','GA','33951','United States',-5.00,'apartment',2373.14 |
| 'AAAAAAAABMFDAAAA','Mrs.','Megan','Webster','124','11th ','Pkwy','Suite K','Shady Grove','Miller County','GA','32812','United States',-5.00,'single family',3059.27 |
| 'AAAAAAAABNMGBAAA','Mrs.','Wanda','Shumate','422','Pine 3rd','Cir.','Suite 170','Crossroads','Berrien County','GA','30534','United States',-5.00,'single family',1699.41 |
| 'AAAAAAAABNMIAAAA','Miss','Bobbi','Erickson','731','Jefferson ','Parkway','Suite H','Mountain View','Taylor County','GA','34466','United States',-5.00,'apartment',2925.34 |
| 'AAAAAAAABOAPAAAA','Ms.','Karen','Burt','794','Sunset Sunset','Drive','Suite 470','Glendale','Calhoun County','GA','33951','United States',-5.00,'condo',8807.07 |
| 'AAAAAAAABOCDBAAA','Ms.','Flora','Messer','608','10th ','Court','Suite A','Highland Park','Dooly County','GA','36534','United States',-5.00,'single family',3255.56 |
| 'AAAAAAAABPIMAAAA','Dr.','Steve','Cano','711','Park ','Ln','Suite N','Smith','Gwinnett County','GA','37317','United States',-5.00,'single family',2086.39 |
| 'AAAAAAAACABFAAAA','Sir','Steven','Lamb','726','Cherry Hill','ST','Suite E','Woodville','Worth County','GA','34289','United States',-5.00,'apartment',2361.71 |
| 'AAAAAAAACBDDAAAA','Sir','Terry','Seiler','960','Eigth ','Way','Suite 240','Mountain View','Putnam County','GA','34466','United States',-5.00,'apartment',3222.09 |
| 'AAAAAAAACCBOAAAA','Sir','Patrick','Miller','577','Church ','Ave','Suite 110','Bethel','White County','GA','35281','United States',-5.00,'single family',2927.35 |
| 'AAAAAAAACDOPAAAA','Mrs.','Carolyn','Singer','399','Second ','Boulevard','Suite O','White Oak','Wayne County','GA','36668','United States',-5.00,'condo',4011.31 |
| 'AAAAAAAACFADBAAA','NULL','NULL','NULL','502','Willow Park','Cir.','Suite E','Dewey','Colquitt County','GA','31160','United States',-5.00,'apartment',3732.03 |
| 'AAAAAAAACIHABAAA','Ms.','Arlene','Baker','216','Maple Walnut','Ave','Suite 310','Greenfield','Glascock County','GA','35038','United States',-5.00,'single family',2857.28 |
| 'AAAAAAAACJCHAAAA','Mr.','Wilbur','Mullins','119','Eigth ','Dr.','Suite W','Fairview','Wilkes County','GA','35709','United States',-5.00,'single family',4861.83 |
| 'AAAAAAAACJJKAAAA','Mr.','Ramon','Zaragoza','934','Park ','Lane','Suite K','Lakewood','Muscogee County','GA','38877','United States',-5.00,'condo',9022.65 |
| 'AAAAAAAACKHEAAAA','Mr.','Richard','Leblanc','18','Green View','Court','Suite J','Woodville','Butts County','GA','34289','United States',-5.00,'single family',3787.49 |
| 'AAAAAAAACODJAAAA','Dr.','Neal','Nash','95','NULL','Street','Suite 110','Spring Hill','Murray County','GA','36787','NULL',-5.00,'NULL',2144.31 |
| 'AAAAAAAACOFIBAAA','Mr.','Fred','Morales','687','5th Park','Cir.','Suite X','Fairview','Murray County','GA','35709','United States',-5.00,'condo',1936.70 |
| 'AAAAAAAACOHIAAAA','Mr.','Roger','Grider','191','Williams Second','Avenue','Suite 420','Brownsville','Coweta County','GA','39310','United States',-5.00,'single family',5782.43 |
| 'AAAAAAAACPBABAAA','Miss','Lucile','Anderson','149','View Williams','Ct.','Suite L','Mount Pleasant','Washington County','GA','31933','United States',-5.00,'single family',2600.59 |
| 'AAAAAAAADANFBAAA','Mrs.','Ann','Nadeau','247','Maple ','Avenue','Suite 260','Hamilton','Elbert County','GA','32808','United States',-5.00,'single family',4103.32 |
| 'AAAAAAAADBAHAAAA','Dr.','Lucille','Hill','668','West Laurel','Pkwy','Suite M','Mount Olive','Quitman County','GA','38059','United States',-5.00,'single family',2033.22 |
| 'AAAAAAAADBEIBAAA','Miss','Erica','Hill','396','Maple Johnson','Avenue','Suite R','Crossroads','Hall County','GA','30534','United States',-5.00,'condo',4500.01 |
| 'AAAAAAAADBEOAAAA','Miss','Loretta','Smith','149','Maple ','ST','Suite 350','Fairview','Cherokee County','GA','35709','United States',-5.00,'apartment',3810.28 |
| 'AAAAAAAADBFHBAAA','Mr.','Charles','Robinson','731','Ridge ','Lane','Suite 160','New Hope','Bartow County','GA','39431','United States',-5.00,'apartment',4042.46 |
| 'AAAAAAAADCAEAAAA','Dr.','Joanna','Walker','665','15th Maple','Avenue','Suite Y','Camden','McDuffie County','GA','37119','United States',-5.00,'single family',4067.57 |
| 'AAAAAAAADCLBAAAA','Mrs.','Evelyn','Hammett','302','1st Adams','Cir.','Suite 370','Five Points','Pulaski County','GA','36098','United States',-5.00,'single family',2187.12 |
| 'AAAAAAAADDENAAAA','Sir','Alejandro','Doyle','916','Cherry Elm','ST','Suite 110','Providence','Clay County','GA','36614','United States',-5.00,'single family',3357.92 |
| 'AAAAAAAADEGFAAAA','Sir','Jesse','Johnson','725','Laurel First','Parkway','Suite O','Pleasant Valley','Candler County','GA','32477','United States',-5.00,'condo',4969.77 |
| 'AAAAAAAADGCCAAAA','Miss','Loretta','Moss','274','3rd Walnut','Ave','Suite L','Arlington','Lee County','GA','36557','United States',-5.00,'condo',1891.74 |
| 'AAAAAAAADHFLAAAA','Mr.','George','Schneider','579','Johnson Ash','Way','Suite 110','Concord','Seminole County','GA','34107','United States',-5.00,'condo',4011.59 |
| 'AAAAAAAADHOIAAAA','Miss','Andrew','James','139','Smith Lincoln','Lane','Suite 360','Stringtown','Coweta County','GA','30162','United States',-5.00,'condo',2834.91 |
| 'AAAAAAAADJABAAAA','Mrs.','Charity','Arroyo','158','Highland Elm','Ln','Suite K','Woodland','Pickens County','GA','34854','United States',-5.00,'apartment',2443.75 |
| 'AAAAAAAADJJMAAAA','Dr.','Joseph','Burns','339','12th ','Blvd','Suite S','Hamilton','Banks County','GA','32808','United States',-5.00,'condo',2670.84 |
| 'AAAAAAAADJODAAAA','Dr.','Larry','Phillips','612','Mill Church','Blvd','Suite 0','Sunnyside','Fannin County','GA','31952','United States',-5.00,'single family',7260.82 |
| 'AAAAAAAADKKGAAAA','Sir','Robert','Patrick','638','Fourth ','Cir.','Suite 250','Lebanon','Clay County','GA','32898','United States',-5.00,'condo',9049.72 |
| 'AAAAAAAADMDHAAAA','Mr.','Samuel','Wells','877','Highland East','Road','Suite I','Valley View','Bartow County','GA','35124','United States',-5.00,'single family',4490.95 |
| 'AAAAAAAADNIBBAAA','Miss','Sharon','Prince','955','Walnut Ridge','Boulevard','Suite W','Cedar Grove','Troup County','GA','30411','United States',-5.00,'condo',2158.11 |
| 'AAAAAAAADNOGBAAA','Dr.','David','Lofton','327','11th Central','Parkway','Suite D','White Oak','Glynn County','GA','36668','United States',-5.00,'single family',1971.52 |
| 'AAAAAAAADOBCBAAA','Dr.','Benjamin','Pham','300','Willow Sycamore','Blvd','Suite 380','Liberty','Twiggs County','GA','33451','United States',-5.00,'apartment',1787.84 |
| 'AAAAAAAADOFNAAAA','Mrs.','Sebrina','Jacobs','611','Center ','Avenue','Suite 210','Glenwood','Ware County','GA','33511','United States',-5.00,'condo',5738.75 |
| 'AAAAAAAAEAFJAAAA','Mr.','Gregory','Quigley','932','Maple ','Avenue','Suite 300','Glendale','Butts County','GA','33951','United States',-5.00,'single family',2621.58 |
| 'AAAAAAAAEAJGBAAA','Miss','Helen','Flora','38','Park ','Circle','Suite 360','Florence','Pike County','GA','33394','United States',-5.00,'apartment',2804.13 |
| 'AAAAAAAAEBDFAAAA','Mr.','Ed','Chavis','276','Pine Hill','Boulevard','Suite 150','Shiloh','Lincoln County','GA','39275','United States',-5.00,'single family',1800.04 |
| 'AAAAAAAAECIHAAAA','Sir','Gilberto','Mcdonald','615','10th ','Blvd','Suite 150','Oakdale','Glynn County','GA','39584','United States',-5.00,'apartment',6023.68 |
| 'AAAAAAAAECOGBAAA','Dr.','David','Lamb','436','Second Elm','Way','Suite 30','Green Acres','Laurens County','GA','37683','United States',-5.00,'single family',4778.71 |
| 'AAAAAAAAEDOAAAAA','Dr.','NULL','Finley','899','Willow Pine','ST','Suite 160','Clinton','Peach County','GA','38222','United States',-5.00,'condo',18954.78 |
| 'AAAAAAAAEEFEBAAA','Mr.','Gordon','Lee','444','Park Jackson','Boulevard','Suite 150','Kingston','Mitchell County','GA','34975','United States',-5.00,'single family',2499.41 |
| 'AAAAAAAAEFMHBAAA','Ms.','Anne','Busby','724','Second ','ST','Suite 20','Union Hill','Pulaski County','GA','37746','United States',-5.00,'condo',2866.95 |
| 'AAAAAAAAEGOJAAAA','Miss','Caroline','Miller','719','Smith ','RD','Suite M','Empire','Oglethorpe County','GA','34145','United States',-5.00,'single family',7530.99 |
| 'AAAAAAAAEHCIAAAA','Ms.','Laura','Wright','816','Main ','Ln','Suite 300','Sunnyside','Emanuel County','GA','31952','United States',-5.00,'apartment',2767.59 |
| 'AAAAAAAAEHPBAAAA','Dr.','Paula','Bearden','932','Williams Forest','Ln','Suite 10','Allentown','Long County','GA','31838','United States',-5.00,'single family',12021.15 |
| 'AAAAAAAAEJFNAAAA','Dr.','Chad','Baldwin','404','Cedar ','Ct.','Suite Y','Enterprise','Treutlen County','GA','31757','United States',-5.00,'apartment',1737.56 |
| 'AAAAAAAAEJJDAAAA','Ms.','Katherine','Hernandez','741','Park Jefferson','ST','Suite 270','Oak Ridge','McIntosh County','GA','38371','United States',-5.00,'single family',6714.64 |
| 'AAAAAAAAEKGABAAA','Sir','Steven','Thurman','638','Park Green','Ct.','Suite 270','Lakeside','Wayne County','GA','39532','United States',-5.00,'single family',1662.14 |
| 'AAAAAAAAEKJGBAAA','Sir','Kurt','Jenkins','656','8th ','Dr.','Suite E','Franklin','Heard County','GA','39101','United States',-5.00,'apartment',1607.08 |
| 'AAAAAAAAELCGBAAA','Sir','Harold','Randolph','535','Lee ','RD','Suite 160','Sutton','Bacon County','GA','35413','United States',-5.00,'apartment',4737.51 |
| 'AAAAAAAAELJAAAAA','Miss','Gwendolyn','Johnson','482','Sunset Spring','Ave','Suite J','Mount Olive','Madison County','GA','38059','United States',-5.00,'apartment',7387.08 |
| 'AAAAAAAAENFHBAAA','Dr.','Janice','Ojeda','127','Williams 5th','Ave','Suite 280','Walnut Grove','Burke County','GA','37752','United States',-5.00,'condo',4838.93 |
| 'AAAAAAAAEOBHAAAA','Miss','Janet','Mcculloch','379','Spring Eigth','Street','Suite R','Cedar Grove','Wilcox County','GA','30411','United States',-5.00,'single family',7581.20 |
| 'AAAAAAAAEOGGAAAA','Sir','Christopher','Garrett','308','Broadway 4th','Lane','Suite 360','Sunnyside','Coffee County','GA','31952','United States',-5.00,'apartment',3279.85 |
| 'AAAAAAAAEPBDBAAA','Dr.','Curtis','Briscoe','142','Meadow ','Road','Suite 460','Woodville','Montgomery County','GA','34289','United States',-5.00,'single family',5239.12 |
| 'AAAAAAAAFAHCAAAA','Ms.','Anna','Koch','482','Sunset Spring','Ave','Suite J','Mount Olive','Madison County','GA','38059','United States',-5.00,'apartment',1858.37 |
| 'AAAAAAAAFANDAAAA','Dr.','Daniel','Wilson','586','Pine Cherry','Street','Suite U','Deerfield','Muscogee County','GA','39840','United States',-5.00,'condo',2774.69 |
| 'AAAAAAAAFANDAAAA','Dr.','Daniel','Wilson','586','Pine Cherry','Street','Suite U','Deerfield','Muscogee County','GA','39840','United States',-5.00,'condo',2946.52 |
| ---- TYPES |
| STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, DECIMAL, STRING, DECIMAL |
| ==== |
