-- Step 1 # of Ambassador members staying in Japanese resorts for the months of May to Aug 2024

SELECT
    COUNT(DISTINCT m.MEMBER_ID) AS ambassador_count
FROM MEMBER_PROFILE_DIMENSION_TABLE m
INNER JOIN RESERVATIONS_DETAIL_TABLE r
    ON m.CUSTOMER_PROFILE_ID = r.CUSTOMER_PROFILE_ID
INNER JOIN HOTEL_DETAIL_TABLE h
    ON r.PROPERTY_CODE = h.PROPERTY_CODE
WHERE m.MEMBER_TIER = 'Ambassador'
  AND h.PROPERTY_COUNTRY = 'Japan'
  AND LOWER(h.HOTEL_TYPE) = 'resort'
  AND r.CHECK_IN_DATE >= '2024-05-01'
  AND r.CHECK_IN_DATE <  '2024-09-01';


-- Step 2: Automate an extract for a data visualisation dashboard
-- a) Total reservations and nights stayed
-- b) Rolling twelve-month basis for the night of the guest stay
-- c) Exclude non-member stays
-- d) Include cuts for property country and hotel type
-- Assumption: reservations and nights are attributed to the check-in month.
--             Stays crossing a month boundary are not split by night.

WITH params AS (
    SELECT
        DATE_SUB(first_of_month, INTERVAL 24 MONTH) AS history_start,  -- 12 extra months so every output month has a full rolling window
        DATE_SUB(first_of_month, INTERVAL 12 MONTH) AS output_start,
        first_of_month                              AS window_end      -- exclusive: current (incomplete) month excluded
    FROM (
        SELECT CAST(DATE_FORMAT(CURRENT_DATE, '%Y-%m-01') AS DATE) AS first_of_month) d),

member_reservations AS (
    SELECT
        r.RESERVATION_ID,
        r.CUSTOMER_PROFILE_ID,
        r.LENGTH_OF_STAY,
        h.PROPERTY_COUNTRY,
        h.HOTEL_TYPE,
        CAST(DATE_FORMAT(r.CHECK_IN_DATE, '%Y-%m-01') AS DATE) AS stay_month
    FROM RESERVATIONS_DETAIL_TABLE r
    INNER JOIN HOTEL_DETAIL_TABLE h
        ON r.PROPERTY_CODE = h.PROPERTY_CODE
    CROSS JOIN params p
    WHERE r.CHECK_IN_DATE >= p.history_start
      AND r.CHECK_IN_DATE <  p.window_end
      -- Members only; EXISTS avoids duplicate rows if a guest has more than one member record
      AND EXISTS (
          SELECT 1
          FROM MEMBER_PROFILE_DIMENSION_TABLE m
          WHERE m.CUSTOMER_PROFILE_ID = r.CUSTOMER_PROFILE_ID
            AND m.MEMBER_ID IS NOT NULL)),

monthly_agg AS (
    SELECT
        stay_month,
        PROPERTY_COUNTRY,
        HOTEL_TYPE,
        COUNT(DISTINCT RESERVATION_ID) AS total_reservations,
        SUM(LENGTH_OF_STAY) AS total_nights
    FROM member_reservations
    GROUP BY
        stay_month,
        PROPERTY_COUNTRY,
        HOTEL_TYPE),

rolling AS (
    SELECT
        stay_month,
        PROPERTY_COUNTRY,
        HOTEL_TYPE,
        total_reservations,
        total_nights,
        -- RANGE on a month index covers 12 calendar months even when some months have no stays
        SUM(total_reservations) OVER (
            PARTITION BY PROPERTY_COUNTRY, HOTEL_TYPE
            ORDER BY YEAR(stay_month) * 12 + MONTH(stay_month)
            RANGE BETWEEN 11 PRECEDING AND CURRENT ROW
        ) AS rolling_12m_reservations,
        SUM(total_nights) OVER (
            PARTITION BY PROPERTY_COUNTRY, HOTEL_TYPE
            ORDER BY YEAR(stay_month) * 12 + MONTH(stay_month)
            RANGE BETWEEN 11 PRECEDING AND CURRENT ROW
        ) AS rolling_12m_nights
    FROM monthly_agg)

SELECT
    r.stay_month,
    r.PROPERTY_COUNTRY,
    r.HOTEL_TYPE,
    r.total_reservations,
    r.total_nights,
    r.rolling_12m_reservations,
    r.rolling_12m_nights
FROM rolling r
CROSS JOIN params p
WHERE r.stay_month >= p.output_start
ORDER BY
    r.stay_month DESC,
    r.PROPERTY_COUNTRY,
    r.HOTEL_TYPE;