| ==== |
| ---- 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.000000,86.170000,0.000000,85.300000,415.200000,1981.000000,2.000000 |
| 'AAAAAAAADMKDAAAA','United States','IN','Adams County',99.000000,29.120000,0.000000,11.060000,-562.320000,1981.000000,2.000000 |
| 'AAAAAAAAEGDAAAAA','United States','IN','Adams County',70.000000,51.120000,0.000000,34.250000,1110.200000,1981.000000,2.000000 |
| 'AAAAAAAAGHGAAAAA','United States','IN','Adams County',90.000000,73.470000,0.000000,36.730000,-1318.500000,1981.000000,2.000000 |
| 'AAAAAAAAKBCBAAAA','United States','IN','Adams County',35.000000,46.230000,973.000000,28.660000,-682.850000,1981.000000,2.000000 |
| 'AAAAAAAACCJAAAAA','United States','IN','Cass County',67.000000,55.730000,679.480000,36.220000,-14.170000,1972.000000,1.000000 |
| 'AAAAAAAACIMBAAAA','United States','IN','Cass County',51.000000,21.900000,294.860000,17.520000,-86.780000,1972.000000,1.000000 |
| 'AAAAAAAADJPBAAAA','United States','IN','Cass County',88.000000,51.590000,0.000000,49.010000,2697.200000,1972.000000,1.000000 |
| 'AAAAAAAAHJDDAAAA','United States','IN','Cass County',77.000000,38.310000,0.000000,32.940000,-300.300000,1972.000000,1.000000 |
| 'AAAAAAAANFAEAAAA','United States','IN','Cass County',46.000000,110.690000,0.000000,47.590000,-1474.300000,1972.000000,1.000000 |
| 'AAAAAAAAOLJDAAAA','United States','IN','Cass County',59.000000,239.300000,0.000000,50.250000,-2918.140000,1972.000000,1.000000 |
| 'AAAAAAAACEGCAAAA','United States','IN','Clark County',42.000000,24.850000,0.000000,13.660000,-188.160000,1975.000000,3.000000 |
| 'AAAAAAAACLPDAAAA','United States','IN','Clark County',1.000000,61.080000,0.000000,0.610000,-23.160000,1975.000000,3.000000 |
| 'AAAAAAAAGLDAAAAA','United States','IN','Clark County',11.000000,204.810000,0.000000,12.280000,-963.930000,1975.000000,3.000000 |
| 'AAAAAAAAJPKAAAAA','United States','IN','Clark County',52.000000,11.510000,201.450000,5.870000,-240.450000,1975.000000,3.000000 |
| 'AAAAAAAALJCDAAAA','United States','IN','Clark County',83.000000,101.540000,684.180000,29.440000,-3002.370000,1975.000000,3.000000 |
| 'AAAAAAAACLICAAAA','United States','IN','Crawford County',51.000000,126.570000,0.000000,78.470000,1663.110000,1930.000000,4.000000 |
| 'AAAAAAAAJEDAAAAA','United States','IN','Crawford County',83.000000,78.260000,6237.520000,75.910000,-5952.000000,1930.000000,4.000000 |
| 'AAAAAAAALJFAAAAA','United States','IN','Crawford County',82.000000,94.100000,0.000000,34.810000,-4637.100000,1930.000000,4.000000 |
| 'AAAAAAAAODJAAAAA','United States','IN','Crawford County',49.000000,151.090000,0.000000,15.100000,-2198.140000,1930.000000,4.000000 |
| 'AAAAAAAAOPBBAAAA','United States','IN','Crawford County',73.000000,162.050000,0.000000,71.300000,839.500000,1930.000000,4.000000 |
| 'AAAAAAAAAGCDAAAA','United States','IN','De Kalb County',15.000000,33.560000,0.000000,29.530000,191.250000,1963.000000,5.000000 |
| 'AAAAAAAAALBCAAAA','United States','IN','De Kalb County',3.000000,43.180000,0.000000,5.180000,-39.120000,1963.000000,5.000000 |
| 'AAAAAAAACICCAAAA','United States','IN','De Kalb County',62.000000,111.500000,0.000000,66.900000,-1050.280000,1963.000000,5.000000 |
| 'AAAAAAAADFIDAAAA','United States','IN','De Kalb County',75.000000,277.950000,0.000000,150.090000,3811.500000,1963.000000,5.000000 |
| 'AAAAAAAAGDDBAAAA','United States','IN','De Kalb County',17.000000,102.120000,0.000000,30.630000,-438.430000,1963.000000,5.000000 |
| 'AAAAAAAAGDDCAAAA','United States','IN','De Kalb County',72.000000,7.990000,404.350000,7.910000,-58.750000,1963.000000,5.000000 |
| 'AAAAAAAAIHLCAAAA','United States','IN','De Kalb County',76.000000,234.200000,0.000000,201.410000,8271.840000,1963.000000,5.000000 |
| 'AAAAAAAAILCBAAAA','United States','IN','De Kalb County',92.000000,87.330000,0.000000,3.490000,-2781.160000,1963.000000,5.000000 |
| 'AAAAAAAAJIDCAAAA','United States','IN','De Kalb County',1.000000,82.200000,0.000000,62.470000,-4.360000,1963.000000,5.000000 |
| 'AAAAAAAAACBCAAAA','United States','IN','Dearborn County',1.000000,77.530000,0.000000,49.610000,-1.400000,1982.000000,0.000000 |
| 'AAAAAAAACOHCAAAA','United States','IN','Dearborn County',53.000000,76.540000,258.730000,22.190000,-2407.880000,1982.000000,0.000000 |
| 'AAAAAAAAFBBAAAAA','United States','IN','Dearborn County',66.000000,121.250000,0.000000,54.560000,-524.040000,1982.000000,0.000000 |
| 'AAAAAAAAFIBCAAAA','United States','IN','Dearborn County',26.000000,38.280000,0.000000,25.640000,222.300000,1982.000000,0.000000 |
| 'AAAAAAAAILEDAAAA','United States','IN','Dearborn County',96.000000,213.310000,491.320000,17.060000,-7995.640000,1982.000000,0.000000 |
| 'AAAAAAAAKIKDAAAA','United States','IN','Dearborn County',96.000000,168.660000,0.000000,123.120000,4840.320000,1982.000000,0.000000 |
| 'AAAAAAAAKPEDAAAA','United States','IN','Dearborn County',1.000000,103.550000,10.250000,93.190000,40.150000,1982.000000,0.000000 |
| 'AAAAAAAAMFKBAAAA','United States','IN','Dearborn County',79.000000,98.010000,0.000000,81.340000,2217.530000,1982.000000,0.000000 |
| 'AAAAAAAAMFOBAAAA','United States','IN','Dearborn County',10.000000,203.770000,90.060000,52.980000,-293.260000,1982.000000,0.000000 |
| 'AAAAAAAAMLIBAAAA','United States','IN','Dearborn County',37.000000,48.540000,0.000000,0.000000,-1350.500000,1982.000000,0.000000 |
| 'AAAAAAAAMOIAAAAA','United States','IN','Dearborn County',60.000000,173.070000,1623.980000,58.840000,-1578.380000,1982.000000,0.000000 |
| 'AAAAAAAAONBAAAAA','United States','IN','Dearborn County',14.000000,17.240000,0.000000,1.370000,-114.240000,1982.000000,0.000000 |
| 'AAAAAAAAPFEBAAAA','United States','IN','Dearborn County',35.000000,141.770000,0.000000,89.310000,920.500000,1982.000000,0.000000 |
| 'AAAAAAAAAEJBAAAA','United States','IN','Fayette County',76.000000,85.660000,0.000000,17.130000,-4670.960000,1985.000000,2.000000 |
| 'AAAAAAAAALIBAAAA','United States','IN','Fayette County',53.000000,170.420000,3689.470000,161.890000,-45.190000,1985.000000,2.000000 |
| 'AAAAAAAACDADAAAA','United States','IN','Fayette County',43.000000,45.120000,0.000000,34.290000,774.000000,1985.000000,2.000000 |
| 'AAAAAAAADKDAAAAA','United States','IN','Fayette County',75.000000,40.580000,0.000000,38.140000,606.000000,1985.000000,2.000000 |
| 'AAAAAAAAGCBCAAAA','United States','IN','Fayette County',50.000000,118.210000,648.900000,72.100000,-1849.400000,1985.000000,2.000000 |
| 'AAAAAAAAGKOBAAAA','United States','IN','Fayette County',2.000000,85.080000,0.000000,45.090000,-42.760000,1985.000000,2.000000 |
| 'AAAAAAAAGMLAAAAA','United States','IN','Fayette County',7.000000,28.730000,0.000000,24.420000,62.790000,1985.000000,2.000000 |
| 'AAAAAAAAKNODAAAA','United States','IN','Fayette County',97.000000,153.250000,227.410000,78.150000,-554.300000,1985.000000,2.000000 |
| 'AAAAAAAAKOPBAAAA','United States','IN','Fayette County',28.000000,67.690000,1021.310000,37.220000,-1520.270000,1985.000000,2.000000 |
| 'AAAAAAAAMJJCAAAA','United States','IN','Fayette County',67.000000,25.670000,0.000000,21.810000,775.860000,1985.000000,2.000000 |
| 'AAAAAAAANAHCAAAA','United States','IN','Fayette County',55.000000,45.890000,368.940000,7.800000,-844.690000,1985.000000,2.000000 |
| 'AAAAAAAAAOFDAAAA','United States','IN','Fountain County',87.000000,28.710000,0.000000,27.840000,1472.040000,1968.000000,6.000000 |
| 'AAAAAAAACCGBAAAA','United States','IN','Fountain County',85.000000,44.480000,0.000000,30.240000,15.300000,1968.000000,6.000000 |
| 'AAAAAAAAIIADAAAA','United States','IN','Fountain County',78.000000,118.170000,0.000000,115.800000,3411.720000,1968.000000,6.000000 |
| 'AAAAAAAAOBDBAAAA','United States','IN','Fountain County',65.000000,188.630000,0.000000,165.990000,5481.450000,1968.000000,6.000000 |
| 'AAAAAAAAAEABAAAA','United States','IN','Hamilton County',32.000000,29.890000,0.000000,28.390000,391.360000,1932.000000,5.000000 |
| 'AAAAAAAAAEFCAAAA','United States','IN','Hamilton County',32.000000,31.970000,0.000000,18.540000,65.920000,1932.000000,5.000000 |
| 'AAAAAAAAAMNDAAAA','United States','IN','Hamilton County',70.000000,101.270000,4284.880000,65.820000,-4341.580000,1932.000000,5.000000 |
| 'AAAAAAAAEPKCAAAA','United States','IN','Hamilton County',35.000000,128.900000,0.000000,105.690000,2021.950000,1932.000000,5.000000 |
| 'AAAAAAAAGIMAAAAA','United States','IN','Hamilton County',79.000000,12.060000,326.710000,8.440000,-118.150000,1932.000000,5.000000 |
| 'AAAAAAAAGKFBAAAA','United States','IN','Hamilton County',43.000000,23.850000,0.000000,0.950000,-580.930000,1932.000000,5.000000 |
| 'AAAAAAAAIFICAAAA','United States','IN','Hamilton County',86.000000,2.450000,0.000000,1.980000,48.160000,1932.000000,5.000000 |
| 'AAAAAAAACHNDAAAA','United States','IN','Jennings County',36.000000,2.870000,0.000000,0.110000,-49.320000,1958.000000,2.000000 |
| 'AAAAAAAADBECAAAA','United States','IN','Jennings County',59.000000,89.310000,775.520000,57.150000,747.270000,1958.000000,2.000000 |
| 'AAAAAAAAIAHBAAAA','United States','IN','Jennings County',2.000000,42.430000,0.000000,22.060000,-12.460000,1958.000000,2.000000 |
| 'AAAAAAAAICDEAAAA','United States','IN','Jennings County',17.000000,146.850000,0.000000,41.110000,-524.960000,1958.000000,2.000000 |
| 'AAAAAAAANDMAAAAA','United States','IN','Jennings County',32.000000,238.000000,637.450000,21.420000,-2870.090000,1958.000000,2.000000 |
| 'AAAAAAAAPNBCAAAA','United States','IN','Jennings County',59.000000,230.880000,0.000000,223.950000,7465.270000,1958.000000,2.000000 |
| 'AAAAAAAAAICBAAAA','United States','IN','Johnson County',59.000000,156.860000,0.000000,37.640000,-1144.600000,1924.000000,2.000000 |
| 'AAAAAAAACGJAAAAA','United States','IN','Johnson County',41.000000,111.540000,0.000000,72.500000,1333.320000,1924.000000,2.000000 |
| 'AAAAAAAAHNPCAAAA','United States','IN','Johnson County',5.000000,229.900000,885.990000,188.510000,-432.590000,1924.000000,2.000000 |
| 'AAAAAAAAKBBCAAAA','United States','IN','Johnson County',80.000000,105.660000,0.000000,73.960000,-344.800000,1924.000000,2.000000 |
| 'AAAAAAAAKMEDAAAA','United States','IN','Johnson County',11.000000,81.280000,0.000000,27.630000,-343.970000,1924.000000,2.000000 |
| 'AAAAAAAAKNCEAAAA','United States','IN','Johnson County',68.000000,100.510000,0.000000,18.090000,-4713.080000,1924.000000,2.000000 |
| 'AAAAAAAADAEDAAAA','United States','IN','Kosciusko County',84.000000,68.840000,464.190000,7.570000,-2622.150000,1987.000000,1.000000 |
| 'AAAAAAAAENDAAAAA','United States','IN','Kosciusko County',99.000000,142.120000,679.550000,98.060000,3855.640000,1987.000000,1.000000 |
| 'AAAAAAAAHHFAAAAA','United States','IN','Kosciusko County',59.000000,74.540000,0.000000,64.840000,2237.870000,1987.000000,1.000000 |
| 'AAAAAAAAHKHBAAAA','United States','IN','Kosciusko County',16.000000,58.200000,0.000000,27.350000,-97.600000,1987.000000,1.000000 |
| 'AAAAAAAAIFBCAAAA','United States','IN','Kosciusko County',44.000000,69.880000,0.000000,69.880000,1464.760000,1987.000000,1.000000 |
| 'AAAAAAAAKCLDAAAA','United States','IN','Kosciusko County',53.000000,171.980000,0.000000,29.230000,-1997.570000,1987.000000,1.000000 |
| 'AAAAAAAAKHCEAAAA','United States','IN','Kosciusko County',75.000000,62.040000,0.000000,32.880000,369.750000,1987.000000,1.000000 |
| 'AAAAAAAALOFBAAAA','United States','IN','Kosciusko County',49.000000,95.190000,0.000000,77.100000,2135.420000,1987.000000,1.000000 |
| 'AAAAAAAAMMIAAAAA','United States','IN','Kosciusko County',56.000000,12.780000,0.000000,12.010000,345.520000,1987.000000,1.000000 |
| 'AAAAAAAAPHADAAAA','United States','IN','Kosciusko County',19.000000,89.150000,0.000000,1.780000,-638.400000,1987.000000,1.000000 |
| 'AAAAAAAAAJAEAAAA','United States','IN','La Porte County',56.000000,84.850000,0.000000,6.780000,-2990.400000,1975.000000,4.000000 |
| 'AAAAAAAAALCBAAAA','United States','IN','La Porte County',61.000000,80.160000,0.000000,26.450000,-1646.390000,1975.000000,4.000000 |
| 'AAAAAAAACDDCAAAA','United States','IN','La Porte County',31.000000,129.150000,0.000000,38.740000,-1182.340000,1975.000000,4.000000 |
| 'AAAAAAAACDPDAAAA','United States','IN','La Porte County',23.000000,54.410000,282.090000,22.300000,-343.270000,1975.000000,4.000000 |
| 'AAAAAAAADFEEAAAA','United States','IN','La Porte County',52.000000,126.010000,0.000000,41.580000,-829.920000,1975.000000,4.000000 |
| 'AAAAAAAAEPJDAAAA','United States','IN','La Porte County',38.000000,63.130000,0.000000,46.080000,859.180000,1975.000000,4.000000 |
| 'AAAAAAAAIEDBAAAA','United States','IN','La Porte County',13.000000,50.530000,0.000000,31.320000,-52.260000,1975.000000,4.000000 |
| 'AAAAAAAAIOAEAAAA','United States','IN','La Porte County',10.000000,53.350000,0.000000,33.070000,122.300000,1975.000000,4.000000 |
| 'AAAAAAAAJKMAAAAA','United States','IN','La Porte County',50.000000,66.920000,0.000000,16.730000,-749.500000,1975.000000,4.000000 |
| 'AAAAAAAAKDBAAAAA','United States','IN','La Porte County',57.000000,123.530000,0.000000,81.520000,959.880000,1975.000000,4.000000 |
| 'AAAAAAAAKIDBAAAA','United States','IN','La Porte County',38.000000,50.200000,0.000000,40.660000,-307.040000,1975.000000,4.000000 |
| 'AAAAAAAALHDEAAAA','United States','IN','La Porte County',72.000000,147.380000,0.000000,26.520000,-3985.920000,1975.000000,4.000000 |
| 'AAAAAAAALKEAAAAA','United States','IN','La Porte County',68.000000,159.120000,0.000000,77.960000,-709.920000,1975.000000,4.000000 |
| ---- TYPES |
| STRING, STRING, STRING, STRING, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL |
| ==== |
