Combine cost and usage data with contract commitment metadata to analyze commitment terms, categories, and periods alongside actual consumption patterns.
Column names from the source use case.
ContractApplied and the Contract Commitment dataset fields ContractCommitmentId, ContractCommitmentCategory, ContractCommitmentPeriodStart, ContractCommitmentPeriodEnd, and ContractCommitmentCost are absent from the v1.0 sample.
SELECT CU.ServiceProviderName, JSON_VALUE(CA, '$.ContractCommitmentID') AS ContractCommitmentId, CC.ContractCommitmentCategory, CC.ContractCommitmentPeriodStart, CC.ContractCommitmentPeriodEnd, CC.ContractCommitmentCost, SUM(CU.EffectiveCost) AS TotalUsageCost, SUM(CAST(JSON_VALUE(CA, '$.ContractCommitmentAppliedCost') AS FLOAT64)) AS AppliedCost FROM focus_data_table CU CROSS JOIN UNNEST(JSON_EXTRACT_ARRAY(CU.ContractApplied, '$.Elements')) AS CA JOIN contract_commitment CC ON JSON_VALUE(CA, '$.ContractCommitmentID') = CC.ContractCommitmentId WHERE CU.ChargePeriodStart >= ? AND CU.ChargePeriodEnd < ? GROUP BY CU.ServiceProviderName, JSON_VALUE(CA, '$.ContractCommitmentID'), CC.ContractCommitmentCategory, CC.ContractCommitmentPeriodStart, CC.ContractCommitmentPeriodEnd, CC.ContractCommitmentCost