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
|
SELECT
i_brand_id brand_id,
i_brand brand,
t_hour,
t_minute,
sum(ext_price) ext_price
FROM item,
(SELECT
ws_ext_sales_price AS ext_price,
ws_sold_date_sk AS sold_date_sk,
ws_item_sk AS sold_item_sk,
ws_sold_time_sk AS time_sk
FROM web_sales, date_dim
WHERE d_date_sk = ws_sold_date_sk
AND d_moy = 11
AND d_year = 1999
UNION ALL
SELECT
cs_ext_sales_price AS ext_price,
cs_sold_date_sk AS sold_date_sk,
cs_item_sk AS sold_item_sk,
cs_sold_time_sk AS time_sk
FROM catalog_sales, date_dim
WHERE d_date_sk = cs_sold_date_sk
AND d_moy = 11
AND d_year = 1999
UNION ALL
SELECT
ss_ext_sales_price AS ext_price,
ss_sold_date_sk AS sold_date_sk,
ss_item_sk AS sold_item_sk,
ss_sold_time_sk AS time_sk
FROM store_sales, date_dim
WHERE d_date_sk = ss_sold_date_sk
AND d_moy = 11
AND d_year = 1999
) AS tmp, time_dim
WHERE
sold_item_sk = i_item_sk
AND i_manager_id = 1
AND time_sk = t_time_sk
AND (t_meal_time = 'breakfast' OR t_meal_time = 'dinner')
GROUP BY i_brand, i_brand_id, t_hour, t_minute
ORDER BY ext_price DESC, brand_id
|
