resources/tpcds-modifiedQueries/q65.sql

0001 -- start query 65 in stream 0 using template query65.tpl
0002 select
0003   s_store_name,
0004   i_item_desc,
0005   sc.revenue,
0006   i_current_price,
0007   i_wholesale_cost,
0008   i_brand
0009 from
0010   store,
0011   item,
0012   (select
0013     ss_store_sk,
0014     avg(revenue) as ave
0015   from
0016     (select
0017       ss_store_sk,
0018       ss_item_sk,
0019       sum(ss_sales_price) as revenue
0020     from
0021       store_sales,
0022       date_dim
0023     where
0024       ss_sold_date_sk = d_date_sk
0025       and d_month_seq between 1212 and 1212 + 11
0026       and ss_sold_date_sk between 2451911 and 2452275  -- partition key filter
0027     group by
0028       ss_store_sk,
0029       ss_item_sk
0030     ) sa
0031   group by
0032     ss_store_sk
0033   ) sb,
0034   (select
0035     ss_store_sk,
0036     ss_item_sk,
0037     sum(ss_sales_price) as revenue
0038   from
0039     store_sales,
0040     date_dim
0041   where
0042     ss_sold_date_sk = d_date_sk
0043     and d_month_seq between 1212 and 1212 + 11
0044     and ss_sold_date_sk between 2451911 and 2452275  -- partition key filter
0045   group by
0046     ss_store_sk,
0047     ss_item_sk
0048   ) sc
0049 where
0050   sb.ss_store_sk = sc.ss_store_sk
0051   and sc.revenue <= 0.1 * sb.ave
0052   and s_store_sk = sc.ss_store_sk
0053   and i_item_sk = sc.ss_item_sk
0054 order by
0055   s_store_name,
0056   i_item_desc
0057 limit 100
0058 -- end query 65 in stream 0 using template query65.tpl