resources/tpcds-v2.7.0/q27a.sql

0001 -- This is a new query in TPCDS v2.7
0002 with results as (
0003     select
0004         i_item_id,
0005         s_state, 0 as g_state,
0006         ss_quantity agg1,
0007         ss_list_price agg2,
0008         ss_coupon_amt agg3,
0009         ss_sales_price agg4
0010     from
0011       store_sales, customer_demographics, date_dim, store, item
0012     where
0013       ss_sold_date_sk = d_date_sk
0014         and ss_item_sk = i_item_sk
0015         and ss_store_sk = s_store_sk
0016         and ss_cdemo_sk = cd_demo_sk
0017         and cd_gender = 'F'
0018         and cd_marital_status = 'W'
0019         and cd_education_status = 'Primary'
0020         and d_year = 1998
0021         and s_state in ('TN','TN', 'TN', 'TN', 'TN', 'TN'))
0022 select
0023   i_item_id,
0024   s_state,
0025   g_state,
0026   agg1,
0027   agg2,
0028   agg3,
0029   agg4
0030 from (
0031     select
0032       i_item_id,
0033       s_state,
0034       0 as g_state,
0035       avg(agg1) agg1,
0036       avg(agg2) agg2,
0037       avg(agg3) agg3,
0038       avg(agg4) agg4
0039     from
0040       results
0041     group by
0042       i_item_id,
0043       s_state
0044     union all
0045     select
0046       i_item_id,
0047       NULL AS s_state,
0048       1 AS g_state,
0049       avg(agg1) agg1,
0050       avg(agg2) agg2,
0051       avg(agg3) agg3,
0052       avg(agg4) agg4
0053     from results
0054     group by
0055       i_item_id
0056     union all
0057     select
0058       NULL AS i_item_id,
0059       NULL as s_state,
0060       1 as g_state,
0061       avg(agg1) agg1,
0062       avg(agg2) agg2,
0063       avg(agg3) agg3,
0064       avg(agg4) agg4
0065     from
0066       results) foo
0067 order by
0068   i_item_id,
0069   s_state
0070 limit 100