Aggregate usage and pricing across multiple SKUs to compute the average cost per core for each virtual machine series by region. This common denominator supports benchmarking when billed and consumed units differ.
Column names from the source use case.
The v1.0 sample lacks SkuPriceDetails, including its CoreCount and InstanceSeries properties, so average effective cost per core cannot be calculated.
SELECT
ServiceProviderName,
ServiceName,
PricingUnit,
RegionName,
JSON_UNQUOTE(JSON_EXTRACT(SkuPriceDetails, '$.InstanceSeries')) AS InstanceSeries,
SUM(CAST(JSON_UNQUOTE(JSON_EXTRACT(SkuPriceDetails, '$.CoreCount')) AS UNSIGNED)) AS TotalCoreCount,
CASE
WHEN SUM(CAST(JSON_UNQUOTE(JSON_EXTRACT(SkuPriceDetails, '$.CoreCount')) AS UNSIGNED)) > 0
THEN SUM(EffectiveCost) / SUM(CAST(JSON_UNQUOTE(JSON_EXTRACT(SkuPriceDetails, '$.CoreCount')) AS UNSIGNED))
ELSE NULL
END AS AverageEffectiveCoreCost
FROM focus_data_table
WHERE ChargePeriodStart >= ? and ChargePeriodEnd < ?
AND JSON_CONTAINS_PATH(SkuPriceDetails, 'all', '$.CoreCount', '$.InstanceSeries')
GROUP BY
ServiceProviderName,
ServiceName,
PricingUnit,
RegionName,
InstanceSeries