heyitsaamir · April 30, 2020 18:16
diff --git a/core_dedication_all_job_contract_matching.sql b/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'


	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'