Calculate average rate of a component resource

This query uses the ProviderName column. ProviderName is deprecated in v1.3 and will be removed in v1.4.

Personas: FinOps Practitioner, Engineering
Capabilities: Forecasting, Planning & Estimating, Unit Economics, Reporting & Analytics

Calculate Average Rate of a Component Resource

Not in v1.0 sample

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

Columns used

Column names from the source use case.

Read the source on focus.finops.org ↗