| ==== |
| ---- QUERY: TPCDS-Q18A |
| with results as |
| (select i_item_id, |
| ca_country, |
| ca_state, |
| ca_county, |
| cast(cs_quantity as decimal(12,2)) agg1, |
| cast(cs_list_price as decimal(12,2)) agg2, |
| cast(cs_coupon_amt as decimal(12,2)) agg3, |
| cast(cs_sales_price as decimal(12,2)) agg4, |
| cast(cs_net_profit as decimal(12,2)) agg5, |
| cast(c_birth_year as decimal(12,2)) agg6, |
| cast(cd1.cd_dep_count as decimal(12,2)) agg7 |
| from catalog_sales, customer_demographics cd1, customer_demographics cd2, customer, customer_address, date_dim, item |
| where cs_sold_date_sk = d_date_sk and |
| cs_item_sk = i_item_sk and |
| cs_bill_cdemo_sk = cd1.cd_demo_sk and |
| cs_bill_customer_sk = c_customer_sk and |
| cd1.cd_gender = 'F' and |
| cd1.cd_education_status = 'Unknown' and |
| c_current_cdemo_sk = cd2.cd_demo_sk and |
| c_current_addr_sk = ca_address_sk and |
| c_birth_month in (1, 6, 8, 9, 12, 2) and |
| d_year = 1998 and |
| ca_state in ('MS', 'IN', 'ND', 'OK', 'NM', 'VA', 'MS') |
| ) |
| select i_item_id, ca_country, ca_state, ca_county, agg1, agg2, agg3, agg4, agg5, agg6, agg7 |
| from ( |
| select i_item_id, ca_country, ca_state, ca_county, avg(agg1) agg1, |
| avg(agg2) agg2, avg(agg3) agg3, avg(agg4) agg4, avg(agg5) agg5, avg(agg6) agg6, avg(agg7) agg7 |
| from results |
| group by i_item_id, ca_country, ca_state, ca_county |
| union all |
| select i_item_id, ca_country, ca_state, NULL as county, avg(agg1) agg1, avg(agg2) agg2, avg(agg3) agg3, |
| avg(agg4) agg4, avg(agg5) agg5, avg(agg6) agg6, avg(agg7) agg7 |
| from results |
| group by i_item_id, ca_country, ca_state |
| union all |
| select i_item_id, ca_country, NULL as ca_state, NULL as county, avg(agg1) agg1, avg(agg2) agg2, avg(agg3) agg3, |
| avg(agg4) agg4, avg(agg5) agg5, avg(agg6) agg6, avg(agg7) agg7 |
| from results |
| group by i_item_id, ca_country |
| union all |
| select i_item_id, NULL as ca_country, NULL as ca_state, NULL as county, avg(agg1) agg1, avg(agg2) agg2, avg(agg3) agg3, |
| avg(agg4) agg4, avg(agg5) agg5, avg(agg6) agg6, avg(agg7) agg7 |
| from results |
| group by i_item_id |
| union all |
| select NULL AS i_item_id, NULL as ca_country, NULL as ca_state, NULL as county, avg(agg1) agg1, avg(agg2) agg2, avg(agg3) agg3, |
| avg(agg4) agg4, avg(agg5) agg5, avg(agg6) agg6, avg(agg7) agg7 |
| from results |
| ) foo |
| order by ca_country, ca_state, ca_county, i_item_id |
| limit 100; |
| ---- RESULTS |
| 'AAAAAAAABFBAAAAA','United States','IN','Adams County',8.00,86.17,0.00,85.30,415.20,1981.00,2.00 |
| 'AAAAAAAADMKDAAAA','United States','IN','Adams County',99.00,29.12,0.00,11.06,-562.32,1981.00,2.00 |
| 'AAAAAAAAEGDAAAAA','United States','IN','Adams County',70.00,51.12,0.00,34.25,1110.20,1981.00,2.00 |
| 'AAAAAAAAGHGAAAAA','United States','IN','Adams County',90.00,73.47,0.00,36.73,-1318.50,1981.00,2.00 |
| 'AAAAAAAAKBCBAAAA','United States','IN','Adams County',35.00,46.23,973.00,28.66,-682.85,1981.00,2.00 |
| 'AAAAAAAACCJAAAAA','United States','IN','Cass County',67.00,55.73,679.48,36.22,-14.17,1972.00,1.00 |
| 'AAAAAAAACIMBAAAA','United States','IN','Cass County',51.00,21.90,294.86,17.52,-86.78,1972.00,1.00 |
| 'AAAAAAAADJPBAAAA','United States','IN','Cass County',88.00,51.59,0.00,49.01,2697.20,1972.00,1.00 |
| 'AAAAAAAAHJDDAAAA','United States','IN','Cass County',77.00,38.31,0.00,32.94,-300.30,1972.00,1.00 |
| 'AAAAAAAANFAEAAAA','United States','IN','Cass County',46.00,110.69,0.00,47.59,-1474.30,1972.00,1.00 |
| 'AAAAAAAAOLJDAAAA','United States','IN','Cass County',59.00,239.30,0.00,50.25,-2918.14,1972.00,1.00 |
| 'AAAAAAAACEGCAAAA','United States','IN','Clark County',42.00,24.85,0.00,13.66,-188.16,1975.00,3.00 |
| 'AAAAAAAACLPDAAAA','United States','IN','Clark County',1.00,61.08,0.00,0.61,-23.16,1975.00,3.00 |
| 'AAAAAAAAGLDAAAAA','United States','IN','Clark County',11.00,204.81,0.00,12.28,-963.93,1975.00,3.00 |
| 'AAAAAAAAJPKAAAAA','United States','IN','Clark County',52.00,11.51,201.45,5.87,-240.45,1975.00,3.00 |
| 'AAAAAAAALJCDAAAA','United States','IN','Clark County',83.00,101.54,684.18,29.44,-3002.37,1975.00,3.00 |
| 'AAAAAAAACLICAAAA','United States','IN','Crawford County',51.00,126.57,0.00,78.47,1663.11,1930.00,4.00 |
| 'AAAAAAAAJEDAAAAA','United States','IN','Crawford County',83.00,78.26,6237.52,75.91,-5952.00,1930.00,4.00 |
| 'AAAAAAAALJFAAAAA','United States','IN','Crawford County',82.00,94.10,0.00,34.81,-4637.10,1930.00,4.00 |
| 'AAAAAAAAODJAAAAA','United States','IN','Crawford County',49.00,151.09,0.00,15.10,-2198.14,1930.00,4.00 |
| 'AAAAAAAAOPBBAAAA','United States','IN','Crawford County',73.00,162.05,0.00,71.30,839.50,1930.00,4.00 |
| 'AAAAAAAAAGCDAAAA','United States','IN','De Kalb County',15.00,33.56,0.00,29.53,191.25,1963.00,5.00 |
| 'AAAAAAAAALBCAAAA','United States','IN','De Kalb County',3.00,43.18,0.00,5.18,-39.12,1963.00,5.00 |
| 'AAAAAAAACICCAAAA','United States','IN','De Kalb County',62.00,111.50,0.00,66.90,-1050.28,1963.00,5.00 |
| 'AAAAAAAADFIDAAAA','United States','IN','De Kalb County',75.00,277.95,0.00,150.09,3811.50,1963.00,5.00 |
| 'AAAAAAAAGDDBAAAA','United States','IN','De Kalb County',17.00,102.12,0.00,30.63,-438.43,1963.00,5.00 |
| 'AAAAAAAAGDDCAAAA','United States','IN','De Kalb County',72.00,7.99,404.35,7.91,-58.75,1963.00,5.00 |
| 'AAAAAAAAIHLCAAAA','United States','IN','De Kalb County',76.00,234.20,0.00,201.41,8271.84,1963.00,5.00 |
| 'AAAAAAAAILCBAAAA','United States','IN','De Kalb County',92.00,87.33,0.00,3.49,-2781.16,1963.00,5.00 |
| 'AAAAAAAAJIDCAAAA','United States','IN','De Kalb County',1.00,82.20,0.00,62.47,-4.36,1963.00,5.00 |
| 'AAAAAAAAACBCAAAA','United States','IN','Dearborn County',1.00,77.53,0.00,49.61,-1.40,1982.00,0.00 |
| 'AAAAAAAACOHCAAAA','United States','IN','Dearborn County',53.00,76.54,258.73,22.19,-2407.88,1982.00,0.00 |
| 'AAAAAAAAFBBAAAAA','United States','IN','Dearborn County',66.00,121.25,0.00,54.56,-524.04,1982.00,0.00 |
| 'AAAAAAAAFIBCAAAA','United States','IN','Dearborn County',26.00,38.28,0.00,25.64,222.30,1982.00,0.00 |
| 'AAAAAAAAILEDAAAA','United States','IN','Dearborn County',96.00,213.31,491.32,17.06,-7995.64,1982.00,0.00 |
| 'AAAAAAAAKIKDAAAA','United States','IN','Dearborn County',96.00,168.66,0.00,123.12,4840.32,1982.00,0.00 |
| 'AAAAAAAAKPEDAAAA','United States','IN','Dearborn County',1.00,103.55,10.25,93.19,40.15,1982.00,0.00 |
| 'AAAAAAAAMFKBAAAA','United States','IN','Dearborn County',79.00,98.01,0.00,81.34,2217.53,1982.00,0.00 |
| 'AAAAAAAAMFOBAAAA','United States','IN','Dearborn County',10.00,203.77,90.06,52.98,-293.26,1982.00,0.00 |
| 'AAAAAAAAMLIBAAAA','United States','IN','Dearborn County',37.00,48.54,0.00,0.00,-1350.50,1982.00,0.00 |
| 'AAAAAAAAMOIAAAAA','United States','IN','Dearborn County',60.00,173.07,1623.98,58.84,-1578.38,1982.00,0.00 |
| 'AAAAAAAAONBAAAAA','United States','IN','Dearborn County',14.00,17.24,0.00,1.37,-114.24,1982.00,0.00 |
| 'AAAAAAAAPFEBAAAA','United States','IN','Dearborn County',35.00,141.77,0.00,89.31,920.50,1982.00,0.00 |
| 'AAAAAAAAAEJBAAAA','United States','IN','Fayette County',76.00,85.66,0.00,17.13,-4670.96,1985.00,2.00 |
| 'AAAAAAAAALIBAAAA','United States','IN','Fayette County',53.00,170.42,3689.47,161.89,-45.19,1985.00,2.00 |
| 'AAAAAAAACDADAAAA','United States','IN','Fayette County',43.00,45.12,0.00,34.29,774.00,1985.00,2.00 |
| 'AAAAAAAADKDAAAAA','United States','IN','Fayette County',75.00,40.58,0.00,38.14,606.00,1985.00,2.00 |
| 'AAAAAAAAGCBCAAAA','United States','IN','Fayette County',50.00,118.21,648.90,72.10,-1849.40,1985.00,2.00 |
| 'AAAAAAAAGKOBAAAA','United States','IN','Fayette County',2.00,85.08,0.00,45.09,-42.76,1985.00,2.00 |
| 'AAAAAAAAGMLAAAAA','United States','IN','Fayette County',7.00,28.73,0.00,24.42,62.79,1985.00,2.00 |
| 'AAAAAAAAKNODAAAA','United States','IN','Fayette County',97.00,153.25,227.41,78.15,-554.30,1985.00,2.00 |
| 'AAAAAAAAKOPBAAAA','United States','IN','Fayette County',28.00,67.69,1021.31,37.22,-1520.27,1985.00,2.00 |
| 'AAAAAAAAMJJCAAAA','United States','IN','Fayette County',67.00,25.67,0.00,21.81,775.86,1985.00,2.00 |
| 'AAAAAAAANAHCAAAA','United States','IN','Fayette County',55.00,45.89,368.94,7.80,-844.69,1985.00,2.00 |
| 'AAAAAAAAAOFDAAAA','United States','IN','Fountain County',87.00,28.71,0.00,27.84,1472.04,1968.00,6.00 |
| 'AAAAAAAACCGBAAAA','United States','IN','Fountain County',85.00,44.48,0.00,30.24,15.30,1968.00,6.00 |
| 'AAAAAAAAIIADAAAA','United States','IN','Fountain County',78.00,118.17,0.00,115.80,3411.72,1968.00,6.00 |
| 'AAAAAAAAOBDBAAAA','United States','IN','Fountain County',65.00,188.63,0.00,165.99,5481.45,1968.00,6.00 |
| 'AAAAAAAAAEABAAAA','United States','IN','Hamilton County',32.00,29.89,0.00,28.39,391.36,1932.00,5.00 |
| 'AAAAAAAAAEFCAAAA','United States','IN','Hamilton County',32.00,31.97,0.00,18.54,65.92,1932.00,5.00 |
| 'AAAAAAAAAMNDAAAA','United States','IN','Hamilton County',70.00,101.27,4284.88,65.82,-4341.58,1932.00,5.00 |
| 'AAAAAAAAEPKCAAAA','United States','IN','Hamilton County',35.00,128.90,0.00,105.69,2021.95,1932.00,5.00 |
| 'AAAAAAAAGIMAAAAA','United States','IN','Hamilton County',79.00,12.06,326.71,8.44,-118.15,1932.00,5.00 |
| 'AAAAAAAAGKFBAAAA','United States','IN','Hamilton County',43.00,23.85,0.00,0.95,-580.93,1932.00,5.00 |
| 'AAAAAAAAIFICAAAA','United States','IN','Hamilton County',86.00,2.45,0.00,1.98,48.16,1932.00,5.00 |
| 'AAAAAAAACHNDAAAA','United States','IN','Jennings County',36.00,2.87,0.00,0.11,-49.32,1958.00,2.00 |
| 'AAAAAAAADBECAAAA','United States','IN','Jennings County',59.00,89.31,775.52,57.15,747.27,1958.00,2.00 |
| 'AAAAAAAAIAHBAAAA','United States','IN','Jennings County',2.00,42.43,0.00,22.06,-12.46,1958.00,2.00 |
| 'AAAAAAAAICDEAAAA','United States','IN','Jennings County',17.00,146.85,0.00,41.11,-524.96,1958.00,2.00 |
| 'AAAAAAAANDMAAAAA','United States','IN','Jennings County',32.00,238.00,637.45,21.42,-2870.09,1958.00,2.00 |
| 'AAAAAAAAPNBCAAAA','United States','IN','Jennings County',59.00,230.88,0.00,223.95,7465.27,1958.00,2.00 |
| 'AAAAAAAAAICBAAAA','United States','IN','Johnson County',59.00,156.86,0.00,37.64,-1144.60,1924.00,2.00 |
| 'AAAAAAAACGJAAAAA','United States','IN','Johnson County',41.00,111.54,0.00,72.50,1333.32,1924.00,2.00 |
| 'AAAAAAAAHNPCAAAA','United States','IN','Johnson County',5.00,229.90,885.99,188.51,-432.59,1924.00,2.00 |
| 'AAAAAAAAKBBCAAAA','United States','IN','Johnson County',80.00,105.66,0.00,73.96,-344.80,1924.00,2.00 |
| 'AAAAAAAAKMEDAAAA','United States','IN','Johnson County',11.00,81.28,0.00,27.63,-343.97,1924.00,2.00 |
| 'AAAAAAAAKNCEAAAA','United States','IN','Johnson County',68.00,100.51,0.00,18.09,-4713.08,1924.00,2.00 |
| 'AAAAAAAADAEDAAAA','United States','IN','Kosciusko County',84.00,68.84,464.19,7.57,-2622.15,1987.00,1.00 |
| 'AAAAAAAAENDAAAAA','United States','IN','Kosciusko County',99.00,142.12,679.55,98.06,3855.64,1987.00,1.00 |
| 'AAAAAAAAHHFAAAAA','United States','IN','Kosciusko County',59.00,74.54,0.00,64.84,2237.87,1987.00,1.00 |
| 'AAAAAAAAHKHBAAAA','United States','IN','Kosciusko County',16.00,58.20,0.00,27.35,-97.60,1987.00,1.00 |
| 'AAAAAAAAIFBCAAAA','United States','IN','Kosciusko County',44.00,69.88,0.00,69.88,1464.76,1987.00,1.00 |
| 'AAAAAAAAKCLDAAAA','United States','IN','Kosciusko County',53.00,171.98,0.00,29.23,-1997.57,1987.00,1.00 |
| 'AAAAAAAAKHCEAAAA','United States','IN','Kosciusko County',75.00,62.04,0.00,32.88,369.75,1987.00,1.00 |
| 'AAAAAAAALOFBAAAA','United States','IN','Kosciusko County',49.00,95.19,0.00,77.10,2135.42,1987.00,1.00 |
| 'AAAAAAAAMMIAAAAA','United States','IN','Kosciusko County',56.00,12.78,0.00,12.01,345.52,1987.00,1.00 |
| 'AAAAAAAAPHADAAAA','United States','IN','Kosciusko County',19.00,89.15,0.00,1.78,-638.40,1987.00,1.00 |
| 'AAAAAAAAAJAEAAAA','United States','IN','La Porte County',56.00,84.85,0.00,6.78,-2990.40,1975.00,4.00 |
| 'AAAAAAAAALCBAAAA','United States','IN','La Porte County',61.00,80.16,0.00,26.45,-1646.39,1975.00,4.00 |
| 'AAAAAAAACDDCAAAA','United States','IN','La Porte County',31.00,129.15,0.00,38.74,-1182.34,1975.00,4.00 |
| 'AAAAAAAACDPDAAAA','United States','IN','La Porte County',23.00,54.41,282.09,22.30,-343.27,1975.00,4.00 |
| 'AAAAAAAADFEEAAAA','United States','IN','La Porte County',52.00,126.01,0.00,41.58,-829.92,1975.00,4.00 |
| 'AAAAAAAAEPJDAAAA','United States','IN','La Porte County',38.00,63.13,0.00,46.08,859.18,1975.00,4.00 |
| 'AAAAAAAAIEDBAAAA','United States','IN','La Porte County',13.00,50.53,0.00,31.32,-52.26,1975.00,4.00 |
| 'AAAAAAAAIOAEAAAA','United States','IN','La Porte County',10.00,53.35,0.00,33.07,122.30,1975.00,4.00 |
| 'AAAAAAAAJKMAAAAA','United States','IN','La Porte County',50.00,66.92,0.00,16.73,-749.50,1975.00,4.00 |
| 'AAAAAAAAKDBAAAAA','United States','IN','La Porte County',57.00,123.53,0.00,81.52,959.88,1975.00,4.00 |
| 'AAAAAAAAKIDBAAAA','United States','IN','La Porte County',38.00,50.20,0.00,40.66,-307.04,1975.00,4.00 |
| 'AAAAAAAALHDEAAAA','United States','IN','La Porte County',72.00,147.38,0.00,26.52,-3985.92,1975.00,4.00 |
| 'AAAAAAAALKEAAAAA','United States','IN','La Porte County',68.00,159.12,0.00,77.96,-709.92,1975.00,4.00 |
| ---- TYPES |
| STRING, STRING, STRING, STRING, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL |
| ==== |
