resources/tpcds-v2.7.0/q18a.sql

0001 -- This is a new query in TPCDS v2.7
0002 with results as (
0003     select
0004       i_item_id,
0005       ca_country,
0006       ca_state,
0007       ca_county,
0008       cast(cs_quantity as decimal(12,2)) agg1,
0009       cast(cs_list_price as decimal(12,2)) agg2,
0010       cast(cs_coupon_amt as decimal(12,2)) agg3,
0011       cast(cs_sales_price as decimal(12,2)) agg4,
0012       cast(cs_net_profit as decimal(12,2)) agg5,
0013       cast(c_birth_year as decimal(12,2)) agg6,
0014       cast(cd1.cd_dep_count as decimal(12,2)) agg7
0015     from
0016       catalog_sales, customer_demographics cd1, customer_demographics cd2, customer,
0017       customer_address, date_dim, item
0018     where
0019       cs_sold_date_sk = d_date_sk
0020         and cs_item_sk = i_item_sk
0021         and cs_bill_cdemo_sk = cd1.cd_demo_sk
0022         and cs_bill_customer_sk = c_customer_sk
0023         and cd1.cd_gender = 'M'
0024         and cd1.cd_education_status = 'College'
0025         and c_current_cdemo_sk = cd2.cd_demo_sk
0026         and c_current_addr_sk = ca_address_sk
0027         and c_birth_month in (9,5,12,4,1,10)
0028         and d_year = 2001
0029         and ca_state in ('ND','WI','AL','NC','OK','MS','TN'))
0030 select
0031   i_item_id,
0032   ca_country,
0033   ca_state,
0034   ca_county,
0035   agg1,
0036   agg2,
0037   agg3,
0038   agg4,
0039   agg5,
0040   agg6,
0041   agg7
0042 from (
0043     select
0044       i_item_id,
0045       ca_country,
0046       ca_state,
0047       ca_county,
0048       avg(agg1) agg1,
0049       avg(agg2) agg2,
0050       avg(agg3) agg3,
0051       avg(agg4) agg4,
0052       avg(agg5) agg5,
0053       avg(agg6) agg6,
0054       avg(agg7) agg7
0055     from
0056       results
0057     group by
0058       i_item_id,
0059       ca_country,
0060       ca_state,
0061       ca_county
0062     union all
0063     select
0064       i_item_id,
0065       ca_country,
0066       ca_state,
0067       NULL as county,
0068       avg(agg1) agg1,
0069       avg(agg2) agg2,
0070       avg(agg3) agg3,
0071       avg(agg4) agg4,
0072       avg(agg5) agg5,
0073       avg(agg6) agg6,
0074       avg(agg7) agg7
0075     from
0076       results
0077     group by
0078       i_item_id,
0079       ca_country,
0080       ca_state
0081     union all
0082     select
0083       i_item_id,
0084       ca_country,
0085       NULL as ca_state,
0086       NULL as county,
0087       avg(agg1) agg1,
0088       avg(agg2) agg2,
0089       avg(agg3) agg3,
0090       avg(agg4) agg4,
0091       avg(agg5) agg5,
0092       avg(agg6) agg6,
0093       avg(agg7) agg7
0094     from results
0095     group by
0096       i_item_id,
0097       ca_country
0098     union all
0099     select
0100       i_item_id,
0101       NULL as ca_country,
0102       NULL as ca_state,
0103       NULL as county,
0104       avg(agg1) agg1,
0105       avg(agg2) agg2,
0106       avg(agg3) agg3,
0107       avg(agg4) agg4,
0108       avg(agg5) agg5,
0109       avg(agg6) agg6,
0110       avg(agg7) agg7
0111     from results
0112     group by
0113       i_item_id
0114     union all
0115     select
0116       NULL AS i_item_id,
0117       NULL as ca_country,
0118       NULL as ca_state,
0119       NULL as county,
0120       avg(agg1) agg1,
0121       avg(agg2) agg2,
0122       avg(agg3) agg3,
0123       avg(agg4) agg4,
0124       avg(agg5) agg5,
0125       avg(agg6) agg6,
0126       avg(agg7) agg7
0127     from results) foo
0128 order by
0129   ca_country,
0130   ca_state,
0131   ca_county,
0132   i_item_id
0133 limit 100