resources/tpcds-modifiedQueries/q59.sql

0001 -- start query 59 in stream 0 using template query59.tpl
0002 with
0003   wss as
0004   (select
0005     d_week_seq,
0006     ss_store_sk,
0007     sum(case when (d_day_name = 'Sunday') then ss_sales_price else null end) sun_sales,
0008     sum(case when (d_day_name = 'Monday') then ss_sales_price else null end) mon_sales,
0009     sum(case when (d_day_name = 'Tuesday') then ss_sales_price else null end) tue_sales,
0010     sum(case when (d_day_name = 'Wednesday') then ss_sales_price else null end) wed_sales,
0011     sum(case when (d_day_name = 'Thursday') then ss_sales_price else null end) thu_sales,
0012     sum(case when (d_day_name = 'Friday') then ss_sales_price else null end) fri_sales,
0013     sum(case when (d_day_name = 'Saturday') then ss_sales_price else null end) sat_sales
0014   from
0015     store_sales,
0016     date_dim
0017   where
0018     d_date_sk = ss_sold_date_sk
0019   group by
0020     d_week_seq,
0021     ss_store_sk
0022   )
0023 select
0024   s_store_name1,
0025   s_store_id1,
0026   d_week_seq1,
0027   sun_sales1 / sun_sales2,
0028   mon_sales1 / mon_sales2,
0029   tue_sales1 / tue_sales1,
0030   wed_sales1 / wed_sales2,
0031   thu_sales1 / thu_sales2,
0032   fri_sales1 / fri_sales2,
0033   sat_sales1 / sat_sales2
0034 from
0035   (select
0036     s_store_name s_store_name1,
0037     wss.d_week_seq d_week_seq1,
0038     s_store_id s_store_id1,
0039     sun_sales sun_sales1,
0040     mon_sales mon_sales1,
0041     tue_sales tue_sales1,
0042     wed_sales wed_sales1,
0043     thu_sales thu_sales1,
0044     fri_sales fri_sales1,
0045     sat_sales sat_sales1
0046   from
0047     wss,
0048     store,
0049     date_dim d
0050   where
0051     d.d_week_seq = wss.d_week_seq
0052     and ss_store_sk = s_store_sk
0053     and d_month_seq between 1185 and 1185 + 11
0054   ) y,
0055   (select
0056     s_store_name s_store_name2,
0057     wss.d_week_seq d_week_seq2,
0058     s_store_id s_store_id2,
0059     sun_sales sun_sales2,
0060     mon_sales mon_sales2,
0061     tue_sales tue_sales2,
0062     wed_sales wed_sales2,
0063     thu_sales thu_sales2,
0064     fri_sales fri_sales2,
0065     sat_sales sat_sales2
0066   from
0067     wss,
0068     store,
0069     date_dim d
0070   where
0071     d.d_week_seq = wss.d_week_seq
0072     and ss_store_sk = s_store_sk
0073     and d_month_seq between 1185 + 12 and 1185 + 23
0074   ) x
0075 where
0076   s_store_id1 = s_store_id2
0077   and d_week_seq1 = d_week_seq2 - 52
0078 order by
0079   s_store_name1,
0080   s_store_id1,
0081   d_week_seq1
0082 limit 100
0083 -- end query 59 in stream 0 using template query59.tpl