0001 WITH wss AS
0002 (SELECT
0003 d_week_seq,
0004 ss_store_sk,
0005 sum(CASE WHEN (d_day_name = 'Sunday')
0006 THEN ss_sales_price
0007 ELSE NULL END) sun_sales,
0008 sum(CASE WHEN (d_day_name = 'Monday')
0009 THEN ss_sales_price
0010 ELSE NULL END) mon_sales,
0011 sum(CASE WHEN (d_day_name = 'Tuesday')
0012 THEN ss_sales_price
0013 ELSE NULL END) tue_sales,
0014 sum(CASE WHEN (d_day_name = 'Wednesday')
0015 THEN ss_sales_price
0016 ELSE NULL END) wed_sales,
0017 sum(CASE WHEN (d_day_name = 'Thursday')
0018 THEN ss_sales_price
0019 ELSE NULL END) thu_sales,
0020 sum(CASE WHEN (d_day_name = 'Friday')
0021 THEN ss_sales_price
0022 ELSE NULL END) fri_sales,
0023 sum(CASE WHEN (d_day_name = 'Saturday')
0024 THEN ss_sales_price
0025 ELSE NULL END) sat_sales
0026 FROM store_sales, date_dim
0027 WHERE d_date_sk = ss_sold_date_sk
0028 GROUP BY d_week_seq, ss_store_sk
0029 )
0030 SELECT
0031 s_store_name1,
0032 s_store_id1,
0033 d_week_seq1,
0034 sun_sales1 / sun_sales2,
0035 mon_sales1 / mon_sales2,
0036 tue_sales1 / tue_sales2,
0037 wed_sales1 / wed_sales2,
0038 thu_sales1 / thu_sales2,
0039 fri_sales1 / fri_sales2,
0040 sat_sales1 / sat_sales2
0041 FROM
0042 (SELECT
0043 s_store_name s_store_name1,
0044 wss.d_week_seq d_week_seq1,
0045 s_store_id s_store_id1,
0046 sun_sales sun_sales1,
0047 mon_sales mon_sales1,
0048 tue_sales tue_sales1,
0049 wed_sales wed_sales1,
0050 thu_sales thu_sales1,
0051 fri_sales fri_sales1,
0052 sat_sales sat_sales1
0053 FROM wss, store, date_dim d
0054 WHERE d.d_week_seq = wss.d_week_seq AND
0055 ss_store_sk = s_store_sk AND
0056 d_month_seq BETWEEN 1212 AND 1212 + 11) y,
0057 (SELECT
0058 s_store_name s_store_name2,
0059 wss.d_week_seq d_week_seq2,
0060 s_store_id s_store_id2,
0061 sun_sales sun_sales2,
0062 mon_sales mon_sales2,
0063 tue_sales tue_sales2,
0064 wed_sales wed_sales2,
0065 thu_sales thu_sales2,
0066 fri_sales fri_sales2,
0067 sat_sales sat_sales2
0068 FROM wss, store, date_dim d
0069 WHERE d.d_week_seq = wss.d_week_seq AND
0070 ss_store_sk = s_store_sk AND
0071 d_month_seq BETWEEN 1212 + 12 AND 1212 + 23) x
0072 WHERE s_store_id1 = s_store_id2
0073 AND d_week_seq1 = d_week_seq2 - 52
0074 ORDER BY s_store_name1, s_store_id1, d_week_seq1
0075 LIMIT 100
