| ==== |
| ---- QUERY: TPCDS-Q60 |
| with ss as ( |
| select |
| i_item_id,sum(ss_ext_sales_price) total_sales |
| from |
| store_sales, |
| date_dim, |
| customer_address, |
| item |
| where |
| i_item_id in (select |
| i_item_id |
| from |
| item |
| where i_category in ('Music')) |
| and ss_item_sk = i_item_sk |
| and ss_sold_date_sk = d_date_sk |
| and d_year = 1998 |
| and d_moy = 9 |
| and ss_addr_sk = ca_address_sk |
| and ca_gmt_offset = -5 |
| group by i_item_id), |
| cs as ( |
| select |
| i_item_id,sum(cs_ext_sales_price) total_sales |
| from |
| catalog_sales, |
| date_dim, |
| customer_address, |
| item |
| where |
| i_item_id in (select |
| i_item_id |
| from |
| item |
| where i_category in ('Music')) |
| and cs_item_sk = i_item_sk |
| and cs_sold_date_sk = d_date_sk |
| and d_year = 1998 |
| and d_moy = 9 |
| and cs_bill_addr_sk = ca_address_sk |
| and ca_gmt_offset = -5 |
| group by i_item_id), |
| ws as ( |
| select |
| i_item_id,sum(ws_ext_sales_price) total_sales |
| from |
| web_sales, |
| date_dim, |
| customer_address, |
| item |
| where |
| i_item_id in (select |
| i_item_id |
| from |
| item |
| where i_category in ('Music')) |
| and ws_item_sk = i_item_sk |
| and ws_sold_date_sk = d_date_sk |
| and d_year = 1998 |
| and d_moy = 9 |
| and ws_bill_addr_sk = ca_address_sk |
| and ca_gmt_offset = -5 |
| group by i_item_id) |
| select |
| i_item_id |
| ,sum(total_sales) total_sales |
| from (select * from ss |
| union all |
| select * from cs |
| union all |
| select * from ws) tmp1 |
| group by i_item_id |
| order by i_item_id |
| ,total_sales |
| limit 100; |
| ---- RESULTS |
| 'AAAAAAAAAABBAAAA',6287.84 |
| 'AAAAAAAAAABEAAAA',1297.26 |
| 'AAAAAAAAAADCAAAA',8936.88 |
| 'AAAAAAAAAADEAAAA',20945.84 |
| 'AAAAAAAAAAHAAAAA',7227.38 |
| 'AAAAAAAAAANDAAAA',17117.56 |
| 'AAAAAAAAAAOCAAAA',4287.89 |
| 'AAAAAAAAABADAAAA',499.72 |
| 'AAAAAAAAABBCAAAA',22704.66 |
| 'AAAAAAAAABCEAAAA',22499.76 |
| 'AAAAAAAAABDEAAAA',5527.74 |
| 'AAAAAAAAABFBAAAA',4953.67 |
| 'AAAAAAAAABFEAAAA',12523.22 |
| 'AAAAAAAAABHCAAAA',20093.49 |
| 'AAAAAAAAABIBAAAA',382.30 |
| 'AAAAAAAAABICAAAA',1271.62 |
| 'AAAAAAAAABKCAAAA',7355.85 |
| 'AAAAAAAAABLBAAAA',786.05 |
| 'AAAAAAAAABNAAAAA',8385.94 |
| 'AAAAAAAAACACAAAA',11564.85 |
| 'AAAAAAAAACBEAAAA',13046.23 |
| 'AAAAAAAAACCDAAAA',14533.50 |
| 'AAAAAAAAACDCAAAA',820.49 |
| 'AAAAAAAAACLBAAAA',6761.17 |
| 'AAAAAAAAACNCAAAA',19023.15 |
| 'AAAAAAAAACODAAAA',7322.65 |
| 'AAAAAAAAADACAAAA',4696.41 |
| 'AAAAAAAAADBAAAAA',988.80 |
| 'AAAAAAAAADDCAAAA',5661.21 |
| 'AAAAAAAAADDEAAAA',1846.56 |
| 'AAAAAAAAADEEAAAA',15327.48 |
| 'AAAAAAAAADGEAAAA',4511.21 |
| 'AAAAAAAAADHDAAAA',27866.80 |
| 'AAAAAAAAADICAAAA',15624.49 |
| 'AAAAAAAAADLCAAAA',399.00 |
| 'AAAAAAAAADNDAAAA',6403.89 |
| 'AAAAAAAAADPCAAAA',4246.28 |
| 'AAAAAAAAAEBAAAAA',1946.34 |
| 'AAAAAAAAAEBCAAAA',2021.69 |
| 'AAAAAAAAAEBDAAAA',6664.64 |
| 'AAAAAAAAAECCAAAA',11363.72 |
| 'AAAAAAAAAECEAAAA',10198.96 |
| 'AAAAAAAAAEFCAAAA',62.04 |
| 'AAAAAAAAAEGDAAAA',9547.35 |
| 'AAAAAAAAAEIBAAAA',3804.21 |
| 'AAAAAAAAAEICAAAA',7097.51 |
| 'AAAAAAAAAEKCAAAA',20954.40 |
| 'AAAAAAAAAEPDAAAA',2162.31 |
| 'AAAAAAAAAFBEAAAA',10916.01 |
| 'AAAAAAAAAFCDAAAA',14657.18 |
| 'AAAAAAAAAFDCAAAA',794.73 |
| 'AAAAAAAAAFFAAAAA',9979.09 |
| 'AAAAAAAAAFHCAAAA',2060.61 |
| 'AAAAAAAAAFIAAAAA',705.60 |
| 'AAAAAAAAAFJCAAAA',13594.18 |
| 'AAAAAAAAAFMDAAAA',2354.86 |
| 'AAAAAAAAAFNBAAAA',17475.06 |
| 'AAAAAAAAAGBBAAAA',2052.41 |
| 'AAAAAAAAAGCCAAAA',8040.70 |
| 'AAAAAAAAAGCDAAAA',16455.28 |
| 'AAAAAAAAAGDCAAAA',2495.73 |
| 'AAAAAAAAAGDEAAAA',6750.35 |
| 'AAAAAAAAAGFAAAAA',5985.82 |
| 'AAAAAAAAAGHAAAAA',17532.15 |
| 'AAAAAAAAAGHDAAAA',1220.67 |
| 'AAAAAAAAAGJCAAAA',7564.93 |
| 'AAAAAAAAAGKAAAAA',14082.90 |
| 'AAAAAAAAAGKDAAAA',1891.00 |
| 'AAAAAAAAAGNBAAAA',7856.78 |
| 'AAAAAAAAAGOCAAAA',14920.31 |
| 'AAAAAAAAAGPCAAAA',7986.28 |
| 'AAAAAAAAAHCBAAAA',15074.94 |
| 'AAAAAAAAAHECAAAA',11667.69 |
| 'AAAAAAAAAHHDAAAA',2491.48 |
| 'AAAAAAAAAHJDAAAA',4056.95 |
| 'AAAAAAAAAHMAAAAA',15866.43 |
| 'AAAAAAAAAHNDAAAA',7289.53 |
| 'AAAAAAAAAIACAAAA',2603.30 |
| 'AAAAAAAAAIBCAAAA',9.09 |
| 'AAAAAAAAAIBEAAAA',6979.53 |
| 'AAAAAAAAAIDAAAAA',32.70 |
| 'AAAAAAAAAIDCAAAA',15307.29 |
| 'AAAAAAAAAIIAAAAA',11268.14 |
| 'AAAAAAAAAIIDAAAA',6837.36 |
| 'AAAAAAAAAIJCAAAA',28318.16 |
| 'AAAAAAAAAIMAAAAA',4013.06 |
| 'AAAAAAAAAINBAAAA',7691.92 |
| 'AAAAAAAAAINCAAAA',13190.42 |
| 'AAAAAAAAAIODAAAA',17489.85 |
| 'AAAAAAAAAIPDAAAA',6096.92 |
| 'AAAAAAAAAJAEAAAA',30169.62 |
| 'AAAAAAAAAJCCAAAA',17608.54 |
| 'AAAAAAAAAJGBAAAA',14.10 |
| 'AAAAAAAAAJJCAAAA',2763.55 |
| 'AAAAAAAAAJMBAAAA',1013.43 |
| 'AAAAAAAAAJMCAAAA',12220.00 |
| 'AAAAAAAAAJNBAAAA',8076.70 |
| 'AAAAAAAAAJODAAAA',5113.02 |
| 'AAAAAAAAAKBAAAAA',2413.44 |
| 'AAAAAAAAAKDAAAAA',15712.05 |
| ---- TYPES |
| STRING, DECIMAL |
| ==== |