| ==== |
| ---- QUERY: TPCDS-Q46 |
| select |
| c_last_name, |
| c_first_name, |
| ca_city, |
| bought_city, |
| ss_ticket_number, |
| amt, |
| profit |
| from |
| (select |
| ss_ticket_number, |
| ss_customer_sk, |
| ca_city bought_city, |
| sum(ss_coupon_amt) amt, |
| sum(ss_net_profit) profit |
| 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 (household_demographics.hd_dep_count = 4 |
| or household_demographics.hd_vehicle_count = 3) |
| and date_dim.d_dow in (6, 0) |
| and date_dim.d_year in (1999, 1999 + 1, 1999 + 2) |
| and store.s_city in ('Fairview','Midway','Fairview','Fairview','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, |
| c_first_name, |
| ca_city, |
| bought_city, |
| ss_ticket_number |
| limit 100; |
| ---- RESULTS |
| 'Aaron','Charles','Jamestown','Red Hill',183217,2067.37,-8054.03 |
| 'Aaron','Genevieve','Lakeside','Springtown',185247,1739.84,-1337.44 |
| 'Aaron','Guy','Lakeside','Mount Pleasant',210815,1849.59,-11333.00 |
| 'Abbott','Frederick','Green Acres','Oakland',7247,518.63,-13290.67 |
| 'Abbott','Harriet','Wesley','Forest Hills',84405,800.12,-8540.46 |
| 'Abel','Flora','Perry','Newtown',117310,8532.29,-28474.67 |
| 'Abney','Louis','Gladstone','Green Acres',140967,7.52,-13382.19 |
| 'Abrams','Amy','Salem','Pine Grove',231298,1132.44,-19986.97 |
| 'Abrams','Dorothy','Lincoln','New Hope',99435,2640.46,-12712.81 |
| 'Abreu','David','Brownsville','Mount Pleasant',189640,4026.11,-7711.87 |
| 'Abreu','David','Brownsville','White Oak',55423,2943.20,-5695.81 |
| 'Ackerman','NULL','Hopewell','Pleasant Grove',94468,5981.77,-11788.36 |
| 'Ackley','Christopher','Bethel','Newport',95923,2996.52,-7797.50 |
| 'Acosta','Bobby','Riverdale','Friendship',197899,60.50,-27188.42 |
| 'Acosta','Eric','Fairfield','Shiloh',11072,9.85,-4142.15 |
| 'Acosta','Johnnie','Centerville','Enterprise',69183,1581.96,-3015.22 |
| 'Acosta','Wilfred','Jackson','Macedonia',190054,316.43,-913.72 |
| 'Adair','Silvia','Bayview','Plainview',170324,4208.47,-9295.96 |
| 'Adams','Alicia','Concord','Mount Zion',22029,3132.36,-12262.13 |
| 'Adams','Carlena','Five Forks','Mount Olive',200385,220.23,-7642.46 |
| 'Adams','Christopher','Highland Park','New Hope',89344,66.03,-8711.35 |
| 'Adams','Christopher','Highland Park','Owens',150027,755.51,-5103.79 |
| 'Adams','David','Bridgeport','Antioch',213366,9174.05,-14025.32 |
| 'Adams','David','Bridgeport','Price',233836,1568.08,-20484.95 |
| 'Adams','Earl','Mount Zion','Greenfield',99055,551.98,-6440.04 |
| 'Adams','Henry','Saint James','Georgetown',231785,901.24,-18286.29 |
| 'Adams','Hortencia','Wildwood','Friendship',69694,296.89,-11931.45 |
| 'Adams','Isidro','Glenwood','Oak Grove',148351,923.30,-3177.00 |
| 'Adams','Jane','Shiloh','Red Hill',106214,1434.41,-7320.39 |
| 'Adams','Jay','Sulphur Springs','Bridgeport',195437,141.95,-6997.43 |
| 'Adams','Jim','Pleasant Valley','Edgewater',136554,4677.64,-8308.27 |
| 'Adams','Kimberly','Caledonia','Five Forks',105902,2112.44,-305.98 |
| 'Adams','Magdalena','Glendale','Edgewood',70321,163.95,-3472.48 |
| 'Adams','Patrick','Bridgeport','Edgewood',232741,523.33,-5830.63 |
| 'Adams','Pedro','Pleasant Valley','Enterprise',139637,2968.84,-5251.73 |
| 'Adams','Steven','Greenville','Hillcrest',13710,7235.33,-2345.79 |
| 'Adams','Tim','Mount Zion','Deerfield',188255,1942.53,-11899.84 |
| 'Adams','Tim','Mount Zion','Oak Grove',97273,3299.36,-16893.16 |
| 'Adams','Tonya','Enterprise','Oak Hill',143974,1025.42,-9619.17 |
| 'Adkins','Alica','Oakwood','Woodrow',131755,4255.95,-18319.64 |
| 'Adkins','Debra','Bloomingdale','Wayland',231738,4340.02,-15037.93 |
| 'Adkins','Debra','Helena','Deerfield',97428,1757.72,-4654.11 |
| 'Adkins','Judith','Plainview','Riverview',61339,5414.10,-15150.39 |
| 'Adkins','Melanie','Liberty','Riverside',41578,3471.30,-15110.65 |
| 'Adkins','Michael','Five Forks','Mount Pleasant',158714,3565.11,-14888.46 |
| 'Adkins','Virginia','Highland','Woodlawn',192086,0.00,-5100.88 |
| 'Aguilar','Arlene','Ashland','Belmont',132623,5624.93,-6045.24 |
| 'Aguilar','Douglas','Pine Grove','Brunswick',77899,166.17,-5251.06 |
| 'Aguilar','Karen','Pleasant Hill','Buena Vista',47133,1868.27,-6121.78 |
| 'Aguilar','Kimberly','Pleasant Hill','Jackson',192256,1.19,-7640.59 |
| 'Aguilar','Lynda','Greenwood','Pleasant Valley',51477,978.54,-11859.19 |
| 'Aguilar','Lynda','Greenwood','Woodrow',89360,803.59,-5283.17 |
| 'Aguilar','Milton','Mount Zion','Friendship',162299,5305.56,-7522.86 |
| 'Aguilar','Robert','Walnut Grove','Antioch',169539,2584.34,-5225.46 |
| 'Aguilar','Tracy','Highland Park','Kingston',206717,2995.45,-3976.20 |
| 'Aguilera','Michelle','Tipton','Shiloh',228010,3815.66,-11820.05 |
| 'Ahmed','Maureen','Oak Hill','Union Hill',128051,7270.41,-13433.60 |
| 'Aiken','James','Mount Olive','Crossroads',212463,0.00,-3126.87 |
| 'Akin','Sharon','Harmony','Edgewood',201392,2962.97,-14053.85 |
| 'Alaniz','Elia','Marion','Mount Zion',79719,3691.54,-10297.32 |
| 'Albert','Donald','Red Hill','Macedonia',83899,2140.15,-9622.92 |
| 'Albert','Jesse','Bunker Hill','Stringtown',139171,19429.35,-18276.98 |
| 'Albert','Joseph','Richfield','Brownsville',63910,5982.83,-9276.46 |
| 'Albert','Robert','Greenville','Royal',52927,0.00,-1929.68 |
| 'Albertson','Sarah','Pleasant Valley','White Oak',82675,2311.79,-11496.78 |
| 'Albright','Michelle','Hillcrest','Woodland',49024,6451.20,-12414.60 |
| 'Albright','Vincent','Wildwood','Oakdale',7278,4425.56,-25696.83 |
| 'Alcala','Nancy','Fairfield','Arlington',181927,711.70,-14419.56 |
| 'Aldrich','Stephen','Shady Grove','Post Oak',194020,4141.47,-15841.39 |
| 'Aldrich','Willard','Woodville','Arthur',11159,9577.24,1816.47 |
| 'Aldrich','NULL','Salem','Woodville',107876,638.97,-6506.27 |
| 'Aleman','Eddie','White Oak','Concord',25327,9293.14,-21425.24 |
| 'Alexander','Beverly','Valley View','Five Forks',212635,0.00,-5122.61 |
| 'Alexander','Donna','Liberty','Hamilton',218554,517.32,-10027.79 |
| 'Alexander','Graham','Fairfield','Newtown',104061,1574.22,-4375.65 |
| 'Alexander','Joseph','Winchester','Sutton',211390,6542.30,-20325.16 |
| 'Alexander','Nancy','Riverside','Union Hill',184734,7944.54,-12244.04 |
| 'Alexander','Paul','Springdale','Hopewell',190468,143.92,-9226.83 |
| 'Alfonso','Mabel','Clifton','Red Hill',54976,2422.52,3133.04 |
| 'Ali','Jennifer','Lake Forest','Liberty',40263,3467.53,-18495.55 |
| 'Ali','Stephen','Lone Pine','Glenwood',175860,167.14,-12116.78 |
| 'Allen','Andrea','Lakeside','Newport',11334,6.64,-2610.30 |
| 'Allen','Curtis','Brentwood','Richardson',216964,0.00,-19124.72 |
| 'Allen','Dana','Crossroads','Bunker Hill',112648,2164.34,-11973.83 |
| 'Allen','Donita','Riverdale','Sunnyside',182017,711.62,-3681.42 |
| 'Allen','Earl','Marion','Lincoln',64277,11935.26,-12108.31 |
| 'Allen','Edward','Brownsville','Bunker Hill',155950,3267.59,-7671.84 |
| 'Allen','Edward','Brownsville','Lakewood',162070,1359.35,-15806.88 |
| 'Allen','Emily','Macedonia','Springdale',186035,1860.88,-10258.69 |
| 'Allen','Jeanne','Waterloo','Summit',181784,448.50,-10244.62 |
| 'Allen','Jennifer','Green Acres','Five Points',194912,536.52,-18001.88 |
| 'Allen','John','Sulphur Springs','Five Forks',97314,463.68,-14328.20 |
| 'Allen','Leslie','Five Forks','Ashland',77055,87.92,-9644.24 |
| 'Allen','Mark','Lakeside','Riverside',191934,203.13,-19803.96 |
| 'Allen','Marty','Greenwood','Riverview',1040,308.20,-1547.30 |
| 'Allen','Matthew','Mount Pleasant','Mountain View',190723,2.77,-2219.34 |
| 'Allen','Natalie','Oak Grove','Woodville',82315,2230.76,-14635.09 |
| 'Allen','Robert','Oakdale','White Oak',92331,331.56,1122.69 |
| 'Allen','Roger','Jamestown','Springdale',84977,407.65,-2525.30 |
| 'Allen','Ronald','Wright','Pleasant Valley',8104,24.26,-1996.98 |
| ---- TYPES |
| STRING, STRING, STRING, STRING, BIGINT, DECIMAL, DECIMAL |
| ==== |
