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
|
CREATE OR REPLACE VIEW sales AS
SELECT
items.uuid AS item_uuid,
items.item_name AS item_name,
items.user_uuid AS seller_uuid,
items.start_date AS start_date,
items.end_date AS end_date,
best_bids.uuid AS best_bid_uuid,
best_bids.user_uuid AS best_bidder_uuid,
items.initial_price AS initial_price,
best_bids.offer AS best_offer,
COALESCE(charges.fee, 0) + COALESCE(charges.rate, 0) * best_bids.offer AS charges
FROM items
LEFT OUTER JOIN (
SELECT
DISTINCT
item_uuid,
first_value(offer)
OVER (
PARTITION BY item_uuid
ORDER BY offer DESC
) offer,
first_value(uuid)
OVER (
PARTITION BY item_uuid
ORDER BY offer DESC
) uuid,
first_value(user_uuid)
OVER (
PARTITION BY item_uuid
ORDER BY offer DESC
) user_uuid
FROM bids
) best_bids
ON best_bids.item_uuid = items.uuid
LEFT OUTER JOIN (
SELECT
start_date,
end_date,
SUM(fee) AS fee,
SUM(rate) AS rate
FROM charges
GROUP BY charges.start_date, charges.end_date
) charges
ON items.end_date BETWEEN charges.start_date AND charges.end_date;
|