Fixing never_eligible

core_dedication_all_job_contract_matching.sql

 
with historical_shipment_matching as 
    (
    SELECT -- Use a distinct to avoid duplicate entries coming from lane mapping table
     DISTINCT s.id AS shipment_id
            , s.created_at as matched_at
            , m.contract_id
            , coalesce(m.rate_override, cc.contract_rate) AS contracted_rate_dollars
            , s.pickup_driver_end_time
    FROM convoy.core.jobs s
    LEFT JOIN convoy.core.job_geos sg ON sg.shipment_id = s.id -- Find all shipments that match requirements in contract Lane Mapping Table
    JOIN convoy.core.dedication_carrier_contract_lane_mappings m ON m.pickup_city_key = sg.pickup_city_state
        AND m.dropoff_city_key = sg.dropoff_city_state
        AND m.pick_type = CASE WHEN s.pick_type = 'LIVE' THEN 'LIVE' ELSE 'DROP' END
        AND m.drop_type = CASE WHEN s.drop_type = 'LIVE' THEN 'LIVE'ELSE 'DROP'END
        AND m.truck_type = s.truck_type
        AND s.pickup_driver_end_time BETWEEN m.contract_start_date AND m.contract_end_date
    JOIN convoy.core.dedication_carrier_contracts cc ON cc.contract_id = m.contract_id -- start of dedication program
    WHERE s.pickup_driver_end_time > '2019-05-12'
)
, historical_job_matching as (
    select 
    nvl(batch_shipments.batch_id, historical_shipment_matching.shipment_id) as job_id
    , historical_shipment_matching.contract_id
    , historical_shipment_matching.matched_at
    , null as filter_reason
    , historical_shipment_matching.contracted_rate_dollars
    , historical_shipment_matching.pickup_driver_end_time
    from historical_shipment_matching 
    left join convoy.core.batch_shipments batch_shipments on batch_shipments.shipment_id = historical_shipment_matching.shipment_id and batch_shipments.completed_as_batch = 1
)
, job_contract_offering_explanation_partition as (
    select
        d.job_id
        , value:contractId::text as contract_id
        , value:reason::text as contract_offer_explanation
        , row_number() over (
            partition by d.job_id
            , value:contractId::text
            order by
                d.created_at desc
        ) as recency
    from
        convoy.src.dedication_event_logs d
        , lateral flatten(input => d.contracts_with_selection_reason)
    where
        d.event_type = 'OFFERING'
)
, job_contract_offering_explanation as (
    select
        e.job_id
        , e.contract_id
        , e.contract_offer_explanation     
    from
        job_contract_offering_explanation_partition e
    where
        e.recency = 1
)
, potentially_for_batch as (
    select distinct d.job_id
        , d.created_at
        , 'potentially_for_batch' as filter_reason
    from convoy.src.dedication_event_logs d
    where
        d.event_type = 'SELECTION'
        and d.exclude_reason = 'PotentiallyForBatch'
)
, filtered_jobs as (
    select
        distinct d.job_id
        , d.created_at
        , value:contractId::text as contract_id
        , value:reason::text as filter_reason
    from convoy.src.dedication_event_logs d,
        lateral flatten(input => d.contracts_filtered_out)
    where d.event_type = 'MATCHING'
)
, matched_eligible_jobs as (
    select
        distinct d.job_id
        , d.created_at
        , value::text as contract_id         
        , null as filter_reason     
    from convoy.src.dedication_event_logs d,
lateral flatten(input => d.matching_contracts)
    where
        d.event_type = 'MATCHING'         
        and d.match_type = 'ELIGIBLE'
)
, matched_ineligible_jobs as (
    select
        distinct d.job_id
        , d.created_at
        , value::text as contract_id         
        , case when p.job_id is not null then 'potentially_for_batch' else 'never_eligible' end as filter_reason     
    from convoy.src.dedication_event_logs d
    left join potentially_for_batch p on p.job_id = d.job_id,
lateral flatten(input => d.matching_contracts)
    where
        d.event_type = 'MATCHING'         
        and d.match_type = 'QUALIFIED'
        and d.job_id not in (select matched_eligible_jobs.job_id from matched_eligible_jobs)
        and d.job_id not in (select filtered_jobs.job_id from filtered_jobs)
)
, job_contract_rate_recency as (
    select 
        d.job_id
        , value:contractId::text as contract_id
        , value:payoutCalculation:combinedTotal::int/100 as contracted_rate_dollars
        , row_number() over (partition by d.job_id, value:contractId::text order by d.created_at desc) as recency_rank
    from convoy.src.dedication_event_logs d 
        , lateral flatten(input => d.contract_ids_with_payout)
    where d.event_type = 'OFFERING'
)
, rates as (
    select *
    from job_contract_rate_recency 
    where job_contract_rate_recency.recency_rank = 1
)
, jobs_model_matched_union as (
    select
        m.job_id
        , m.contract_id
        , max(m.created_at) as matched_at
        , max(m.filter_reason) as filter_reason
    from         (
            select 
                *
            from
                filtered_jobs
            union all
            select
                *
            from
                matched_eligible_jobs
            union all
            select
                *
            from
                matched_ineligible_jobs
        ) m
    group by
        m.job_id
        , m.contract_id
)
, jobs_model_matching as (
    select 
        jobs_model_matched_union.job_id
        , jobs_model_matched_union.contract_id
        , jobs_model_matched_union.matched_at
        , jobs_model_matched_union.filter_reason
        , rates.contracted_rate_dollars
    from jobs_model_matched_union
    left join rates on rates.job_id = jobs_model_matched_union.job_id and rates.contract_id = jobs_model_matched_union.contract_id
)
, historical_job_model_blend as (
    select 
        jobs_model_matching.job_id
        , jobs_model_matching.contract_id
        , jobs_model_matching.matched_at
        , jobs_model_matching.filter_reason
        , jobs_model_matching.contracted_rate_dollars
    from jobs_model_matching
    left join convoy.core.jobs core_jobs on core_jobs.id = jobs_model_matching.job_id
    where core_jobs.carrier_pickup_driver_start_time >= '2020-04-06'
    union all
    select 
        historical_job_matching.job_id
        , historical_job_matching.contract_id
        , historical_job_matching.matched_at
        , historical_job_matching.filter_reason
        , historical_job_matching.contracted_rate_dollars
    from historical_job_matching
    where historical_job_matching.pickup_driver_end_time < '2020-04-06'

)
, matched_union as (
    select 
        blend.job_id
        , blend.contract_id
        , max(blend.matched_at) as matched_at
        , max(blend.filter_reason) as filter_reason
        , max(blend.contracted_rate_dollars) as contracted_rate_dollars
    from historical_job_model_blend blend 
    group by blend.job_id
        , blend.contract_id
)
, job_contract_offers as (
    select         
    distinct d.job_id
    , d.contract_id
    from
        convoy.core.dedication_job_offers d
)
,
all_matches as (
select
    m.job_id
    , m.contract_id
    , m.matched_at
    , nvl(m.filter_reason, e.contract_offer_explanation) as contract_offer_explanation     
    , j.job_id is not null as offered_to_contract 
    , convert_timezone(core_carriers.address_timezone, core_jobs.carrier_pickup_driver_start_time) as pickup_driver_time_local
    , cast(pickup_driver_time_local as date) as pickup_driver_day
    , cast(date_trunc('week', pickup_driver_time_local) as date) as pickup_driver_week
    , cast(date_trunc('month', pickup_driver_time_local) as date) as pickup_driver_month
    , core_jobs.job_type
    , m.contracted_rate_dollars
    , core_jobs.truck_type
    , shipment_geos.pickup_city_state as first_shipment_pickup_city_state
    , shipment_geos.dropoff_city_state as first_shipment_dropoff_city_state
    , case when first_shipment.pick_type = 'LIVE' then 'LIVE' else 'DROP' end as first_shipment_pick_type
    , case when first_shipment.drop_type = 'LIVE' then 'LIVE' else 'DROP' end as first_shipment_drop_type
    from matched_union m     
    left join job_contract_offers j on j.job_id = m.job_id
        and j.contract_id = m.contract_id     
    left join job_contract_offering_explanation e on e.job_id = m.job_id
        and e.contract_id = m.contract_id
    left join convoy.core.jobs core_jobs on core_jobs.id = m.job_id
    left join convoy.core.dedication_carrier_contracts contracts on contracts.contract_id = m.contract_id
    left join convoy.core.carriers core_carriers on core_carriers.id = contracts.carrier_id
    left join convoy.core.batch_shipments batch_shipments on batch_shipments.batch_id = m.job_id and batch_shipments.order_index = 0
    left join convoy.bi.shipment_geos shipment_geos on shipment_geos.shipment_id = nvl(batch_shipments.shipment_id, m.job_id)
    left join convoy.core.jobs first_shipment on first_shipment.id = nvl(batch_shipments.shipment_id, m.job_id)
    ) 
select * 
from all_matches
where job_id = 'dd04062b-218e-4d39-b842-9cd00b15d1f2'