| ==== |
| ---- QUERY: TPCDS-Q33 |
| with ss as ( |
| select |
| i_manufact_id,sum(ss_ext_sales_price) total_sales |
| from |
| store_sales, |
| date_dim, |
| customer_address, |
| item |
| where |
| i_manufact_id in (select |
| i_manufact_id |
| from |
| item |
| where i_category in ('Electronics')) |
| and ss_item_sk = i_item_sk |
| and ss_sold_date_sk = d_date_sk |
| and d_year = 1998 |
| and d_moy = 5 |
| and ss_addr_sk = ca_address_sk |
| and ca_gmt_offset = -5 |
| group by i_manufact_id), |
| cs as ( |
| select |
| i_manufact_id,sum(cs_ext_sales_price) total_sales |
| from |
| catalog_sales, |
| date_dim, |
| customer_address, |
| item |
| where |
| i_manufact_id in (select |
| i_manufact_id |
| from |
| item |
| where i_category in ('Electronics')) |
| and cs_item_sk = i_item_sk |
| and cs_sold_date_sk = d_date_sk |
| and d_year = 1998 |
| and d_moy = 5 |
| and cs_bill_addr_sk = ca_address_sk |
| and ca_gmt_offset = -5 |
| group by i_manufact_id), |
| ws as ( |
| select |
| i_manufact_id,sum(ws_ext_sales_price) total_sales |
| from |
| web_sales, |
| date_dim, |
| customer_address, |
| item |
| where |
| i_manufact_id in (select |
| i_manufact_id |
| from |
| item |
| where i_category in ('Electronics')) |
| and ws_item_sk = i_item_sk |
| and ws_sold_date_sk = d_date_sk |
| and d_year = 1998 |
| and d_moy = 5 |
| and ws_bill_addr_sk = ca_address_sk |
| and ca_gmt_offset = -5 |
| group by i_manufact_id) |
| select i_manufact_id ,sum(total_sales) total_sales |
| from (select * from ss |
| union all |
| select * from cs |
| union all |
| select * from ws) tmp1 |
| group by i_manufact_id |
| order by total_sales |
| limit 100; |
| ---- RESULTS |
| 698,214.34 |
| 929,539.60 |
| 464,641.96 |
| 938,858.22 |
| 697,959.48 |
| 682,1074.64 |
| 741,1195.96 |
| 812,2069.00 |
| 838,2256.80 |
| 910,2291.67 |
| 899,2355.04 |
| 990,2370.68 |
| 963,2380.92 |
| 832,2684.32 |
| 692,2773.42 |
| 738,2945.77 |
| 564,3210.59 |
| 770,3379.72 |
| 821,3446.02 |
| 797,3485.44 |
| 974,3527.17 |
| 704,3574.75 |
| 853,3850.44 |
| 898,3854.56 |
| 937,3968.27 |
| 737,4284.65 |
| 942,4618.19 |
| 798,5017.15 |
| 849,5099.82 |
| 939,5122.92 |
| 947,5138.04 |
| 774,5138.38 |
| 668,5666.30 |
| 607,5770.19 |
| 358,5955.66 |
| 860,5957.46 |
| 951,5973.81 |
| 472,6060.92 |
| 824,6198.72 |
| 766,6237.09 |
| 783,6341.40 |
| 799,6360.64 |
| 794,6474.90 |
| 529,6476.80 |
| 928,6536.81 |
| 946,6817.54 |
| 795,6859.76 |
| 793,6871.33 |
| 423,6878.60 |
| 811,6887.18 |
| 887,6998.14 |
| 961,7098.23 |
| 806,7143.83 |
| 772,7196.00 |
| 880,7202.52 |
| 709,7247.76 |
| 696,7732.80 |
| 710,7783.45 |
| 784,7894.58 |
| 780,7973.56 |
| 476,8166.70 |
| 839,8299.55 |
| 828,8398.05 |
| 968,8461.36 |
| 966,8715.08 |
| 872,8722.84 |
| 432,8779.74 |
| 842,8879.82 |
| 981,8899.15 |
| 567,9226.08 |
| 773,9431.83 |
| 430,9891.62 |
| 445,9913.18 |
| 678,9976.96 |
| 977,10067.34 |
| 705,10202.62 |
| 402,10322.05 |
| 789,10376.28 |
| 733,10653.42 |
| 936,10760.28 |
| 920,10922.38 |
| 658,10926.50 |
| 1000,10992.09 |
| 651,11129.87 |
| 965,11158.41 |
| 844,11348.42 |
| 954,11383.41 |
| 655,11632.71 |
| 99,11785.13 |
| 493,11859.91 |
| 723,12068.58 |
| 623,12388.39 |
| 874,12585.39 |
| 810,12598.51 |
| 299,12649.43 |
| 826,12657.78 |
| 870,12927.40 |
| 385,12992.19 |
| 590,13381.82 |
| 718,13768.98 |
| ---- TYPES |
| INT, DECIMAL |
| ==== |