Analyzing the amount of costs for a provider that have been tagged with a specific tag can enable the FinOps practitioner to check the amount of costs being allocated via tags.
Column names from the source use case.
1 row. Scroll inside the table to see more.
select sum(case when tags ? $1::text then effective_cost else 0 end) / nullif(sum(effective_cost), 0) as tagged_share,
sum(case when tags ? $1::text then effective_cost else 0 end) / nullif(sum(effective_cost), 0) * 100 as tagged_percentage,
sum(case when tags ? $1::text then effective_cost else 0 end) as tagged_effective_cost,
sum(effective_cost) as total_effective_cost
from focus.cost_and_usage
where charge_period_start >= $2::timestamptz
and charge_period_end < $3::timestamptz
and effective_cost > 0
and case when publisher_name is not null and publisher_name is distinct from invoice_issuer_name then publisher_name else provider_name end = $4::textSELECT
SUM(CASE
WHEN JSON_CONTAINS_PATH(tags, 'one', '$.?')
THEN EffectiveCost
ELSE 0
END) / SUM(EffectiveCost) * 100 AS TaggedPercentage
FROM focus_data_table
WHERE ChargePeriodStart >= ? and ChargePeriodEnd < ?
AND EffectiveCost > 0
AND ServiceProviderName = ?1 row. Scroll inside the table to see more.
select sum(case when tags -> $1::text is not null then effective_cost else 0 end) / nullif(sum(effective_cost), 0) as tagged_ratio,
sum(case when tags -> $1::text is not null then effective_cost else 0 end) as tagged_effective_cost,
sum(effective_cost) as total_effective_cost
from focus.cost_and_usage
where charge_period_start >= $2::timestamptz
and charge_period_end < $3::timestamptz
and effective_cost > 0
and provider_name = $4::textSELECT
SUM(CASE
WHEN JSON_CONTAINS_PATH(tags, 'one', '$.?')
THEN EffectiveCost
ELSE 0
END) / SUM(EffectiveCost) * 100 AS TaggedPercentage
FROM focus_data_table
WHERE ChargePeriodStart >= ? and ChargePeriodEnd < ?
AND EffectiveCost > 0
AND ProviderName = ?