]>
Commit | Line | Data |
---|---|---|
aee94f69 TL |
1 | -- start query 1 in stream 0 using template query56.tpl and seed 1951559352 |
2 | with ss as ( | |
3 | select i_item_id,sum(ss_ext_sales_price) total_sales | |
4 | from | |
5 | store_sales, | |
6 | date_dim, | |
7 | customer_address, | |
8 | item | |
9 | where i_item_id in (select | |
10 | i_item_id | |
11 | from item | |
12 | where i_color in ('chiffon','smoke','lace')) | |
13 | and ss_item_sk = i_item_sk | |
14 | and ss_sold_date_sk = d_date_sk | |
15 | and d_year = 2001 | |
16 | and d_moy = 5 | |
17 | and ss_addr_sk = ca_address_sk | |
18 | and ca_gmt_offset = -6 | |
19 | group by i_item_id), | |
20 | cs as ( | |
21 | select i_item_id,sum(cs_ext_sales_price) total_sales | |
22 | from | |
23 | catalog_sales, | |
24 | date_dim, | |
25 | customer_address, | |
26 | item | |
27 | where | |
28 | i_item_id in (select | |
29 | i_item_id | |
30 | from item | |
31 | where i_color in ('chiffon','smoke','lace')) | |
32 | and cs_item_sk = i_item_sk | |
33 | and cs_sold_date_sk = d_date_sk | |
34 | and d_year = 2001 | |
35 | and d_moy = 5 | |
36 | and cs_bill_addr_sk = ca_address_sk | |
37 | and ca_gmt_offset = -6 | |
38 | group by i_item_id), | |
39 | ws as ( | |
40 | select i_item_id,sum(ws_ext_sales_price) total_sales | |
41 | from | |
42 | web_sales, | |
43 | date_dim, | |
44 | customer_address, | |
45 | item | |
46 | where | |
47 | i_item_id in (select | |
48 | i_item_id | |
49 | from item | |
50 | where i_color in ('chiffon','smoke','lace')) | |
51 | and ws_item_sk = i_item_sk | |
52 | and ws_sold_date_sk = d_date_sk | |
53 | and d_year = 2001 | |
54 | and d_moy = 5 | |
55 | and ws_bill_addr_sk = ca_address_sk | |
56 | and ca_gmt_offset = -6 | |
57 | group by i_item_id) | |
58 | select i_item_id ,sum(total_sales) total_sales | |
59 | from (select * from ss | |
60 | union all | |
61 | select * from cs | |
62 | union all | |
63 | select * from ws) tmp1 | |
64 | group by i_item_id | |
65 | order by total_sales, | |
66 | i_item_id | |
67 | limit 100; | |
68 | ||
69 | -- end query 1 in stream 0 using template query56.tpl |