forked from agirish/tpcds
-
Notifications
You must be signed in to change notification settings - Fork 0
/
query5.sql
128 lines (127 loc) · 6.15 KB
/
query5.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
-- start query 5 in stream 0 using template query5.tpl
WITH ssr AS
(
SELECT s_store_id,
Sum(sales_price) AS sales,
Sum(profit) AS profit,
Sum(return_amt) AS returns1,
Sum(net_loss) AS profit_loss
FROM (
SELECT ss_store_sk AS store_sk,
ss_sold_date_sk AS date_sk,
ss_ext_sales_price AS sales_price,
ss_net_profit AS profit,
Cast(0 AS DECIMAL(7,2)) AS return_amt,
Cast(0 AS DECIMAL(7,2)) AS net_loss
FROM store_sales
UNION ALL
SELECT sr_store_sk AS store_sk,
sr_returned_date_sk AS date_sk,
Cast(0 AS DECIMAL(7,2)) AS sales_price,
Cast(0 AS DECIMAL(7,2)) AS profit,
sr_return_amt AS return_amt,
sr_net_loss AS net_loss
FROM store_returns ) salesreturns,
date_dim,
store
WHERE date_sk = d_date_sk
AND d_date BETWEEN Cast('2002-08-22' AS DATE) AND (
Cast('2002-08-22' AS DATE) + INTERVAL '14' day)
AND store_sk = s_store_sk
GROUP BY s_store_id) , csr AS
(
SELECT cp_catalog_page_id,
sum(sales_price) AS sales,
sum(profit) AS profit,
sum(return_amt) AS returns1,
sum(net_loss) AS profit_loss
FROM (
SELECT cs_catalog_page_sk AS page_sk,
cs_sold_date_sk AS date_sk,
cs_ext_sales_price AS sales_price,
cs_net_profit AS profit,
cast(0 AS decimal(7,2)) AS return_amt,
cast(0 AS decimal(7,2)) AS net_loss
FROM catalog_sales
UNION ALL
SELECT cr_catalog_page_sk AS page_sk,
cr_returned_date_sk AS date_sk,
cast(0 AS decimal(7,2)) AS sales_price,
cast(0 AS decimal(7,2)) AS profit,
cr_return_amount AS return_amt,
cr_net_loss AS net_loss
FROM catalog_returns ) salesreturns,
date_dim,
catalog_page
WHERE date_sk = d_date_sk
AND d_date BETWEEN cast('2002-08-22' AS date) AND (
cast('2002-08-22' AS date) + INTERVAL '14' day)
AND page_sk = cp_catalog_page_sk
GROUP BY cp_catalog_page_id) , wsr AS
(
SELECT web_site_id,
sum(sales_price) AS sales,
sum(profit) AS profit,
sum(return_amt) AS returns1,
sum(net_loss) AS profit_loss
FROM (
SELECT ws_web_site_sk AS wsr_web_site_sk,
ws_sold_date_sk AS date_sk,
ws_ext_sales_price AS sales_price,
ws_net_profit AS profit,
cast(0 AS decimal(7,2)) AS return_amt,
cast(0 AS decimal(7,2)) AS net_loss
FROM web_sales
UNION ALL
SELECT ws_web_site_sk AS wsr_web_site_sk,
wr_returned_date_sk AS date_sk,
cast(0 AS decimal(7,2)) AS sales_price,
cast(0 AS decimal(7,2)) AS profit,
wr_return_amt AS return_amt,
wr_net_loss AS net_loss
FROM web_returns
LEFT OUTER JOIN web_sales
ON (
wr_item_sk = ws_item_sk
AND wr_order_number = ws_order_number) ) salesreturns,
date_dim,
web_site
WHERE date_sk = d_date_sk
AND d_date BETWEEN cast('2002-08-22' AS date) AND (
cast('2002-08-22' AS date) + INTERVAL '14' day)
AND wsr_web_site_sk = web_site_sk
GROUP BY web_site_id)
SELECT
channel ,
id ,
sum(sales) AS sales ,
sum(returns1) AS returns1 ,
sum(profit) AS profit
FROM (
SELECT 'store channel' AS channel ,
'store'
|| s_store_id AS id ,
sales ,
returns1 ,
(profit - profit_loss) AS profit
FROM ssr
UNION ALL
SELECT 'catalog channel' AS channel ,
'catalog_page'
|| cp_catalog_page_id AS id ,
sales ,
returns1 ,
(profit - profit_loss) AS profit
FROM csr
UNION ALL
SELECT 'web channel' AS channel ,
'web_site'
|| web_site_id AS id ,
sales ,
returns1 ,
(profit - profit_loss) AS profit
FROM wsr ) x
GROUP BY rollup (channel, id)
ORDER BY channel ,
id
LIMIT 100;