Calculate Commitment Discount Coverage Rate with Eligibility-Adjusted Denominator
Computes a commitment discount coverage rate using only eligible charges as the denominator. Without eligibility data, practitioners typically divide covered spend by total spend, which produces a coverage rate that includes ineligible charges (e.g., storage services, support fees) in the denominator and may not reflect the actionable coverage opportunity. Targets discount-bearing programs only. Note: Unused commitment rows (CommitmentDiscountStatus = ‘Unused’) have CommitmentDiscountId populated and will artificially inflate both the numerator and the denominator if left in the dataset; practitioners should additionally filter these out for a true utilization-adjusted rate.
- Source
- FOCUS MCP snapshot
- FOCUS editions
- v1.4
- Queries
- 1
- Mode
- Read-only
- Library snapshot (UTC)
- 2026-10-08
- Source
- FOCUS use-case SQL
- Status
- Not in v1.0 sample
- Results
- Not run
- Mode
- Read-only
- Library snapshot (UTC)
- 2026-10-08
CommitmentProgramEligibilityDetails (v1.4) is absent from the v1.0 sample, so the eligibility-adjusted denominator of uncovered-but-eligible usage cannot be computed.
Original FOCUS SQL
WITH CommitmentDiscountEligible AS (
SELECT
CU.ServiceProviderName,
CU.EffectiveCost,
CU.CommitmentDiscountId
FROM focus_data_table CU
WHERE CU.ChargePeriodStart >= ? AND CU.ChargePeriodEnd < ?
AND CU.ChargeCategory = 'Usage'
AND (
-- Include covered rows in the denominator
CU.CommitmentDiscountId IS NOT NULL
-- If uncovered, check if the JSON array contains an eligible program type
OR EXISTS (
SELECT 1
FROM UNNEST(JSON_EXTRACT_ARRAY(CU.CommitmentProgramEligibilityDetails, '$.CommitmentPrograms')) AS CP
WHERE JSON_VALUE(CP, '$.ProgramType') IN ('FlexibleSpendPlan', 'ResourceReservation')
)
)
)
SELECT
ServiceProviderName,
SUM(CASE WHEN CommitmentDiscountId IS NOT NULL THEN EffectiveCost ELSE 0 END) AS CoveredCost,
SUM(EffectiveCost) AS EligibleCost,
SUM(CASE WHEN CommitmentDiscountId IS NOT NULL THEN EffectiveCost ELSE 0 END)
/ NULLIF(SUM(EffectiveCost), 0) AS CommitmentCoverageRate
FROM CommitmentDiscountEligible
GROUP BY ServiceProviderNameColumns used
Column names from the source use case.