--GET THE FIRST VEHICLE RESERVATION PER USER/APP/VEHICLE
create or replace table temp_eu.delete_first_ride_reserve as 

with first_reservations as (
    select
        vrh.created_at,
        vrh.user_id,
        vrh.company_vehicle_id as vehicle_company_id,
        vrh.company_id as app_co_id,
        co1.name as vehicle_company_name,
        co2.name as app_company_name,
        row_number() over (partition by vrh.user_id,vrh.company_vehicle_id,vrh.company_id order by vrh.created_at asc) as r_n
    from `reby-cloud.analytics_reby_v1_eu.pg_vehicle_reservation_history` vrh
    left join `reby-cloud.analytics_reby_v1_eu.pg_company` co1
    on vrh.company_vehicle_id = co1.id
    left join `reby-cloud.analytics_reby_v1_eu.pg_company` co2
    on vrh.company_vehicle_id = co2.id
    QUALIFY r_n = 1
),
--FINISH

--GET THE FIRST RIDE PER USER/APP/VEHICLE
rides as (
    select 
        id,
        created_at,
        user_id,
        if(vehicle_company_id is null,'c_3qteetfhxjshx4j54111',vehicle_company_id) as vehicle_company_id,
        if(company_id is null,'c_3qteetfhxjshx4j54111',company_id) as company_id
    from analytics_reby_v1_eu.pg_rides_json
),

first_ride_pre as (
    select
        r.id,
        r.user_id,
        r.created_at as ride_date,
        r.vehicle_company_id,
        r.company_id as app_co_id,
        co.name as vehicle_company_name,
        co2.name as app_company_name,
        cu.id as cu_id_vehicle,
        cu2.id as cu_id_app,
        --row_number() over (partition by r.user_id,r.vehicle_company_id order by r.created_at asc) as rank_
    from rides r
    left join `reby-cloud.analytics_reby_v1_eu.pg_company` co on r.vehicle_company_id = co.id
    left join `reby-cloud.analytics_reby_v1_eu.pg_company` co2 on r.company_id = co2.id
    left join `reby-cloud.reby_marketing_eu.pg_company_user` cu on r.user_id = cu.user_id and r.vehicle_company_id = cu.company_id
    left join `reby-cloud.reby_marketing_eu.pg_company_user` cu2 on r.user_id = cu2.user_id and r.company_id = cu2.company_id
),

first_ride_per_app_vehicle as (
select *, row_number() over (partition by user_id, app_company_name, vehicle_company_name order by ride_date asc) as rank_ from first_ride_pre qualify rank_ = 1
),
--FINISH

--GET FIRST DATE BETWEEN FIRST RIDE AND/OR FIRST RESERVATION
first_date_ride_reserv as (
    select
        user_id,
        vehicle_company_name,
        app_company_name,
        created_at,
        vehicle_company_id,
        app_co_id,
        row_number() over (partition by user_id, app_company_name, vehicle_company_name order by created_at asc) as rank_
        from(
            --UNION BETWEEN RIDES AND RESERVATIONS
            select
                user_id,
                vehicle_company_name,
                app_company_name,
                created_at,
                vehicle_company_id,
                app_co_id
            from first_reservations 
            UNION ALL
            select
                user_id,
                vehicle_company_name,
                app_company_name,
                ride_date as created_at,
                vehicle_company_id,
                app_co_id
            from first_ride_per_app_vehicle
        ) QUALIFY rank_=1
)

select * from first_date_ride_reserv
--FINISH
;