| ==== |
| ---- QUERY: TPCDS-Q79 |
| select |
| c_last_name, |
| c_first_name, |
| substr(s_city, 1, 30), |
| ss_ticket_number, |
| amt, |
| profit |
| from |
| (select |
| ss_ticket_number, |
| ss_customer_sk, |
| store.s_city, |
| sum(ss_coupon_amt) amt, |
| sum(ss_net_profit) profit |
| from |
| store_sales, |
| date_dim, |
| store, |
| household_demographics |
| 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 (household_demographics.hd_dep_count = 6 |
| or household_demographics.hd_vehicle_count > 2) |
| and date_dim.d_dow = 1 |
| and date_dim.d_year in (1999, 1999 + 1, 1999 + 2) |
| and store.s_number_employees between 200 and 295 |
| group by |
| ss_ticket_number, |
| ss_customer_sk, |
| ss_addr_sk, |
| store.s_city |
| ) ms, |
| customer |
| where |
| ss_customer_sk = c_customer_sk |
| order by |
| c_last_name, |
| c_first_name, |
| substr(s_city, 1, 30), |
| profit |
| limit 100; |
| ---- RESULTS |
| 'Aaron','Guy','Midway',30633,8423.48,-7612.67 |
| 'Abbott','James','Midway',19069,11829.58,-14596.44 |
| 'Abbott','Robert','Midway',108183,2094.22,-11543.20 |
| 'Abell','Tonya','Midway',11487,17.71,-7179.35 |
| 'Abernathy','Verda','Midway',91981,2187.38,-6913.16 |
| 'Abner','Eula','Midway',120315,1123.14,-10293.09 |
| 'Abney','Anthony','Fairview',211863,299.87,-5224.94 |
| 'Abraham','Michael','Fairview',101213,2759.54,-14160.33 |
| 'Abraham','Robert','Midway',172751,122.64,-4104.67 |
| 'Abraham','Travis','Midway',68579,1803.49,-10272.59 |
| 'Abrams','Fannie','Midway',177397,222.18,-2529.63 |
| 'Abreu','Jeffrey','Midway',188029,349.62,-8170.17 |
| 'Acevedo','David','Midway',124592,550.41,-18324.80 |
| 'Acevedo','Ellen','Midway',48390,0.00,-8873.13 |
| 'Acevedo','Grace','Midway',103952,4245.63,-10161.12 |
| 'Ackerman','Jeffrey','Midway',198943,977.78,-6460.69 |
| 'Ackerman','Jeffrey','Midway',19260,3865.35,-1451.04 |
| 'Adair','Evan','Fairview',117449,543.02,-2845.37 |
| 'Adam','Paul','Fairview',161844,100.48,-13979.58 |
| 'Adam','Paul','Fairview',161844,0.00,75.25 |
| 'Adams','Beth','Midway',73934,2148.45,-9147.51 |
| 'Adams','Betty','Midway',83966,3471.23,-11418.81 |
| 'Adams','Dan','Midway',57271,4235.99,-9850.80 |
| 'Adams','George','Midway',162011,7111.36,-23409.35 |
| 'Adams','John','Midway',229336,342.06,-3441.34 |
| 'Adams','Michael','Midway',37967,1645.84,-8363.98 |
| 'Adams','Roy','Midway',164196,0.00,-10557.40 |
| 'Adams','Tony','Midway',184799,0.00,-10143.02 |
| 'Adkins','Evelyn','Midway',210368,14.36,-5619.27 |
| 'Adkins','Kathleen','Midway',58726,8710.56,-16195.45 |
| 'Adler','Charles','Midway',22973,0.00,-14430.54 |
| 'Adler','Julian','Midway',221567,0.00,-7158.29 |
| 'Agee','Anthony','Midway',90993,2119.00,-25648.52 |
| 'Agee','Dora','Midway',217452,2097.93,-7874.59 |
| 'Agnew','Elizabeth','Midway',92724,0.00,-585.34 |
| 'Aguayo','Edwin','Midway',45528,0.00,-12991.97 |
| 'Aguayo','John','Fairview',156381,538.39,444.42 |
| 'Aguilar','Roxane','Midway',6855,2276.72,-8075.22 |
| 'Aguilera','Gladys','Midway',30412,638.21,-3212.77 |
| 'Ahern','Christine','Midway',872,2513.29,-18806.37 |
| 'Ainsworth','John','Midway',12751,31.00,-16667.57 |
| 'Akin','Sharon','Midway',11249,147.14,-6157.31 |
| 'Alarcon','George','Midway',166192,1807.82,-678.04 |
| 'Alarcon','Hazel','Midway',124475,31.54,-1761.42 |
| 'Alarcon','Victoria','Midway',228661,459.41,-3937.68 |
| 'Albert','Jaime','Midway',152168,2869.09,-7283.22 |
| 'Albert','Jeffrey','Midway',238250,1927.20,-9322.37 |
| 'Albright','Helen','Midway',59549,86.16,-6275.12 |
| 'Aldrich','Evelyn','Midway',91580,1806.08,-5265.75 |
| 'Aldridge','Nathanial','Midway',20206,154.62,-4794.76 |
| 'Alexander','Annie','Midway',72228,798.98,-7804.33 |
| 'Alexander','Eddie','Midway',69977,0.00,-9618.25 |
| 'Alexander','Kristen','Midway',55155,1034.24,-8816.85 |
| 'Alexander','Victor','Midway',184075,4566.50,-16164.13 |
| 'Alfonso','Paul','Midway',224079,2598.57,-3747.90 |
| 'Alford','Roberta','Midway',15770,5271.15,-7286.77 |
| 'Ali','Henry','Midway',72418,231.31,-6663.34 |
| 'Alicea','Ramona','Midway',142193,601.85,-10567.32 |
| 'Allard','John','Midway',20098,1.45,-16478.51 |
| 'Allen','Anna','Midway',175145,21.61,-9271.32 |
| 'Allen','Bill','Fairview',144210,351.66,-5655.63 |
| 'Allen','Colleen','Midway',33031,4941.91,-7217.35 |
| 'Allen','David','Fairview',46354,2987.79,-8400.65 |
| 'Allen','Dianna','Midway',72135,3000.83,-5229.79 |
| 'Allen','Earnestine','Midway',159660,1533.49,-16606.99 |
| 'Allen','Earnestine','Midway',159660,NULL,NULL |
| 'Allen','Florence','Midway',6542,5405.58,-10287.74 |
| 'Allen','Fred','Midway',122737,1188.63,-92.29 |
| 'Allen','Janice','Midway',199800,11.54,-7276.73 |
| 'Allen','Jason','Midway',111015,1074.54,-14891.59 |
| 'Allen','John','Fairview',207020,3598.03,-13469.52 |
| 'Allen','Lena','Midway',187155,10857.02,-12205.46 |
| 'Allen','Leslie','Midway',11150,0.00,-10958.34 |
| 'Allen','Marcia','Midway',190763,1661.52,-14020.72 |
| 'Allen','Michael','Midway',143187,2446.13,-6376.82 |
| 'Allen','Michael','Midway',43014,1774.16,-5814.87 |
| 'Allen','Rocky','Midway',33844,2417.09,-7084.09 |
| 'Allen','Ronald','Midway',80879,1836.27,-9603.40 |
| 'Allen','Sarah','Midway',148147,3530.75,-17432.64 |
| 'Allen','Teresita','Midway',2008,628.35,-5026.60 |
| 'Allen','Teresita','Midway',2008,447.93,-2761.02 |
| 'Allen','NULL','Midway',43827,844.35,-7012.34 |
| 'Allison','Norma','Midway',82553,1922.94,-9307.16 |
| 'Allison','Peggie','Midway',146780,6966.19,-7455.22 |
| 'Allred','Deborah','Midway',193501,1832.66,-11177.35 |
| 'Almeida','David','Fairview',168589,1172.09,-9778.60 |
| 'Alonso','Anthony','Midway',208514,4090.00,-20066.44 |
| 'Alonzo','Troy','Fairview',54936,0.00,-3939.58 |
| 'Alston','Jenny','Midway',127544,3431.78,-6602.81 |
| 'Alston','Ryan','Midway',214797,297.62,-5349.93 |
| 'Altman','Alfred','Fairview',140764,7591.92,-12948.64 |
| 'Alvarado','Richard','Fairview',81704,1547.83,-8473.98 |
| 'Alvarez','Ana','Midway',225521,0.00,-8431.41 |
| 'Alvarez','Eric','Midway',226554,0.00,-3912.23 |
| 'Alvarez','Jennifer','Midway',119831,7409.63,-10587.25 |
| 'Amador','Wilbur','Midway',225632,1355.65,-4691.06 |
| 'Amaya','Christina','Midway',27102,290.84,-10993.57 |
| 'Ambrose','Robert','Midway',91578,247.07,-6173.40 |
| 'Ames','Perry','Fairview',5862,3975.41,-1347.02 |
| 'Amundson','Ernest','Midway',228272,8218.46,-4039.62 |
| ---- TYPES |
| STRING, STRING, STRING, BIGINT, DECIMAL, DECIMAL |
| ==== |
