AdvancedTopics: olap_queries.sql

-- OLAP queries


-- 1) revenues - ROLLUP po hotel, godina, kvartal

SELECT
    COALESCE(h.hotel_name, '──')        AS hotel,
    COALESCE(d.year::TEXT,  '──')        AS year,
    COALESCE(d.quarter::TEXT,'──')       AS quarter,
    COUNT(*)                                     AS reservations,
    SUM(f.total_cost)                            AS revenue,
    ROUND(AVG(f.total_cost), 2)                  AS avg_per_reservation
FROM FactReservation f
         JOIN DimDate  d ON d.date_key  = f.date_key
         JOIN DimHotel h ON h.hotel_key = f.hotel_key
WHERE f.status != 'cancelled'
GROUP BY ROLLUP(h.hotel_name, d.year, d.quarter)
ORDER BY h.hotel_name, d.year, d.quarter;


-- 2) occupancy Rate po hotel i sezona - CUBE
SELECT
    COALESCE(h.hotel_name, '──') AS hotel,
    COALESCE(d.season,      '──') AS season,
    COUNT(*)                                  AS total_reservations,
    COUNT(*) FILTER (WHERE f.status = 'completed') AS completed,
    ROUND(
                    COUNT(*) FILTER (WHERE f.status = 'completed') * 100.0
                / NULLIF(COUNT(*), 0)
        , 1)                                      AS occupancy_pct,
    ROUND(AVG(f.nights_stayed), 1)            AS avg_nights
FROM FactReservation f
         JOIN DimDate  d ON d.date_key  = f.date_key
         JOIN DimHotel h ON h.hotel_key = f.hotel_key
GROUP BY CUBE(h.hotel_name, d.season)
ORDER BY h.hotel_name, d.season;


-- 3) Drill Down - pronaogjanje na pricini za pagjanje na prihodot

--1. po godina
SELECT
    d.year,
    SUM(f.total_cost)           AS revenue,
    LAG(SUM(f.total_cost)) OVER (ORDER BY d.year) AS prev_year,
    ROUND(
            (SUM(f.total_cost) - LAG(SUM(f.total_cost)) OVER (ORDER BY d.year))
                * 100.0
                / NULLIF(LAG(SUM(f.total_cost)) OVER (ORDER BY d.year), 0)
        , 1)                        AS growth_pct   -- % промена vs минатата година
FROM FactReservation f
         JOIN DimDate d ON d.date_key = f.date_key
WHERE f.status != 'cancelled'
GROUP BY d.year
ORDER BY d.year;

--2. za konkretna godina po hotel
SELECT
    h.hotel_name,
    SUM(f.total_cost) AS revenue
FROM FactReservation f
         JOIN DimDate  d ON d.date_key  = f.date_key
         JOIN DimHotel h ON h.hotel_key = f.hotel_key
WHERE d.year   = 2022
  AND f.status != 'cancelled'
GROUP BY h.hotel_name
ORDER BY revenue;

--3. za konkreten hotel po species
SELECT
    s.species_name,
    COUNT(*)          AS reservations,
    SUM(f.total_cost) AS revenue
FROM FactReservation f
         JOIN DimDate    d ON d.date_key   = f.date_key
         JOIN DimHotel   h ON h.hotel_key  = f.hotel_key
         JOIN DimPet     p ON p.pet_key    = f.pet_key
         JOIN DimSpecies s ON p.species_key = s.species_key
WHERE d.year        = 2022
  AND h.hotel_name  = 'Fur Haven'
  AND f.status     != 'cancelled'
GROUP BY s.species_name
ORDER BY revenue;

--4. za konkreten species po mesec
SELECT
    d.month_name,
    d.month_num,
    COUNT(*)          AS reservations,
    SUM(f.total_cost) AS revenue
FROM FactReservation f
         JOIN DimDate    d ON d.date_key   = f.date_key
         JOIN DimHotel   h ON h.hotel_key  = f.hotel_key
         JOIN DimPet     p ON p.pet_key    = f.pet_key
         JOIN DimSpecies s ON p.species_key = s.species_key
WHERE d.year        = 2022
  AND h.hotel_name  = 'Fur Haven'
  AND s.species_name = 'Dog'
  AND f.status      != 'cancelled'
GROUP BY d.month_name, d.month_num
ORDER BY d.month_num;



-- 4) top uslugi po prihod - Window functions
SELECT
    ds.service_name,
    h.hotel_name,
    COUNT(*)                                    AS times_used,
    SUM(f.price)                                AS total_revenue,

    RANK() OVER (
        PARTITION BY h.hotel_name
        ORDER BY SUM(f.price) DESC
        )                                           AS rank_in_hotel,

    ROUND(
            SUM(f.price) * 100.0
                / SUM(SUM(f.price)) OVER (PARTITION BY h.hotel_name)
        , 1)                                        AS pct_of_hotel_revenue
FROM FactServiceUsage f
         JOIN DimService ds ON ds.service_key = f.service_key
         JOIN DimHotel   h  ON h.hotel_key    = f.hotel_key
WHERE f.status = 'completed'
GROUP BY ds.service_name, h.hotel_name
ORDER BY h.hotel_name, rank_in_hotel;



-- 5) Customer Lifetime Value - najvredni klienti
SELECT
    c.customer_key,
    c.full_name,
    c.customer_segment,
    COUNT(DISTINCT f.reservation_key)   AS total_reservations,
    SUM(f.total_cost)                   AS lifetime_value,
    ROUND(AVG(f.total_cost), 2)         AS avg_per_visit,
    MIN(d.full_date)                    AS first_visit,
    MAX(d.full_date)                    AS last_visit,
    RANK() OVER (
        ORDER BY SUM(f.total_cost) DESC
        )                                   AS overall_rank,
    RANK() OVER (
        PARTITION BY c.customer_segment
        ORDER BY SUM(f.total_cost) DESC
        )                                   AS segment_rank
FROM FactReservation f
         JOIN DimCustomer c ON c.customer_key = f.customer_key
         JOIN DimDate     d ON d.date_key     = f.date_key
WHERE f.status = 'completed'
GROUP BY c.customer_key, c.full_name, c.customer_segment
ORDER BY lifetime_value DESC
LIMIT 20;

-- 6) prodazba po kategorija, hotel - CUBE
SELECT
    COALESCE(dp.category_name, '──')  AS category,
    COALESCE(h.hotel_name,     '──')  AS hotel,
    COALESCE(dp.price_range,   '──')  AS price_range,
    SUM(f.quantity)                        AS units_sold,
    SUM(f.total_price)                     AS revenue,
    ROUND(AVG(f.unit_price), 2)            AS avg_price
FROM FactOrderProduct f
         JOIN DimProduct dp ON dp.product_key = f.product_key
         JOIN DimHotel   h  ON h.hotel_key    = f.hotel_key
WHERE f.order_status = 'completed'
GROUP BY CUBE(dp.category_name, h.hotel_name, dp.price_range)
ORDER BY dp.category_name, h.hotel_name;


-- 7) Pivoting - sporedba na prihodi na sobi vo razlicni hoteli

SELECT
    h.hotel_name,
    SUM(CASE WHEN rt.type_name = 'Economy'        THEN f.total_cost ELSE 0 END) AS economy_revenue,
    SUM(CASE WHEN rt.type_name = 'Standard Single' THEN f.total_cost ELSE 0 END) AS standard_single_revenue,
    SUM(CASE WHEN rt.type_name = 'Standard Double' THEN f.total_cost ELSE 0 END) AS standard_double_revenue,
    SUM(CASE WHEN rt.type_name = 'Deluxe Suite'    THEN f.total_cost ELSE 0 END) AS deluxe_suite_revenue,
    SUM(CASE WHEN rt.type_name = 'VIP Penthouse'   THEN f.total_cost ELSE 0 END) AS vip_penthouse_revenue,
    SUM(f.total_cost) AS total_revenue
FROM FactReservation f
JOIN DimHotel h ON h.hotel_key = f.hotel_key
JOIN DimRoom  dr ON dr.room_key = f.room_key
JOIN DimRoomType rt ON rt.room_type_key = dr.room_type_key
WHERE f.status != 'cancelled'
GROUP BY h.hotel_name
ORDER BY h.hotel_name;

-- 8) Slicing - fiksiranje na vrednost na edna dimenzija - hotel i pregled na preostanatite dimenzii

SELECT
    d.month_name,
    s.species_name,
    COUNT(*) AS reservations,
    SUM(f.total_cost) AS revenue
FROM FactReservation f
JOIN DimDate    d ON d.date_key  = f.date_key
JOIN DimHotel   h ON h.hotel_key = f.hotel_key
JOIN DimPet     p ON p.pet_key   = f.pet_key
JOIN DimSpecies s ON p.species_key = s.species_key
WHERE h.hotel_name = 'Fur Haven'
  AND d.year = 2022
  AND f.status != 'cancelled'
GROUP BY d.month_name, d.month_num, s.species_name
ORDER BY d.month_num, s.species_name;


-- 9) Dicing - fiksiranje na vrednosti na povekje dimenzii (4) - hotel, vid na milenice, seozna i status i pregled na preostanatite dimenzii
SELECT
    d.month_name,
    rt.type_name AS room_type,
    COUNT(*) AS reservations,
    SUM(f.total_cost) AS revenue
FROM FactReservation f
JOIN DimDate     d  ON d.date_key   = f.date_key
JOIN DimHotel    h  ON h.hotel_key  = f.hotel_key
JOIN DimPet      p  ON p.pet_key    = f.pet_key
JOIN DimSpecies  s  ON p.species_key = s.species_key
JOIN DimRoom     dr ON dr.room_key  = f.room_key
JOIN DimRoomType rt ON rt.room_type_key = dr.room_type_key
WHERE h.hotel_name = 'Fur Haven'
  AND d.season = 'Summer'
  AND s.species_name = 'Dog'
  AND f.status != 'cancelled'
GROUP BY d.month_name, d.month_num, rt.type_name
ORDER BY d.month_num, rt.type_name;