AWS Billing: On-Demand VS Reserved VS Spot

-- Unused Reservations
WITH reservations AS
  (SELECT split_part(substr(line_item_usage_type, 12), '.',1) AS s3,
          substr(line_item_usage_type,12) AS s4,
          ROUND(SUM(line_item_blended_cost), 2) AS value
   FROM "athenacurcfn_cost_reports"."cost_reports"
   WHERE bill_billing_period_start_date BETWEEN TIMESTAMP '{{ date.start }}' AND TIMESTAMP '{{ date.end }}'
     AND line_item_usage_account_id = '{{account}}'
     AND line_item_line_item_type = 'RIFee'
     AND product_product_name = 'Amazon Elastic Compute Cloud'
   GROUP BY 1,
            2
   ORDER BY 1 DESC),
     reservation_usage AS
  (SELECT product_instance_type_family AS s3,
          ROUND(SUM(reservation_effective_cost), 2) AS value
   FROM "athenacurcfn_cost_reports"."cost_reports"
   WHERE bill_billing_period_start_date BETWEEN TIMESTAMP '{{ date.start }}' AND TIMESTAMP '{{ date.end }}'
     AND line_item_usage_account_id = '{{account}}'
     AND line_item_line_item_type = 'DiscountedUsage'
     AND product_product_name = 'Amazon Elastic Compute Cloud'
   GROUP BY 1)
SELECT 'Unused Reservations' AS s1,
       'Untagged' AS s2,
       reservations.s3,
       reservations.s4,
       ROUND(SUM(reservations.value - reservation_usage.value), 2) AS value
FROM reservations
INNER JOIN reservation_usage ON (reservation_usage.s3 = reservations.s3)
GROUP BY 1,
         2,
         3,
         4
         
UNION ALL

-- Stack Costs by Reserved or OnDemand


SELECT CASE line_item_line_item_type
           WHEN 'Usage' THEN 'On Demand'
           WHEN 'DiscountedUsage' THEN 'Reserved'
       END AS s1,
       coalesce(nullif(resource_tags_user_stack, ''),'Untagged') AS s2,
       product_instance_type_family AS s3,
       product_instance_type AS s4,
       ROUND(SUM(line_item_blended_cost + reservation_effective_cost),2) AS value
FROM "athenacurcfn_cost_reports"."cost_reports"
WHERE bill_billing_period_start_date BETWEEN TIMESTAMP '{{ date.start }}' AND TIMESTAMP '{{ date.end }}'
  AND line_item_usage_account_id = '{{account}}'
  AND product_instance_type_family != ''
  AND line_item_usage_type LIKE 'BoxUsage%'
GROUP BY 1,
         2,
         3,
         4
UNION ALL


-- Spot Costs

SELECT 'Spot' AS s1,
       coalesce(nullif(resource_tags_user_stack, ''),'Untagged') AS s2,
       split_part(substr(line_item_usage_type,11), '.',1) AS s3,
       substr(line_item_usage_type,11) AS s4,
       SUM(line_item_blended_cost)
FROM "athenacurcfn_cost_reports"."cost_reports"
WHERE bill_billing_period_start_date BETWEEN TIMESTAMP '{{ date.start }}' AND TIMESTAMP '{{ date.end }}'
  AND line_item_usage_account_id = '{{account}}'
  AND line_item_usage_type LIKE 'SpotUsage%'
  AND line_item_line_item_type = 'Usage'
GROUP BY 1,
         2,
         3,
         4