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
|
--!syntax_pg
--TPC-DS Q54
-- start query 1 in stream 0 using template ../query_templates/query54.tpl
with my_customers as (
select distinct c_customer_sk
, c_current_addr_sk
from
( select cs_sold_date_sk sold_date_sk,
cs_bill_customer_sk customer_sk,
cs_item_sk item_sk
from plato.catalog_sales
union all
select ws_sold_date_sk sold_date_sk,
ws_bill_customer_sk customer_sk,
ws_item_sk item_sk
from plato.web_sales
) cs_or_ws_sales,
plato.item,
plato.date_dim,
plato.customer
where sold_date_sk = d_date_sk
and item_sk = i_item_sk
and i_category = 'Jewelry'
and i_class = 'consignment'
and c_customer_sk = cs_or_ws_sales.customer_sk
and d_moy = 3
and d_year = 1999
)
, my_revenue as (
select c_customer_sk,
sum(ss_ext_sales_price) as revenue
from my_customers,
plato.store_sales,
plato.customer_address,
plato.store,
plato.date_dim
where c_current_addr_sk = ca_address_sk
and ca_county = s_county
and ca_state = s_state
and ss_sold_date_sk = d_date_sk
and c_customer_sk = ss_customer_sk
and d_month_seq between (select distinct d_month_seq+1
from plato.date_dim where d_year = 1999 and d_moy = 3)
and (select distinct d_month_seq+3
from plato.date_dim where d_year = 1999 and d_moy = 3)
group by c_customer_sk
)
, segments as
(select cast((revenue/50::numeric) as int) as segment
from my_revenue
)
select segment, count(*) as num_customers, segment*50 as segment_base
from segments
group by segment
order by segment, num_customers
limit 100;
-- end query 1 in stream 0 using template ../query_templates/query54.tpl
|
