Created
May 21, 2020 16:41
-
-
Save heyitsaamir/e00c8a68caca2956d19ea447f34d7d23 to your computer and use it in GitHub Desktop.
TTM comparison
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 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' |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment