| ==== |
| ---- QUERY: TPCDS-Q68 |
| select |
| c_last_name, |
| c_first_name, |
| ca_city, |
| bought_city, |
| ss_ticket_number, |
| extended_price, |
| extended_tax, |
| list_price |
| from |
| (select |
| ss_ticket_number, |
| ss_customer_sk, |
| ca_city bought_city, |
| sum(ss_ext_sales_price) extended_price, |
| sum(ss_ext_list_price) list_price, |
| sum(ss_ext_tax) extended_tax |
| from |
| store_sales, |
| date_dim, |
| store, |
| household_demographics, |
| customer_address |
| where |
| store_sales.ss_sold_date_sk = date_dim.d_date_sk |
| and store_sales.ss_store_sk = store.s_store_sk |
| and store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk |
| and store_sales.ss_addr_sk = customer_address.ca_address_sk |
| and date_dim.d_dom between 1 and 2 |
| and (household_demographics.hd_dep_count = 4 |
| or household_demographics.hd_vehicle_count = 3) |
| and date_dim.d_year in (1999, 1999 + 1, 1999 + 2) |
| and store.s_city in ('Midway', 'Fairview') |
| group by |
| ss_ticket_number, |
| ss_customer_sk, |
| ss_addr_sk, |
| ca_city |
| ) dn, |
| customer, |
| customer_address current_addr |
| where |
| ss_customer_sk = c_customer_sk |
| and customer.c_current_addr_sk = current_addr.ca_address_sk |
| and current_addr.ca_city <> bought_city |
| order by |
| c_last_name, |
| ss_ticket_number |
| limit 100; |
| ---- RESULTS |
| 'Aaron','Guy','Lakeside','Mount Pleasant',210815,24704.66,678.88,45673.18 |
| 'Abbott','Joseph','Greenfield','Midway',62751,37795.50,1086.40,53385.17 |
| 'Abbott','Harriet','Wesley','Forest Hills',84405,23666.36,1274.86,45689.64 |
| 'Abney','Louis','Gladstone','Green Acres',140967,20698.82,491.86,48086.21 |
| 'Acevedo','David','Clinton','Shady Grove',131505,22075.16,772.60,46961.99 |
| 'Acosta','Johnnie','Centerville','Enterprise',69183,21688.97,1059.74,34387.30 |
| 'Acosta','David','Harmony','Bunker Hill',112259,11738.20,399.34,17105.96 |
| 'Adair','Evan','Blanchard','New Hope',117449,10843.01,540.08,20084.01 |
| 'Adams','Genevieve','Enterprise','Union Hill',109910,32666.21,1432.96,61189.81 |
| 'Adams','Joyce','Newtown','Concord',166181,18450.10,1108.79,28427.74 |
| 'Adams','John','Marion','Oak Ridge',206733,34785.44,852.47,57926.68 |
| 'Adams','David','Bridgeport','Antioch',213366,12990.48,225.47,28512.17 |
| 'Aguayo','John','Newport','Newtown',156381,17114.17,567.09,30022.52 |
| 'Aguayo','Jeanne','Glendale','Hopewell',199409,29035.37,1374.34,53784.57 |
| 'Aguilar','Arlene','Ashland','Brownsville',121548,31984.01,1162.65,50601.98 |
| 'Aguirre','Penny','Union Hill','Marion',104413,35843.61,966.63,52811.51 |
| 'Ahmed','Maureen','Oak Hill','Union Hill',128051,23686.18,1174.98,41563.27 |
| 'Aiken','James','Mount Olive','Crossroads',212463,18049.02,819.49,29911.37 |
| 'Alarcon','Victoria','Spring Valley','Bunker Hill',134199,18533.41,710.55,37775.73 |
| 'Alexander','Robert','Woodlawn','Arlington',174134,28340.20,914.46,51978.95 |
| 'Alexander','Megan','White Oak','Greenfield',179976,21580.95,898.61,42878.10 |
| 'Alexander','Beverly','Valley View','Five Forks',212635,15885.12,1067.05,30762.55 |
| 'Allen','Amy','Oakland','Greenville',59638,27050.83,1879.66,41767.88 |
| 'Allen','Kenneth','Springdale','Liberty',79152,10310.19,260.52,30436.60 |
| 'Allen','Harold','Lincoln','Providence',84380,22544.47,1135.75,57628.38 |
| 'Allen','William','Lewis','Springdale',101791,20167.15,1076.20,25099.71 |
| 'Allen','Dustin','Arlington','Five Forks',167363,9931.55,397.05,41126.80 |
| 'Allen','Marcus','Woodlawn','Belleville',213821,17649.78,797.92,66454.88 |
| 'Allen','Curtis','Brentwood','Richardson',216964,5751.82,180.82,39045.19 |
| 'Allison','Betty','Hamilton','Riverside',154720,13246.09,394.33,20798.94 |
| 'Allison','Sylvia','Jackson','Woodland',162335,16473.74,541.19,37558.02 |
| 'Alonzo','Charles','Unionville','Jamestown',91324,12253.64,736.42,31247.28 |
| 'Alston','Jesus','Midway','Bridgeport',52608,30120.39,1733.46,61262.55 |
| 'Alvarado','Brooke','Springfield','Deerfield',22264,7382.73,186.70,14659.04 |
| 'Alvarado','Tyson','Pleasant Grove','Midway',187704,21663.47,1230.22,40665.04 |
| 'Alvarez','Glenn','Mount Vernon','Martinsville',191809,19592.11,1176.85,41206.86 |
| 'Amato','Nelson','Brownsville','Harmon',142017,25961.35,978.70,39198.79 |
| 'Ames','Gregory','Summerfield','Carthage',239041,24406.60,890.32,48047.25 |
| 'Anderson','Alan','Bunker Hill','Oakdale',31774,27005.34,1103.94,40676.61 |
| 'Anderson','Mark','Clearwater','Shiloh',34383,11726.53,446.01,24160.55 |
| 'Anderson','Eloise','Fairview','Midway',59994,24468.31,1185.01,71467.37 |
| 'Anderson','Marcia','Clifton','Lawrenceville',83235,32158.02,1641.86,66411.93 |
| 'Anderson','Margaret','Liberty','Woodville',122663,12244.97,448.17,24411.14 |
| 'Anderson','Jeremy','Bridgeport','Golden',148124,18681.36,645.07,31244.92 |
| 'Anderson','Marcia','Clifton','Belmont',156087,15630.25,586.02,36680.54 |
| 'Anderson','Gerald','Centerville','Hamilton',161201,11833.74,556.97,28705.58 |
| 'Anderson','Mark','Clearwater','Deerfield',178872,22171.96,746.63,35582.48 |
| 'Anderson','Latasha','Florence','Altamont',187946,14746.73,254.79,31702.67 |
| 'Anderson','John','Valley View','Pleasant Valley',207323,24052.33,890.91,40618.13 |
| 'Andrews','Johnathan','Pleasant Valley','Cedar',135587,7745.55,264.73,14969.23 |
| 'Angel','Claudette','Sunnyside','Edgewood',196703,40487.66,2101.67,74569.91 |
| 'Antoine','Natalia','Pine Grove','Providence',17718,9705.04,248.18,35927.56 |
| 'Applegate','Edith','Oak Ridge','Waterloo',99615,14067.16,321.47,28150.90 |
| 'Arce','Casey','Sulphur Springs','Salem',22677,32542.08,1517.63,52981.23 |
| 'Archuleta','NULL','Yorktown','Welcome',205016,18262.18,1100.87,35615.43 |
| 'Armijo','Bryan','Pleasant Grove','Woodbury',91994,13606.26,864.10,42234.98 |
| 'Armstrong','Charles','Providence','Pine Grove',62187,13115.42,655.65,45343.33 |
| 'Ashby','Jeanne','Wildwood','Oakwood',28839,21247.34,1166.93,25034.57 |
| 'Ashe','Barbara','Spring Hill','Bunker Hill',17181,23184.07,1387.18,48324.66 |
| 'Ashley','Emmanuel','Forest Hills','Hopewell',170241,28574.95,475.41,80421.92 |
| 'Atkinson','Cedric','Red Hill','Summit',194465,30255.47,1199.93,48718.93 |
| 'Austin','Molly','Greenville','Midway',233037,21252.57,433.63,36658.60 |
| 'Avila','Ashley','Clinton','Summit',214308,19752.60,476.87,47445.77 |
| 'Ayala','Jesus','Woodville','Arlington',17419,30939.94,1343.98,56058.79 |
| 'Ayala','Ana','Clinton','Oakdale',71608,32095.11,851.00,54011.17 |
| 'Ayala','Stacy','Macedonia','Fairview',233483,21589.41,944.73,50167.53 |
| 'Ayres','Loretta','Whispering Pines','Pine Grove',95587,16392.62,528.13,42092.74 |
| 'Babin','Mike','Arcola','Sulphur Springs',179769,20294.32,731.54,46743.49 |
| 'Bailey','Robert','Mountain View','Lakeview',234709,22616.93,831.50,55324.79 |
| 'Baker','Jennifer','Georgetown','Enterprise',29854,22831.56,760.14,35952.11 |
| 'Baker','Joel','Sulphur Springs','Lincoln',48913,17417.92,829.45,33206.23 |
| 'Baker','Benjamin','Bethel','Oakland',79834,4618.07,149.22,24355.19 |
| 'Baker','Richard','Five Forks','Greenfield',110320,20924.76,1460.23,41027.94 |
| 'Baker','Craig','Oak Ridge','Providence',141109,24105.20,1083.53,42863.71 |
| 'Baker','Daniel','Jackson','Wilson',201588,15385.36,778.77,31795.09 |
| 'Baldridge','Victor','Siloam','Mount Pleasant',46121,32854.69,1031.00,77791.74 |
| 'Baldwin','James','Bunker Hill','Edgewood',67593,13885.95,500.11,43493.89 |
| 'Baldwin','Tena','Andover','Glenwood',180853,13526.84,678.23,35726.94 |
| 'Baldwin','Barry','Walnut Grove','Clinton',187586,14637.55,609.62,21051.70 |
| 'Ball','Lionel','Union','Concord',143182,25763.15,1287.78,42039.28 |
| 'Ball','Evelyn','Spring Valley','Five Points',205355,6095.24,447.79,28374.50 |
| 'Ballou','Michael','Reno','Lincoln',177577,10735.74,560.24,36449.75 |
| 'Banks','Marian','Woodville','Richfield',52317,11876.83,597.09,28381.94 |
| 'Banks','Angela','Harmony','Sullivan',90206,21927.27,1023.96,52589.76 |
| 'Barajas','Wendy','Mount Vernon','Liberty',213552,27777.38,940.01,44515.82 |
| 'Barker','Robert','Midway','Oak Ridge',23734,27986.16,662.34,49205.06 |
| 'Barker','Keith','Summit','Lakewood',139745,4983.72,181.81,12471.30 |
| 'Barker','Keith','Summit','Valley View',214164,20816.56,931.20,52036.95 |
| 'Barnes','Arthur','Flint','Stringtown',71708,13512.86,711.46,32063.72 |
| 'Barnes','Charles','Spring Hill','Franklin',79586,35911.85,759.04,61893.18 |
| 'Barnett','Donald','Fairfield','Bethel',69630,28948.35,825.18,59288.30 |
| 'Barnett','Stacie','Wilson','Greenfield',90549,21277.47,819.57,45569.89 |
| 'Barnett','Linda','Centerville','Providence',226202,28599.43,1389.20,57758.69 |
| 'Barney','Bernice','Mount Olive','Summit',192415,14490.99,919.54,33533.73 |
| 'Barnhart','Josephine','Riverside','Salem',123737,17146.50,308.43,29986.14 |
| 'Barr','Kyle','Shiloh','Greenfield',217215,2593.66,68.45,10983.70 |
| 'Barrett','Robert','Kingston','Riverview',52319,23184.59,434.24,30968.82 |
| 'Barton','Margaret','Sleepy Hollow','Highland Park',50712,15262.57,738.68,40574.06 |
| 'Bates','Albert','Springdale','Gravel Hill',113737,12809.77,585.36,31662.77 |
| 'Bauer','August','Fairfield','Belmont',134369,31443.79,1368.37,50747.16 |
| ---- TYPES |
| STRING, STRING, STRING, STRING, BIGINT, DECIMAL, DECIMAL, DECIMAL |
| ==== |
