| ==== |
| ---- QUERY: TPCDS-Q18 |
| select i_item_id, |
| ca_country, |
| ca_state, |
| ca_county, |
| avg( cast(cs_quantity as decimal(12,2))) agg1, |
| avg( cast(cs_list_price as decimal(12,2))) agg2, |
| avg( cast(cs_coupon_amt as decimal(12,2))) agg3, |
| avg( cast(cs_sales_price as decimal(12,2))) agg4, |
| avg( cast(cs_net_profit as decimal(12,2))) agg5, |
| avg( cast(c_birth_year as decimal(12,2))) agg6, |
| avg( 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 = 'M' and |
| cd1.cd_education_status = 'College' and |
| c_current_cdemo_sk = cd2.cd_demo_sk and |
| c_current_addr_sk = ca_address_sk and |
| c_birth_month in (9,5,12,4,1,10) and |
| d_year = 2001 and |
| ca_state in ('ND','WI','AL' |
| ,'NC','OK','MS','TN') |
| group by rollup (i_item_id, ca_country, ca_state, ca_county) |
| order by ca_country, |
| ca_state, |
| ca_county, |
| i_item_id |
| limit 100; |
| ---- RESULTS |
| 'AAAAAAAAAMJBAAAA','United States','AL','Bullock County',20.000000,192.920000,0.000000,167.840000,1978.800000,1930.000000,6.000000 |
| 'AAAAAAAABHACAAAA','United States','AL','Bullock County',20.000000,117.530000,0.000000,62.290000,-665.400000,1930.000000,6.000000 |
| 'AAAAAAAAGMBDAAAA','United States','AL','Bullock County',87.000000,281.890000,0.000000,253.700000,13405.830000,1930.000000,6.000000 |
| 'AAAAAAAAMFNBAAAA','United States','AL','Bullock County',58.000000,12.070000,0.000000,6.510000,-71.340000,1930.000000,6.000000 |
| 'AAAAAAAANGBCAAAA','United States','AL','Bullock County',62.000000,63.470000,0.000000,0.000000,-1847.600000,1930.000000,6.000000 |
| 'AAAAAAAANKGCAAAA','United States','AL','Bullock County',50.000000,196.520000,0.000000,51.090000,-1557.000000,1930.000000,6.000000 |
| 'AAAAAAAAOJBCAAAA','United States','AL','Bullock County',81.000000,28.570000,0.000000,15.990000,499.770000,1930.000000,6.000000 |
| 'AAAAAAAAPAKDAAAA','United States','AL','Bullock County',48.000000,169.210000,0.000000,69.370000,-345.600000,1930.000000,6.000000 |
| 'AAAAAAAAPNMAAAAA','United States','AL','Bullock County',80.000000,228.040000,0.000000,54.720000,-3068.800000,1930.000000,6.000000 |
| 'AAAAAAAAABLCAAAA','United States','AL','Dale County',97.000000,14.950000,0.000000,13.600000,445.230000,1944.000000,0.000000 |
| 'AAAAAAAAACGCAAAA','United States','AL','Dale County',88.000000,152.900000,451.890000,18.340000,-3954.290000,1944.000000,0.000000 |
| 'AAAAAAAAAHHDAAAA','United States','AL','Dale County',21.000000,62.600000,0.000000,5.000000,-820.890000,1944.000000,0.000000 |
| 'AAAAAAAAAPHBAAAA','United States','AL','Dale County',14.000000,168.640000,0.000000,57.330000,-554.260000,1944.000000,0.000000 |
| 'AAAAAAAACHOAAAAA','United States','AL','Dale County',54.000000,115.260000,0.000000,101.420000,2300.940000,1944.000000,0.000000 |
| 'AAAAAAAADLHBAAAA','United States','AL','Dale County',42.000000,3.450000,0.000000,0.000000,-54.180000,1944.000000,0.000000 |
| 'AAAAAAAAELDBAAAA','United States','AL','Dale County',22.000000,9.120000,0.000000,2.370000,-84.480000,1944.000000,0.000000 |
| 'AAAAAAAAHHACAAAA','United States','AL','Dale County',11.000000,56.970000,0.000000,21.640000,-158.620000,1944.000000,0.000000 |
| 'AAAAAAAAIKAEAAAA','United States','AL','Dale County',41.000000,86.200000,38.160000,31.030000,-1308.750000,1944.000000,0.000000 |
| 'AAAAAAAAIPACAAAA','United States','AL','Dale County',94.000000,45.130000,144.530000,4.960000,-2102.550000,1944.000000,0.000000 |
| 'AAAAAAAALHPBAAAA','United States','AL','Dale County',64.000000,88.570000,0.000000,56.680000,-871.680000,1944.000000,0.000000 |
| 'AAAAAAAAOHMCAAAA','United States','AL','Dale County',78.000000,132.430000,0.000000,11.910000,-3074.760000,1944.000000,0.000000 |
| 'AAAAAAAAOMCEAAAA','United States','AL','Dale County',24.000000,36.350000,0.000000,32.710000,133.920000,1944.000000,0.000000 |
| 'AAAAAAAAPFLDAAAA','United States','AL','Dale County',93.000000,13.250000,0.000000,7.420000,233.430000,1944.000000,0.000000 |
| 'AAAAAAAAAFDDAAAA','United States','AL','Jefferson County',75.000000,88.760000,0.000000,50.590000,-192.000000,1933.000000,0.000000 |
| 'AAAAAAAACIFBAAAA','United States','AL','Jefferson County',64.000000,182.430000,3992.830000,138.640000,423.810000,1933.000000,0.000000 |
| 'AAAAAAAACPMAAAAA','United States','AL','Jefferson County',63.000000,57.100000,0.000000,53.670000,2023.560000,1933.000000,0.000000 |
| 'AAAAAAAAEJOCAAAA','United States','AL','Jefferson County',83.000000,100.530000,0.000000,15.070000,-2457.630000,1933.000000,0.000000 |
| 'AAAAAAAAFLKCAAAA','United States','AL','Jefferson County',88.000000,74.080000,0.000000,40.740000,566.720000,1933.000000,0.000000 |
| 'AAAAAAAAKBDCAAAA','United States','AL','Jefferson County',30.000000,48.600000,0.000000,45.680000,481.200000,1933.000000,0.000000 |
| 'AAAAAAAAMIPCAAAA','United States','AL','Jefferson County',24.000000,212.110000,0.000000,16.960000,-1896.480000,1933.000000,0.000000 |
| 'AAAAAAAAAFPCAAAA','United States','AL','Lamar County',56.000000,121.720000,0.000000,26.770000,-2927.120000,1972.000000,6.000000 |
| 'AAAAAAAAAHIBAAAA','United States','AL','Lamar County',68.000000,281.940000,0.000000,8.450000,-5946.600000,1972.000000,6.000000 |
| 'AAAAAAAAAIFAAAAA','United States','AL','Lamar County',52.000000,205.590000,0.000000,98.680000,271.960000,1972.000000,6.000000 |
| 'AAAAAAAABIDBAAAA','United States','AL','Lamar County',29.000000,94.220000,0.000000,91.390000,887.400000,1972.000000,6.000000 |
| 'AAAAAAAABJADAAAA','United States','AL','Lamar County',62.000000,31.210000,142.730000,12.790000,-407.470000,1972.000000,6.000000 |
| 'AAAAAAAACCNDAAAA','United States','AL','Lamar County',82.000000,63.880000,0.000000,49.180000,1373.500000,1972.000000,6.000000 |
| 'AAAAAAAACJAAAAAA','United States','AL','Lamar County',57.000000,133.260000,0.000000,63.960000,239.400000,1972.000000,6.000000 |
| 'AAAAAAAACJFAAAAA','United States','AL','Lamar County',4.000000,106.250000,0.000000,97.750000,101.880000,1972.000000,6.000000 |
| 'AAAAAAAAGBADAAAA','United States','AL','Lamar County',69.000000,93.170000,0.000000,0.000000,-3716.340000,1972.000000,6.000000 |
| 'AAAAAAAAGDFDAAAA','United States','AL','Lamar County',58.000000,160.040000,0.000000,108.820000,976.720000,1972.000000,6.000000 |
| 'AAAAAAAAIBHDAAAA','United States','AL','Lamar County',89.000000,141.980000,0.000000,53.950000,-377.360000,1974.000000,6.000000 |
| 'AAAAAAAAIGECAAAA','United States','AL','Lamar County',23.000000,36.680000,0.000000,2.200000,-454.710000,1974.000000,6.000000 |
| 'AAAAAAAAJHJAAAAA','United States','AL','Lamar County',11.000000,124.840000,0.000000,92.380000,383.350000,1972.000000,6.000000 |
| 'AAAAAAAAJLDCAAAA','United States','AL','Lamar County',87.000000,7.040000,154.870000,3.870000,-370.630000,1972.000000,6.000000 |
| 'AAAAAAAAMKBDAAAA','United States','AL','Lamar County',7.000000,55.570000,0.000000,32.780000,92.960000,1974.000000,6.000000 |
| 'AAAAAAAAOFHDAAAA','United States','AL','Lamar County',77.000000,85.960000,0.000000,85.100000,3354.890000,1974.000000,6.000000 |
| 'AAAAAAAAOHKAAAAA','United States','AL','Lamar County',87.000000,140.150000,0.000000,9.810000,-5530.590000,1972.000000,6.000000 |
| 'AAAAAAAABEFDAAAA','United States','AL','Lauderdale County',50.000000,16.470000,0.000000,15.480000,-49.500000,1935.000000,5.000000 |
| 'AAAAAAAACMDDAAAA','United States','AL','Lauderdale County',37.000000,197.130000,0.000000,80.820000,-167.240000,1935.000000,5.000000 |
| 'AAAAAAAADEFBAAAA','United States','AL','Lauderdale County',94.000000,40.190000,0.000000,17.680000,-509.480000,1935.000000,5.000000 |
| 'AAAAAAAAEEODAAAA','United States','AL','Lauderdale County',19.000000,82.240000,0.000000,37.000000,2.280000,1935.000000,5.000000 |
| 'AAAAAAAAGFEEAAAA','United States','AL','Lauderdale County',70.000000,89.240000,0.000000,33.910000,-1176.000000,1935.000000,5.000000 |
| 'AAAAAAAAGMKBAAAA','United States','AL','Lauderdale County',69.000000,34.440000,76.870000,6.190000,-701.320000,1935.000000,5.000000 |
| 'AAAAAAAAJGKDAAAA','United States','AL','Lauderdale County',47.000000,6.750000,0.000000,4.520000,30.080000,1935.000000,5.000000 |
| 'AAAAAAAAKLHBAAAA','United States','AL','Lauderdale County',45.000000,62.560000,0.000000,45.040000,717.300000,1935.000000,5.000000 |
| 'AAAAAAAALEKDAAAA','United States','AL','Lauderdale County',2.000000,68.050000,0.000000,19.730000,-41.560000,1935.000000,5.000000 |
| 'AAAAAAAAMLLBAAAA','United States','AL','Lauderdale County',79.000000,14.010000,0.000000,10.640000,417.910000,1935.000000,5.000000 |
| 'AAAAAAAAOIDBAAAA','United States','AL','Lauderdale County',49.000000,117.910000,0.000000,68.380000,-172.480000,1935.000000,5.000000 |
| 'AAAAAAAAOKIDAAAA','United States','AL','Lauderdale County',58.000000,243.790000,0.000000,138.960000,2470.800000,1935.000000,5.000000 |
| 'AAAAAAAAAHFCAAAA','United States','AL','Lee County',90.000000,147.450000,0.000000,106.160000,2341.800000,1939.000000,1.000000 |
| 'AAAAAAAABPKCAAAA','United States','AL','Lee County',96.000000,167.270000,0.000000,70.250000,-2592.000000,1939.000000,1.000000 |
| 'AAAAAAAACBKBAAAA','United States','AL','Lee County',64.000000,258.600000,910.270000,142.230000,2638.530000,1939.000000,1.000000 |
| 'AAAAAAAACIICAAAA','United States','AL','Lee County',9.000000,11.290000,5.920000,5.980000,6.050000,1939.000000,1.000000 |
| 'AAAAAAAAEDJAAAAA','United States','AL','Lee County',19.000000,121.960000,0.000000,47.560000,-26.980000,1939.000000,1.000000 |
| 'AAAAAAAAJJDEAAAA','United States','AL','Lee County',32.000000,107.320000,0.000000,10.730000,-910.080000,1939.000000,1.000000 |
| 'AAAAAAAAMOACAAAA','United States','AL','Lee County',51.000000,16.480000,0.000000,1.640000,-375.870000,1939.000000,1.000000 |
| 'AAAAAAAAOEEDAAAA','United States','AL','Lee County',17.000000,74.280000,336.640000,63.880000,51.640000,1939.000000,1.000000 |
| 'AAAAAAAAOOKDAAAA','United States','AL','Lee County',95.000000,156.530000,0.000000,95.480000,1989.300000,1939.000000,1.000000 |
| 'AAAAAAAAPIFDAAAA','United States','AL','Lee County',42.000000,58.660000,118.980000,40.470000,-811.560000,1939.000000,1.000000 |
| 'AAAAAAAADEGDAAAA','United States','AL','Marengo County',19.000000,36.900000,0.000000,1.470000,-551.570000,1960.000000,2.000000 |
| 'AAAAAAAADPMBAAAA','United States','AL','Marengo County',12.000000,86.800000,0.000000,49.470000,-139.920000,1960.000000,2.000000 |
| 'AAAAAAAAFBFCAAAA','United States','AL','Marengo County',12.000000,22.330000,48.330000,4.240000,-218.970000,1960.000000,2.000000 |
| 'AAAAAAAAPDCCAAAA','United States','AL','Marengo County',2.000000,183.040000,0.000000,91.520000,38.340000,1960.000000,2.000000 |
| 'AAAAAAAAAAHBAAAA','United States','AL','Marshall County',55.000000,40.870000,0.000000,20.020000,-261.250000,1978.000000,3.000000 |
| 'AAAAAAAAAALDAAAA','United States','AL','Marshall County',80.000000,100.760000,59.200000,1.000000,-6228.000000,1978.000000,3.000000 |
| 'AAAAAAAAADHAAAAA','United States','AL','Marshall County',7.000000,260.420000,0.000000,190.100000,710.640000,1978.000000,3.000000 |
| 'AAAAAAAACAGCAAAA','United States','AL','Marshall County',92.000000,183.760000,0.000000,121.280000,3438.040000,1978.000000,3.000000 |
| 'AAAAAAAADAOCAAAA','United States','AL','Marshall County',68.000000,199.290000,3428.280000,109.600000,-2717.680000,1978.000000,3.000000 |
| 'AAAAAAAAGGPCAAAA','United States','AL','Marshall County',64.000000,263.920000,0.000000,160.990000,4313.600000,1978.000000,3.000000 |
| 'AAAAAAAAHBEEAAAA','United States','AL','Marshall County',25.000000,46.470000,0.000000,10.220000,-209.250000,1978.000000,3.000000 |
| 'AAAAAAAAFJODAAAA','United States','AL','Morgan County',39.000000,45.160000,0.000000,8.120000,-1377.090000,1985.000000,4.000000 |
| 'AAAAAAAAGDCDAAAA','United States','AL','Morgan County',99.000000,8.690000,0.000000,0.000000,-354.420000,1985.000000,4.000000 |
| 'AAAAAAAAGKDAAAAA','United States','AL','Morgan County',68.000000,135.270000,0.000000,132.560000,2840.360000,1985.000000,4.000000 |
| 'AAAAAAAAIOFBAAAA','United States','AL','Morgan County',37.000000,64.090000,0.000000,1.920000,-827.320000,1985.000000,4.000000 |
| 'AAAAAAAALBJBAAAA','United States','AL','Morgan County',73.000000,132.770000,0.000000,132.770000,5891.100000,1985.000000,4.000000 |
| 'AAAAAAAAPJICAAAA','United States','AL','Morgan County',85.000000,86.390000,0.000000,60.470000,2237.200000,1985.000000,4.000000 |
| 'AAAAAAAADOCAAAAA','United States','AL','Perry County',44.000000,132.900000,0.000000,53.160000,-584.760000,1957.000000,6.000000 |
| 'AAAAAAAADPAEAAAA','United States','AL','Perry County',95.000000,180.400000,0.000000,151.530000,5279.150000,1957.000000,6.000000 |
| 'AAAAAAAAEFNDAAAA','United States','AL','Perry County',25.000000,3.800000,0.000000,2.200000,6.000000,1957.000000,6.000000 |
| 'AAAAAAAAGHGDAAAA','United States','AL','Perry County',43.000000,82.190000,0.000000,32.870000,-2051.530000,1957.000000,6.000000 |
| 'AAAAAAAAHACEAAAA','United States','AL','Perry County',65.000000,191.050000,0.000000,32.470000,-3173.950000,1957.000000,6.000000 |
| 'AAAAAAAAHHHBAAAA','United States','AL','Perry County',6.000000,36.220000,0.000000,7.960000,-36.480000,1957.000000,6.000000 |
| 'AAAAAAAAIHGCAAAA','United States','AL','Perry County',12.000000,48.660000,0.000000,36.980000,-51.120000,1957.000000,6.000000 |
| 'AAAAAAAAKAHAAAAA','United States','AL','Perry County',99.000000,7.510000,0.000000,0.450000,-292.050000,1957.000000,6.000000 |
| 'AAAAAAAALNBAAAAA','United States','AL','Perry County',49.000000,150.130000,0.000000,123.100000,1285.760000,1957.000000,6.000000 |
| 'AAAAAAAAMFDDAAAA','United States','AL','Perry County',87.000000,121.490000,0.000000,51.020000,-1244.100000,1957.000000,6.000000 |
| 'AAAAAAAAOMFBAAAA','United States','AL','Perry County',55.000000,79.970000,0.000000,38.380000,-461.450000,1957.000000,6.000000 |
| 'AAAAAAAAADFDAAAA','United States','AL','Shelby County',75.000000,231.300000,0.000000,83.260000,-1106.250000,1948.000000,3.000000 |
| 'AAAAAAAAADKAAAAA','United States','AL','Shelby County',61.000000,67.390000,616.250000,13.470000,-3432.620000,1973.000000,1.000000 |
| 'AAAAAAAAAOCEAAAA','United States','AL','Shelby County',67.000000,51.710000,0.000000,11.890000,-1732.620000,1948.000000,3.000000 |
| ---- TYPES |
| STRING, STRING, STRING, STRING, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL |
| ==== |
