====
---- 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
====