heyitsaamir · April 29, 2020 14:08
diff --git a/live_live_to_drop_drop_query.sql b/live_live_to_drop_drop_query.sql
 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
	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