Last active
April 29, 2020 14:08
-
-
Save heyitsaamir/99571448ca0243dde5ccfab6e0dc36a3 to your computer and use it in GitHub Desktop.
Live_Live to Drop_Drop Investigation - query
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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