メンバーズ CUR でやってみよう！Savings Plans 使用率（Utilization）&カバー率（Coverage）編

SELECT
  SPLIT_PART(savings_plan_savings_plan_a_r_n, '/', 2) AS "Savings Plan ID",
  savings_plan_offering_type as Type,
  savings_plan_instance_type_family as "Instance family",
  savings_plan_region as Region,
  TRY_CAST(((SUM(TRY_CAST(savings_plan_used_commitment AS DECIMAL(16, 8))) / SUM(TRY_CAST(savings_plan_total_commitment_to_date AS DECIMAL(16, 8)))) * 100) AS DECIMAL(3, 0)) AS Utilization,
  SUM(TRY_CAST(savings_plan_total_commitment_to_date AS DECIMAL(16, 4))) as "Total commitment",
  SUM(TRY_CAST(savings_plan_used_commitment AS DECIMAL(16, 4))) as "Used commitment",
  SUM(TRY_CAST(line_item_unblended_cost - savings_plan_savings_plan_effective_cost AS DECIMAL(16, 4))) AS "Net savings"
FROM 
  ${table_name} 
WHERE 
  ${date_filter}  
  AND savings_plan_savings_plan_a_r_n <> ''
  AND line_item_line_item_type  IN ('Usage', 'SavingsPlanCoveredUsage', 'SavingsPlanRecurringFee', 'SavingsPlanUpfrontFee')
GROUP BY
  1,2,3,4
ORDER BY
  1;

with sp as 
(Select line_item_product_code as Service, product_instance_type_family as "Instance family", product_region as Region,
  SUM(CASE
    WHEN line_item_line_item_type = 'SavingsPlanCoveredUsage' THEN TRY_CAST(line_item_unblended_cost AS DECIMAL(16, 8))
    ELSE 0 
  END) AS "Spend covered by Savings Plan",
  SUM(CASE
    WHEN line_item_line_item_type = 'Usage' THEN TRY_CAST(line_item_unblended_cost AS DECIMAL(16, 8))
    ELSE 0 
  END) AS "On-Demand spend"
FROM 
  ${table_name} 
WHERE 
  ${date_filter}  
  AND line_item_line_item_type in ('SavingsPlanCoveredUsage', 'Usage') and line_item_product_code LIKE '%AmazonEC2%' and line_item_usage_type LIKE '%BoxUsage%'
 group by
  1,2,3)

 select *,
   TRY_CAST(("Spend covered by Savings Plan" / ("Spend covered by Savings Plan" + "On-Demand spend"))*100 AS DECIMAL(3, 0)) as Coverage
 from sp;

SELECT
  SPLIT_PART(savings_plan_savings_plan_a_r_n, '/', 2) AS "Savings Plan ID",
  savings_plan_offering_type as Type,
  savings_plan_instance_type_family as "Instance family",
  savings_plan_region as Region,
  savings_plan_total_commitment_to_date as "Commitment ($ per hour)",
  DATE_FORMAT(FROM_ISO8601_TIMESTAMP(savings_plan_start_time),'%Y-%m-%d %H:%i:%s') AS Start_date,
  DATE_FORMAT(FROM_ISO8601_TIMESTAMP(savings_plan_end_time),'%Y-%m-%d %H:%i:%s') AS End_date
FROM 
  ${table_name} 
WHERE 
  ${date_filter}  
  AND savings_plan_savings_plan_a_r_n <> ''
  AND line_item_line_item_type = 'SavingsPlanRecurringFee'
GROUP BY
  1,2,3,4,5,6,7
ORDER BY
  1;