dbt_cloud/models/marts/warehouse/fct_exchange__bookings.sql

-- IMPORTS
{% set booking_status = dbt_utils.get_column_values(
    table=ref('stg_exchange__booking_logs'),
    column='status') %}

WITH bookings AS (
    SELECT * FROM {{ ref('int_exchange__bookings_get_estimate_booking_values') }}
),

booking_logs AS (
    SELECT * FROM {{ ref('stg_exchange__booking_logs') }}
),

-- LOGICS
booking_status_datetime AS (

    SELECT
        booking_id,
        MIN(created_datetime) AS booking_created_datetime,
        {%- for status in booking_status %}
            MAX(CASE WHEN status = '{{status}}' THEN updated_datetime END) AS booking_{{status.lower()}}_datetime {%- if not loop.last %},{% endif -%}
        {% endfor %}    

    FROM booking_logs

    GROUP BY
        booking_id
),

bookings_lists AS (
    SELECT 
        bookings.*,

        {% for column_name in ["company", "user"] %}
        CASE 
            WHEN ROW_NUMBER() OVER (PARTITION BY bookings.{{column_name}}_id ORDER BY bookings.created_datetime) = 1 THEN 1 
            ELSE 0 
        END AS is_first_time_{{column_name}},

        CASE 
            WHEN bookings.status = 'COMPLETED' 
                AND 
                MIN(IFF(bookings.status = 'COMPLETED', bookings.created_datetime, NULL))
                    OVER (PARTITION BY bookings.{{column_name}}_id) 
                    = bookings.created_datetime
            THEN 1
            ELSE 0 
        END AS is_first_time_{{column_name}}_completed,
        {% endfor %}

        booking_status_datetime.booking_created_datetime,
        {%- for status in booking_status %}
            booking_status_datetime.booking_{{status.lower()}}_datetime {%- if not loop.last %},{% endif -%}
        {% endfor %}  
    
    FROM
        bookings

    LEFT JOIN booking_status_datetime
        ON (bookings.booking_id = booking_status_datetime.booking_id)
),

-- FINAL
final_fct_exchange__bookings AS (
    SELECT
        -- ids
        booking_id,
        booking_marking_id,
        company_id,
        user_id,
        bank_id,
        fix_currency_id,
        quote_currency_id,
        base_currency_id,

        -- dimensions 
        service_type,
        estimate_payment_method,
        status,
        is_first_time_company,
        is_first_time_company_completed,
        is_first_time_user,
        is_first_time_user_completed,

        -- measures
        estimate_base_to_quote_currency_exchange_rate,
        estimate_quote_value,
        estimate_base_value,
        estimate_value_rm,

        -- date/times
        booking_created_datetime,
        booking_approved_datetime,
        booking_completed_datetime,
        booking_suspended_datetime,
        deleted_datetime,
        created_datetime,
        updated_datetime,

        -- metadata
        '{{ modules.datetime.datetime.now(modules.pytz.timezone("Asia/Kuala_Lumpur")) }}' AS _dbt_ran_datetime

    FROM bookings_lists
)

SELECT * FROM final_fct_exchange__bookings