Skip to content

Instantly share code, notes, and snippets.

@heyitsaamir
Last active April 29, 2020 14:08
Show Gist options
  • Save heyitsaamir/99571448ca0243dde5ccfab6e0dc36a3 to your computer and use it in GitHub Desktop.
Save heyitsaamir/99571448ca0243dde5ccfab6e0dc36a3 to your computer and use it in GitHub Desktop.
Live_Live to Drop_Drop Investigation - query
with qualified as (
select *, get_path(matching_criteria, 'jobSummary.shipmentDigests[0].classification') classification from src.dedication_event_logs del
where event_type = 'MATCHING'
and match_type = 'QUALIFIED'
and matching_contracts != '[]'
)
, drop_drops as (
select s.id, 'DROP_DROP' classification from bi.SHIPMENTS s
inner join src.shipment_stops ss_pick on ss_pick.id = s.pickup_stop_id
inner join src.shipment_stops ss_drop on ss_drop.id = s.last_dropoff_stop_id
where ss_pick.loading_type = 'LOADED_TRAILER' AND ss_drop.loading_type = 'LOADED_TRAILER'
and s.on_hold = false
) ,
offered as (
select * from src.dedication_event_logs del
where event_type = 'OFFERING'
and contract_id is not null
),
part_of_batches as (
select batch_id, shipment_id from src.batch_shipments
)
select date_part('woy', q.created_at) week_of_year
, sum(case when q.job_id is not null then 1 else 0 end) as total_changing_types
, sum(case when pob.shipment_id is not null then 1 else 0 end) as total_turning_batches
, sum(case when o.job_id is not null then 1 else 0 end) as singleton_offered
, sum(case when o_batches.job_id is not null then 1 else 0 end) as batch_offered
-- select q.*, dd.*
from qualified q
inner join drop_drops dd on dd.id = q.job_id
left join offered o on o.job_id = q.job_id
left join part_of_batches pob on pob.shipment_id = q.job_id
left join offered o_batches on o_batches.job_id = pob.batch_id
where dd.classification != q.classification
group by 1
order by 1
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment