forked from yuananf/tpcds-presto
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathq31.sql
60 lines (60 loc) · 1.68 KB
/
q31.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
WITH ss AS
(SELECT
ca_county,
d_qoy,
d_year,
sum(ss_ext_sales_price) AS store_sales
FROM store_sales, date_dim, customer_address
WHERE ss_sold_date_sk = d_date_sk
AND ss_addr_sk = ca_address_sk
GROUP BY ca_county, d_qoy, d_year),
ws AS
(SELECT
ca_county,
d_qoy,
d_year,
sum(ws_ext_sales_price) AS web_sales
FROM web_sales, date_dim, customer_address
WHERE ws_sold_date_sk = d_date_sk
AND ws_bill_addr_sk = ca_address_sk
GROUP BY ca_county, d_qoy, d_year)
SELECT
ss1.ca_county,
ss1.d_year,
ws2.web_sales / ws1.web_sales web_q1_q2_increase,
ss2.store_sales / ss1.store_sales store_q1_q2_increase,
ws3.web_sales / ws2.web_sales web_q2_q3_increase,
ss3.store_sales / ss2.store_sales store_q2_q3_increase
FROM
ss ss1, ss ss2, ss ss3, ws ws1, ws ws2, ws ws3
WHERE
ss1.d_qoy = 1
AND ss1.d_year = 2000
AND ss1.ca_county = ss2.ca_county
AND ss2.d_qoy = 2
AND ss2.d_year = 2000
AND ss2.ca_county = ss3.ca_county
AND ss3.d_qoy = 3
AND ss3.d_year = 2000
AND ss1.ca_county = ws1.ca_county
AND ws1.d_qoy = 1
AND ws1.d_year = 2000
AND ws1.ca_county = ws2.ca_county
AND ws2.d_qoy = 2
AND ws2.d_year = 2000
AND ws1.ca_county = ws3.ca_county
AND ws3.d_qoy = 3
AND ws3.d_year = 2000
AND CASE WHEN ws1.web_sales > 0
THEN ws2.web_sales / ws1.web_sales
ELSE NULL END
> CASE WHEN ss1.store_sales > 0
THEN ss2.store_sales / ss1.store_sales
ELSE NULL END
AND CASE WHEN ws2.web_sales > 0
THEN ws3.web_sales / ws2.web_sales
ELSE NULL END
> CASE WHEN ss2.store_sales > 0
THEN ss3.store_sales / ss2.store_sales
ELSE NULL END
ORDER BY ss1.ca_county;