-
Notifications
You must be signed in to change notification settings - Fork 32
/
query23.sql
136 lines (135 loc) · 5.45 KB
/
query23.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
128
129
130
131
132
133
134
135
136
-- start query 23 in stream 0 using template query23.tpl
WITH frequent_ss_items
AS (SELECT Substr(i_item_desc, 1, 30) itemdesc,
i_item_sk item_sk,
d_date solddate,
Count(*) cnt
FROM store_sales,
date_dim,
item
WHERE ss_sold_date_sk = d_date_sk
AND ss_item_sk = i_item_sk
AND d_year IN ( 1998, 1998 + 1, 1998 + 2, 1998 + 3 )
GROUP BY Substr(i_item_desc, 1, 30),
i_item_sk,
d_date
HAVING Count(*) > 4),
max_store_sales
AS (SELECT Max(csales) tpcds_cmax
FROM (SELECT c_customer_sk,
Sum(ss_quantity * ss_sales_price) csales
FROM store_sales,
customer,
date_dim
WHERE ss_customer_sk = c_customer_sk
AND ss_sold_date_sk = d_date_sk
AND d_year IN ( 1998, 1998 + 1, 1998 + 2, 1998 + 3 )
GROUP BY c_customer_sk)),
best_ss_customer
AS (SELECT c_customer_sk,
Sum(ss_quantity * ss_sales_price) ssales
FROM store_sales,
customer
WHERE ss_customer_sk = c_customer_sk
GROUP BY c_customer_sk
HAVING Sum(ss_quantity * ss_sales_price) >
( 95 / 100.0 ) * (SELECT *
FROM max_store_sales))
SELECT Sum(sales)
FROM (SELECT cs_quantity * cs_list_price sales
FROM catalog_sales,
date_dim
WHERE d_year = 1998
AND d_moy = 6
AND cs_sold_date_sk = d_date_sk
AND cs_item_sk IN (SELECT item_sk
FROM frequent_ss_items)
AND cs_bill_customer_sk IN (SELECT c_customer_sk
FROM best_ss_customer)
UNION ALL
SELECT ws_quantity * ws_list_price sales
FROM web_sales,
date_dim
WHERE d_year = 1998
AND d_moy = 6
AND ws_sold_date_sk = d_date_sk
AND ws_item_sk IN (SELECT item_sk
FROM frequent_ss_items)
AND ws_bill_customer_sk IN (SELECT c_customer_sk
FROM best_ss_customer)) LIMIT 100;
WITH frequent_ss_items
AS (SELECT Substr(i_item_desc, 1, 30) itemdesc,
i_item_sk item_sk,
d_date solddate,
Count(*) cnt
FROM store_sales,
date_dim,
item
WHERE ss_sold_date_sk = d_date_sk
AND ss_item_sk = i_item_sk
AND d_year IN ( 1998, 1998 + 1, 1998 + 2, 1998 + 3 )
GROUP BY Substr(i_item_desc, 1, 30),
i_item_sk,
d_date
HAVING Count(*) > 4),
max_store_sales
AS (SELECT Max(csales) tpcds_cmax
FROM (SELECT c_customer_sk,
Sum(ss_quantity * ss_sales_price) csales
FROM store_sales,
customer,
date_dim
WHERE ss_customer_sk = c_customer_sk
AND ss_sold_date_sk = d_date_sk
AND d_year IN ( 1998, 1998 + 1, 1998 + 2, 1998 + 3 )
GROUP BY c_customer_sk)),
best_ss_customer
AS (SELECT c_customer_sk,
Sum(ss_quantity * ss_sales_price) ssales
FROM store_sales,
customer
WHERE ss_customer_sk = c_customer_sk
GROUP BY c_customer_sk
HAVING Sum(ss_quantity * ss_sales_price) >
( 95 / 100.0 ) * (SELECT *
FROM max_store_sales))
SELECT c_last_name,
c_first_name,
sales
FROM (SELECT c_last_name,
c_first_name,
Sum(cs_quantity * cs_list_price) sales
FROM catalog_sales,
customer,
date_dim
WHERE d_year = 1998
AND d_moy = 6
AND cs_sold_date_sk = d_date_sk
AND cs_item_sk IN (SELECT item_sk
FROM frequent_ss_items)
AND cs_bill_customer_sk IN (SELECT c_customer_sk
FROM best_ss_customer)
AND cs_bill_customer_sk = c_customer_sk
GROUP BY c_last_name,
c_first_name
UNION ALL
SELECT c_last_name,
c_first_name,
Sum(ws_quantity * ws_list_price) sales
FROM web_sales,
customer,
date_dim
WHERE d_year = 1998
AND d_moy = 6
AND ws_sold_date_sk = d_date_sk
AND ws_item_sk IN (SELECT item_sk
FROM frequent_ss_items)
AND ws_bill_customer_sk IN (SELECT c_customer_sk
FROM best_ss_customer)
AND ws_bill_customer_sk = c_customer_sk
GROUP BY c_last_name,
c_first_name)
ORDER BY c_last_name,
c_first_name,
sales
LIMIT 100;