tpcds.query02.sql Maven / Gradle / Ivy

Go to download
WITH wscs AS (
		SELECT sold_date_sk, sales_price
		FROM (
			SELECT ws_sold_date_sk AS sold_date_sk, ws_ext_sales_price AS sales_price
			FROM web_sales
			UNION ALL
			SELECT cs_sold_date_sk AS sold_date_sk, cs_ext_sales_price AS sales_price
			FROM catalog_sales
		) x
	), 
	wswscs AS (
		SELECT d_week_seq
			, sum(CASE 
				WHEN d_day_name = 'Sunday' THEN sales_price
				ELSE NULL
			END) AS sun_sales
			, sum(CASE 
				WHEN d_day_name = 'Monday' THEN sales_price
				ELSE NULL
			END) AS mon_sales
			, sum(CASE 
				WHEN d_day_name = 'Tuesday' THEN sales_price
				ELSE NULL
			END) AS tue_sales
			, sum(CASE 
				WHEN d_day_name = 'Wednesday' THEN sales_price
				ELSE NULL
			END) AS wed_sales
			, sum(CASE 
				WHEN d_day_name = 'Thursday' THEN sales_price
				ELSE NULL
			END) AS thu_sales
			, sum(CASE 
				WHEN d_day_name = 'Friday' THEN sales_price
				ELSE NULL
			END) AS fri_sales
			, sum(CASE 
				WHEN d_day_name = 'Saturday' THEN sales_price
				ELSE NULL
			END) AS sat_sales
		FROM wscs, date_dim
		WHERE d_date_sk = sold_date_sk
		GROUP BY d_week_seq
	)
SELECT d_week_seq1, round(sun_sales1 / sun_sales2, 2)
	, round(mon_sales1 / mon_sales2, 2)
	, round(tue_sales1 / tue_sales2, 2)
	, round(wed_sales1 / wed_sales2, 2)
	, round(thu_sales1 / thu_sales2, 2)
	, round(fri_sales1 / fri_sales2, 2)
	, round(sat_sales1 / sat_sales2, 2)
FROM (
	SELECT wswscs.d_week_seq AS d_week_seq1, sun_sales AS sun_sales1, mon_sales AS mon_sales1, tue_sales AS tue_sales1, wed_sales AS wed_sales1
		, thu_sales AS thu_sales1, fri_sales AS fri_sales1, sat_sales AS sat_sales1
	FROM wswscs, date_dim
	WHERE date_dim.d_week_seq = wswscs.d_week_seq
		AND d_year = 2001
) y, (
		SELECT wswscs.d_week_seq AS d_week_seq2, sun_sales AS sun_sales2, mon_sales AS mon_sales2, tue_sales AS tue_sales2, wed_sales AS wed_sales2
			, thu_sales AS thu_sales2, fri_sales AS fri_sales2, sat_sales AS sat_sales2
		FROM wswscs, date_dim
		WHERE date_dim.d_week_seq = wswscs.d_week_seq
			AND d_year = 2001 + 1
	) z
WHERE d_week_seq1 = d_week_seq2 - 53
ORDER BY d_week_seq1