Aggregate initial contract commitment purchases to understand total contracted spend and usage commitments by service provider and commitment ID over a specified time range.
Column names from the source use case.
ContractApplied and its ContractCommitmentID / ContractCommitmentAppliedCost elements are absent from the v1.0 sample, so contract-commitment purchases cannot be reported.
SELECT MIN(CU.ChargePeriodStart) AS ChargePeriodStart, MAX(CU.ChargePeriodEnd) AS ChargePeriodEnd, CU.ServiceProviderName, JSON_VALUE(CA, '$.ContractCommitmentID') AS ContractCommitmentId, SUM(CAST(JSON_VALUE(CA, '$.ContractCommitmentAppliedCost') AS FLOAT64)) AS ContractCommitmentAppliedCost FROM focus_data_table CU CROSS JOIN UNNEST(JSON_EXTRACT_ARRAY(CU.ContractApplied, '$.Elements')) AS CA WHERE JSON_VALUE(CA, '$.ContractCommitmentAppliedCost') IS NOT NULL AND ChargePeriodStart >= ? AND ChargePeriodEnd < ? AND ChargeCategory = 'Purchase' GROUP BY CU.ServiceProviderName, JSON_VALUE(CA, '$.ContractCommitmentID')