TTM comparison

ttm_comparison.sql

with prices as (
SELECT entity_id, id, created_at
      FROM src.prices p
      WHERE p.org_type = 'CARRIER'
      AND p.source_type = 'MODEL'
      AND p.reason_code != 'DEDICATION_PRICE_REGEN'
      QUALIFY ROW_NUMBER() OVER (PARTITION BY p.entity_id ORDER BY p.created_at desc) = 1
      )
, total_prices as (
select p.entity_id, sum(pli.cents) total, LISTAGG(pli.type, ', ') AS cost_type
from prices p
join src.price_line_items pli on pli.price_id = p.id
-- where pli.type = 'TRANSPORTATION_SERVICES'
group by p.entity_id
)
SELECT D.job_id
	, value:contractId::text as contract_id
	, value:ttm::int/100 as aamir_ttm
	, p.deep_ttm
	, tp.total/100 as latest_from_src
	, tp.cost_type
	, value:payoutCalculation:combinedTotal::int/100 as contracted_rate_dollars
	, value:margin::int/100 AS margin
	, (deep_ttm - aamir_ttm)/deep_ttm AS deep_ttm_aamir_ttm_diff
FROM src.DEDICATION_EVENT_LOGS D 
 JOIN bi.STM_PREDICTIONS p ON p.CARRIER_JOB_ID = D.JOB_ID
 JOIN core.JOBS j ON j.id = D.JOB_ID
 JOIN total_prices tp on tp.entity_id = D.JOB_ID
, lateral flatten(input => d.contract_ids_with_payout)
WHERE D.EVENT_TYPE = 'OFFERING'
AND D.CREATED_AT > current_date  - 2
AND j.job_type = 'BATCH'