]>
Commit | Line | Data |
---|---|---|
aee94f69 TL |
1 | -- start query 1 in stream 0 using template query54.tpl and seed 1930872976 |
2 | with my_customers as ( | |
3 | select distinct c_customer_sk | |
4 | , c_current_addr_sk | |
5 | from | |
6 | ( select cs_sold_date_sk sold_date_sk, | |
7 | cs_bill_customer_sk customer_sk, | |
8 | cs_item_sk item_sk | |
9 | from catalog_sales | |
10 | union all | |
11 | select ws_sold_date_sk sold_date_sk, | |
12 | ws_bill_customer_sk customer_sk, | |
13 | ws_item_sk item_sk | |
14 | from web_sales | |
15 | ) cs_or_ws_sales, | |
16 | item, | |
17 | date_dim, | |
18 | customer | |
19 | where sold_date_sk = d_date_sk | |
20 | and item_sk = i_item_sk | |
21 | and i_category = 'Books' | |
22 | and i_class = 'business' | |
23 | and c_customer_sk = cs_or_ws_sales.customer_sk | |
24 | and d_moy = 2 | |
25 | and d_year = 2000 | |
26 | ) | |
27 | , my_revenue as ( | |
28 | select c_customer_sk, | |
29 | sum(ss_ext_sales_price) as revenue | |
30 | from my_customers, | |
31 | store_sales, | |
32 | customer_address, | |
33 | store, | |
34 | date_dim | |
35 | where c_current_addr_sk = ca_address_sk | |
36 | and ca_county = s_county | |
37 | and ca_state = s_state | |
38 | and ss_sold_date_sk = d_date_sk | |
39 | and c_customer_sk = ss_customer_sk | |
40 | and d_month_seq between (select distinct d_month_seq+1 | |
41 | from date_dim where d_year = 2000 and d_moy = 2) | |
42 | and (select distinct d_month_seq+3 | |
43 | from date_dim where d_year = 2000 and d_moy = 2) | |
44 | group by c_customer_sk | |
45 | ) | |
46 | , segments as | |
47 | (select cast((revenue/50) as int) as segment | |
48 | from my_revenue | |
49 | ) | |
50 | select segment, count(*) as num_customers, segment*50 as segment_base | |
51 | from segments | |
52 | group by segment | |
53 | order by segment, num_customers | |
54 | limit 100; | |
55 | ||
56 | -- end query 1 in stream 0 using template query54.tpl |