AdvancedTopics: olap_fact_order_product.sql

-- FactOrderProduct


CREATE TABLE FactOrderProduct (
                                  order_product_key   BIGSERIAL,

                                  date_key            INTEGER       NOT NULL,
                                  customer_key        INTEGER       NOT NULL,
                                  hotel_key           INTEGER       NOT NULL,
                                  product_key         INTEGER       NOT NULL,

                                  quantity             INTEGER       NOT NULL,
                                  unit_price            NUMERIC(10,2) NOT NULL,
                                  total_price           NUMERIC(10,2) NOT NULL,
                                  order_status          VARCHAR(20)   NOT NULL,
                                  delivery_status        VARCHAR(20)
)
    PARTITION BY RANGE (date_key);

CREATE TABLE FactOrderProduct_2015
    PARTITION OF FactOrderProduct
        FOR VALUES FROM (20150101) TO (20160101);

CREATE TABLE FactOrderProduct_2016
    PARTITION OF FactOrderProduct
        FOR VALUES FROM (20160101) TO (20170101);

CREATE TABLE FactOrderProduct_2017
    PARTITION OF FactOrderProduct
        FOR VALUES FROM (20170101) TO (20180101);

CREATE TABLE FactOrderProduct_2018
    PARTITION OF FactOrderProduct
        FOR VALUES FROM (20180101) TO (20190101);

CREATE TABLE FactOrderProduct_2019
    PARTITION OF FactOrderProduct
        FOR VALUES FROM (20190101) TO (20200101);

CREATE TABLE FactOrderProduct_2020
    PARTITION OF FactOrderProduct
        FOR VALUES FROM (20200101) TO (20210101);

CREATE TABLE FactOrderProduct_2021
    PARTITION OF FactOrderProduct
        FOR VALUES FROM (20210101) TO (20220101);

CREATE TABLE FactOrderProduct_2022
    PARTITION OF FactOrderProduct
        FOR VALUES FROM (20220101) TO (20230101);

CREATE TABLE FactOrderProduct_2023
    PARTITION OF FactOrderProduct
        FOR VALUES FROM (20230101) TO (20240101);

CREATE TABLE FactOrderProduct_2024
    PARTITION OF FactOrderProduct
        FOR VALUES FROM (20240101) TO (20250101);

CREATE TABLE FactOrderProduct_2025
    PARTITION OF FactOrderProduct
        FOR VALUES FROM (20250101) TO (20260101);

CREATE TABLE FactOrderProduct_2026
    PARTITION OF FactOrderProduct
        FOR VALUES FROM (20260101) TO (20270101);

CREATE TABLE FactOrderProduct_2027
    PARTITION OF FactOrderProduct
        FOR VALUES FROM (20270101) TO (20280101);

CREATE INDEX idx_factop_product
    ON FactOrderProduct(product_key);

CREATE INDEX idx_factop_customer
    ON FactOrderProduct(customer_key);

CREATE INDEX idx_factop_hotel
    ON FactOrderProduct(hotel_key);



-- ETL

INSERT INTO FactOrderProduct (
    date_key,
    customer_key,
    hotel_key,
    product_key,
    quantity,
    unit_price,
    total_price,
    order_status,
    delivery_status
)
SELECT
    TO_CHAR(o.order_date, 'YYYYMMDD')::INTEGER,
    dc.customer_key,
    dh.hotel_key,
    dp.product_key,
    op.quantity,
    op.unit_price,
    (op.quantity * op.unit_price),
    os.name,
    ds.name
FROM OrderProduct op
         JOIN "Order"      o  ON o.order_id     = op.order_id
         JOIN OrderStatus  os ON os.order_status_id = o.status_id
         JOIN DimCustomer  dc ON dc.customer_id = o.customer_id
    AND dc.is_current  = TRUE
         JOIN DimHotel     dh ON dh.hotel_id    = o.hotel_id
         JOIN DimProduct   dp ON dp.product_id  = op.product_id
         LEFT JOIN Delivery        d  ON d.purchase_id = op.orderproduct_id
         LEFT JOIN DeliveryStatus  ds ON ds.delivery_status_id = d.status_id;