🏗️ Hurtownie Danych

-- Tabela faktów (FACT TABLE): zawiera klucze obce + miary
CREATE TABLE fact_sales (
    sale_id       NUMBER PRIMARY KEY,
    time_id       NUMBER REFERENCES dim_time(time_id),
    product_id    NUMBER REFERENCES dim_product(product_id),
    customer_id   NUMBER REFERENCES dim_customer(customer_id),
    channel_id    NUMBER REFERENCES dim_channel(channel_id),
    amount_sold   NUMBER(10,2),   -- miara
    quantity_sold NUMBER,          -- miara
    cost          NUMBER(10,2)     -- miara
);

-- Tabela wymiaru (DIMENSION TABLE): płaska, zdenormalizowana
CREATE TABLE dim_product (
    product_id    NUMBER PRIMARY KEY,
    product_name  VARCHAR2(100),
    category      VARCHAR2(50),    -- denormalizacja (w 3NF byłaby osobna tabela)
    subcategory   VARCHAR2(50),
    unit_price    NUMBER(8,2)
);

-- Płatek śniegu: product → subcategory → category (3 tabele)
CREATE TABLE dim_category (
    category_id   NUMBER PRIMARY KEY,
    category_name VARCHAR2(50)
);
CREATE TABLE dim_subcategory (
    subcategory_id   NUMBER PRIMARY KEY,
    subcategory_name VARCHAR2(50),
    category_id      NUMBER REFERENCES dim_category(category_id)
);
CREATE TABLE dim_product_sf (
    product_id     NUMBER PRIMARY KEY,
    product_name   VARCHAR2(100),
    subcategory_id NUMBER REFERENCES dim_subcategory(subcategory_id)
);
-- Zapytanie wymaga 3 JOINów zamiast 1

-- Konstelacja: SALES + COSTS współdzielą wymiary
-- fact_sales(prod_id FK, time_id FK, channel_id FK, amount_sold, quantity_sold)
-- fact_costs(prod_id FK, time_id FK, channel_id FK, unit_cost, unit_price)
-- dim_products(prod_id PK, prod_name, prod_category)
-- dim_times(time_id PK, calendar_year, calendar_month_number)
-- dim_channels(channel_id PK, channel_desc)

-- Zapytanie na konstelacji: marża = sprzedaż - koszty:
SELECT p.prod_name,
       SUM(s.amount_sold) AS przychod,
       SUM(c.unit_cost * s.quantity_sold) AS koszt,
       SUM(s.amount_sold - c.unit_cost * s.quantity_sold) AS marza
FROM sh.sales s
JOIN sh.costs c    ON s.prod_id = c.prod_id AND s.time_id = c.time_id
                  AND s.channel_id = c.channel_id
JOIN sh.products p ON s.prod_id = p.prod_id
GROUP BY p.prod_name
ORDER BY marza DESC;

-- Przykład: które produkty były na promocji w danym dniu (bez kwot)?
CREATE TABLE fact_promo_coverage (
    time_id     NUMBER REFERENCES dim_times(time_id),
    prod_id     NUMBER REFERENCES dim_products(prod_id),
    promo_id    NUMBER REFERENCES dim_promotions(promo_id),
    store_id    NUMBER REFERENCES dim_stores(store_id)
    -- brak kolumn miar!
);

-- Pytanie: ile produktów objęto promocją 2023-01-15?
SELECT COUNT(*) FROM fact_promo_coverage
WHERE time_id = (SELECT time_id FROM dim_times WHERE calendar_date = DATE '2023-01-15');

-- Typowe użycia: frekwencja pracownicza, dostępność produktów,
-- uczestnictwo klientów w szkoleniach

-- Sprzedaż wg roku i kategorii produktu:
SELECT t.calendar_year,
       p.prod_category,
       SUM(s.amount_sold)   AS sprzedaz,
       SUM(s.quantity_sold) AS ilosc
FROM sh.sales s
JOIN sh.times    t ON s.time_id   = t.time_id
JOIN sh.products p ON s.prod_id   = p.prod_id
GROUP BY t.calendar_year, p.prod_category
ORDER BY t.calendar_year, sprzedaz DESC;

-- TOP 5 klientów wg wartości zakupów:
SELECT cust_id, SUM(amount_sold) AS total
FROM sh.sales
GROUP BY cust_id
ORDER BY total DESC
FETCH FIRST 5 ROWS ONLY;

-- Marża: sprzedaż minus koszty (JOIN między dwiema tabelami faktów):
SELECT s.prod_id,
       SUM(s.amount_sold) AS przychod,
       SUM(c.unit_cost * s.quantity_sold) AS koszt,
       SUM(s.amount_sold) - SUM(c.unit_cost * s.quantity_sold) AS marza
FROM sh.sales s
JOIN sh.costs c ON s.prod_id = c.prod_id AND s.time_id = c.time_id
GROUP BY s.prod_id;

-- Sprzedaż: rok → kwartał → miesiąc → total
SELECT t.calendar_year,
       t.calendar_quarter_number,
       t.calendar_month_number,
       SUM(s.amount_sold) AS sprzedaz
FROM sh.sales s
JOIN sh.times t ON s.time_id = t.time_id
GROUP BY ROLLUP(t.calendar_year, t.calendar_quarter_number, t.calendar_month_number)
ORDER BY t.calendar_year NULLS LAST,
         t.calendar_quarter_number NULLS LAST,
         t.calendar_month_number NULLS LAST;