ceph/src/s3select/TPCDS/sample-queries-tpcds/query57.sql

   1 -- start query 1 in stream 0 using template query57.tpl and seed 2031708268
   2 with v1 as(
   3  select i_category, i_brand,
   4         cc_name,
   5         d_year, d_moy,
   6         sum(cs_sales_price) sum_sales,
   7         avg(sum(cs_sales_price)) over
   8           (partition by i_category, i_brand,
   9                      cc_name, d_year)
  10           avg_monthly_sales,
  11         rank() over
  12           (partition by i_category, i_brand,
  13                      cc_name
  14            order by d_year, d_moy) rn
  15  from item, catalog_sales, date_dim, call_center
  16  where cs_item_sk = i_item_sk and
  17        cs_sold_date_sk = d_date_sk and
  18        cc_call_center_sk= cs_call_center_sk and
  19        (
  20          d_year = 1999 or
  21          ( d_year = 1999-1 and d_moy =12) or
  22          ( d_year = 1999+1 and d_moy =1)
  23        )
  24  group by i_category, i_brand,
  25           cc_name , d_year, d_moy),
  26  v2 as(
  27  select v1.i_category, v1.i_brand
  28         ,v1.d_year, v1.d_moy
  29         ,v1.avg_monthly_sales
  30         ,v1.sum_sales, v1_lag.sum_sales psum, v1_lead.sum_sales nsum
  31  from v1, v1 v1_lag, v1 v1_lead
  32  where v1.i_category = v1_lag.i_category and
  33        v1.i_category = v1_lead.i_category and
  34        v1.i_brand = v1_lag.i_brand and
  35        v1.i_brand = v1_lead.i_brand and
  36        v1. cc_name = v1_lag. cc_name and
  37        v1. cc_name = v1_lead. cc_name and
  38        v1.rn = v1_lag.rn + 1 and
  39        v1.rn = v1_lead.rn - 1)
  40   select  *
  41  from v2
  42  where  d_year = 1999 and
  43         avg_monthly_sales > 0 and
  44         case when avg_monthly_sales > 0 then abs(sum_sales - avg_monthly_sales) / avg_monthly_sales else null end > 0.1
  45  order by sum_sales - avg_monthly_sales, avg_monthly_sales
  46  limit 100;
  47
  48 -- end query 1 in stream 0 using template query57.tpl