CTE usage showcase

int_requests_prebook_to_asap.sql

{{
    config(
        materialized='ephemeral'
    )
}}

WITH accepted AS (

    SELECT DISTINCT
        
          request_id
        , LAST_VALUE(car_id IGNORE NULLS) OVER
            (PARTITION BY request_id ORDER BY event_ts_utc ASC
                ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as car_id      

    FROM {{ ref('flt_orders_logs') }}

    WHERE event_status in ('accepted')

    GROUP BY

        request_id
        , car_id
        , event_ts_utc

)

SELECT

      reserved.request_id
    , DECODE(reserved.car_id, accepted.car_id, false, true) as is_prebook_to_asap

FROM {{ ref('int_requests_first_reservations') }} as reserved
    LEFT JOIN accepted USING(request_id)