| ==== |
| ---- QUERY: TPCDS-Q53 |
| select |
| * |
| from |
| (select |
| i_manufact_id, |
| sum(ss_sales_price) sum_sales, |
| avg(sum(ss_sales_price)) over (partition by i_manufact_id) avg_quarterly_sales |
| from |
| item, |
| store_sales, |
| date_dim, |
| store |
| where |
| ss_item_sk = i_item_sk |
| and ss_sold_date_sk = d_date_sk |
| and ss_store_sk = s_store_sk |
| and d_month_seq in (1200, 1200 + 1, 1200 + 2, 1200 + 3, 1200 + 4, 1200 + 5, 1200 + 6, 1200 + 7, 1200 + 8, 1200 + 9, 1200 + 10, 1200 + 11) |
| and ((i_category in ('Books', 'Children', 'Electronics') |
| and i_class in ('personal', 'portable', 'reference', 'self-help') |
| and i_brand in ('scholaramalgamalg #14', 'scholaramalgamalg #7', 'exportiunivamalg #9', 'scholaramalgamalg #9')) |
| or (i_category in ('Women', 'Music', 'Men') |
| and i_class in ('accessories', 'classical', 'fragrances', 'pants') |
| and i_brand in ('amalgimporto #1', 'edu packscholar #1', 'exportiimporto #1', 'importoamalg #1'))) |
| group by |
| i_manufact_id, |
| d_qoy |
| ) tmp1 |
| where |
| case when avg_quarterly_sales > 0 then abs (sum_sales - avg_quarterly_sales) / avg_quarterly_sales else null end > 0.1 |
| order by |
| avg_quarterly_sales, |
| sum_sales, |
| i_manufact_id |
| limit 100 |
| ---- RESULTS |
| 30,165.67,340.635000 |
| 30,247.07,340.635000 |
| 30,627.63,340.635000 |
| 619,158.76,348.640000 |
| 619,210.81,348.640000 |
| 619,464.26,348.640000 |
| 619,560.73,348.640000 |
| 271,79.26,354.330000 |
| 271,86.87,354.330000 |
| 271,179.61,354.330000 |
| 271,1071.58,354.330000 |
| 827,82.44,356.590000 |
| 827,320.05,356.590000 |
| 827,666.52,356.590000 |
| 296,188.61,369.125000 |
| 296,265.76,369.125000 |
| 296,655.24,369.125000 |
| 308,200.28,385.982500 |
| 308,214.07,385.982500 |
| 308,489.17,385.982500 |
| 308,640.41,385.982500 |
| 486,178.80,400.530000 |
| 486,455.08,400.530000 |
| 486,468.01,400.530000 |
| 486,500.23,400.530000 |
| 554,191.48,407.975000 |
| 554,346.80,407.975000 |
| 554,660.97,407.975000 |
| 208,151.84,410.385000 |
| 208,207.02,410.385000 |
| 208,533.75,410.385000 |
| 208,748.93,410.385000 |
| 662,199.83,412.135000 |
| 662,300.61,412.135000 |
| 662,460.94,412.135000 |
| 662,687.16,412.135000 |
| 394,264.65,413.405000 |
| 394,272.02,413.405000 |
| 394,674.12,413.405000 |
| 221,183.63,416.082500 |
| 221,534.62,416.082500 |
| 221,552.44,416.082500 |
| 621,232.20,418.115000 |
| 621,624.80,418.115000 |
| 507,76.98,419.465000 |
| 507,505.36,419.465000 |
| 507,644.24,419.465000 |
| 316,36.25,423.035000 |
| 316,158.85,423.035000 |
| 316,697.32,423.035000 |
| 316,799.72,423.035000 |
| 56,110.81,423.097500 |
| 56,371.77,423.097500 |
| 56,500.77,423.097500 |
| 56,709.04,423.097500 |
| 517,194.87,426.287500 |
| 517,313.41,426.287500 |
| 517,482.41,426.287500 |
| 517,714.46,426.287500 |
| 411,364.09,426.892500 |
| 411,519.60,426.892500 |
| 247,313.42,427.682500 |
| 247,353.08,427.682500 |
| 247,628.37,427.682500 |
| 652,228.75,434.752500 |
| 652,314.98,434.752500 |
| 652,365.90,434.752500 |
| 652,829.38,434.752500 |
| 129,301.69,436.452500 |
| 129,325.26,436.452500 |
| 129,550.47,436.452500 |
| 129,568.39,436.452500 |
| 99,164.24,438.667500 |
| 99,183.75,438.667500 |
| 99,393.58,438.667500 |
| 99,1013.10,438.667500 |
| 235,111.64,441.030000 |
| 235,179.51,441.030000 |
| 235,493.83,441.030000 |
| 235,979.14,441.030000 |
| 360,143.83,445.415000 |
| 360,293.99,445.415000 |
| 360,928.51,445.415000 |
| 732,106.56,447.065000 |
| 732,300.77,447.065000 |
| 732,498.49,447.065000 |
| 732,882.44,447.065000 |
| 190,202.77,451.982500 |
| 190,233.10,451.982500 |
| 190,358.97,451.982500 |
| 190,1013.09,451.982500 |
| 147,212.53,455.892500 |
| 147,366.10,455.892500 |
| 147,553.92,455.892500 |
| 147,691.02,455.892500 |
| 665,283.25,456.087500 |
| 665,357.22,456.087500 |
| 665,691.20,456.087500 |
| 85,325.28,456.280000 |
| 85,350.10,456.280000 |
| ---- TYPES |
| INT, DECIMAL, DECIMAL |
| ==== |
