Calculate average rate of a component resource

FOCUS Versions

v1.3 v1.4

Context

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.

FOCUS SQL Query

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

FOCUS Datasets

Contract Commitment Cost and Usage

FOCUS Columns

Service Provider Name Service Name Pricing Unit Region Name SKU Price Details Charge Period Start Charge Period End Effective Cost

Previous Verify discount accuracy for a previously invoiced billing period (corrections excluded) Next Forecast amortized costs month over month based on historical trends