aboutsummaryrefslogtreecommitdiff
path: root/res/sql/views/pg_sales.sql
blob: f1705f1daacf29d26bbbb6332af16d8c23397382 (plain)
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
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 ON (item_uuid) *
                      FROM bids
                      ORDER BY item_uuid, offer DESC
                    ) 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;