Calculate Average Rate of a Component Resource

Source: FOCUS use-case SQL
Status: Not in v1.0 sample
Results: Not run
Mode: Read-only
Library snapshot (UTC): 2026-10-08

SkuPriceDetails (with its CoreCount and InstanceSeries properties) is absent from the v1.0 sample, so effective cost per core cannot be computed.

Review the original SQL below and the required columns before using a newer FOCUS dataset.

Original FOCUS SQL

SELECT
    ProviderName,
    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
    ProviderName,
    ServiceName,
    PricingUnit,
    RegionName,
    InstanceSeries