0001 WITH wscs AS
0002 ( SELECT
0003 sold_date_sk,
0004 sales_price
0005 FROM (SELECT
0006 ws_sold_date_sk sold_date_sk,
0007 ws_ext_sales_price sales_price
0008 FROM web_sales) x
0009 UNION ALL
0010 (SELECT
0011 cs_sold_date_sk sold_date_sk,
0012 cs_ext_sales_price sales_price
0013 FROM catalog_sales)),
0014 wswscs AS
0015 ( SELECT
0016 d_week_seq,
0017 sum(CASE WHEN (d_day_name = 'Sunday')
0018 THEN sales_price
0019 ELSE NULL END)
0020 sun_sales,
0021 sum(CASE WHEN (d_day_name = 'Monday')
0022 THEN sales_price
0023 ELSE NULL END)
0024 mon_sales,
0025 sum(CASE WHEN (d_day_name = 'Tuesday')
0026 THEN sales_price
0027 ELSE NULL END)
0028 tue_sales,
0029 sum(CASE WHEN (d_day_name = 'Wednesday')
0030 THEN sales_price
0031 ELSE NULL END)
0032 wed_sales,
0033 sum(CASE WHEN (d_day_name = 'Thursday')
0034 THEN sales_price
0035 ELSE NULL END)
0036 thu_sales,
0037 sum(CASE WHEN (d_day_name = 'Friday')
0038 THEN sales_price
0039 ELSE NULL END)
0040 fri_sales,
0041 sum(CASE WHEN (d_day_name = 'Saturday')
0042 THEN sales_price
0043 ELSE NULL END)
0044 sat_sales
0045 FROM wscs, date_dim
0046 WHERE d_date_sk = sold_date_sk
0047 GROUP BY d_week_seq)
0048 SELECT
0049 d_week_seq1,
0050 round(sun_sales1 / sun_sales2, 2),
0051 round(mon_sales1 / mon_sales2, 2),
0052 round(tue_sales1 / tue_sales2, 2),
0053 round(wed_sales1 / wed_sales2, 2),
0054 round(thu_sales1 / thu_sales2, 2),
0055 round(fri_sales1 / fri_sales2, 2),
0056 round(sat_sales1 / sat_sales2, 2)
0057 FROM
0058 (SELECT
0059 wswscs.d_week_seq d_week_seq1,
0060 sun_sales sun_sales1,
0061 mon_sales mon_sales1,
0062 tue_sales tue_sales1,
0063 wed_sales wed_sales1,
0064 thu_sales thu_sales1,
0065 fri_sales fri_sales1,
0066 sat_sales sat_sales1
0067 FROM wswscs, date_dim
0068 WHERE date_dim.d_week_seq = wswscs.d_week_seq AND d_year = 2001) y,
0069 (SELECT
0070 wswscs.d_week_seq d_week_seq2,
0071 sun_sales sun_sales2,
0072 mon_sales mon_sales2,
0073 tue_sales tue_sales2,
0074 wed_sales wed_sales2,
0075 thu_sales thu_sales2,
0076 fri_sales fri_sales2,
0077 sat_sales sat_sales2
0078 FROM wswscs, date_dim
0079 WHERE date_dim.d_week_seq = wswscs.d_week_seq AND d_year = 2001 + 1) z
0080 WHERE d_week_seq1 = d_week_seq2 - 53
0081 ORDER BY d_week_seq1
