Athena query to pull EC2 instance type cost per account

Hello I have some corrections to the above query.

1. Include Spot costs
2. Has Pricing term
3. Has Amortized cost, in addition to the billed cost. This is especially important if the customer bought up front or partial upfront RIs or SPs
4. Savings plan negation should not be ommittted unless using amortized costs.

You still will have to replace the same name. You should also use a where clause to restruct the timeframe . For example to find Jan cost you would write: where line_item_useage_state_date >= date '2021-01-01' and line_item_useage_state_date <date '2021-01-01' and

SELECT
  line_item_usage_account_id
, (CASE WHEN (line_item_usage_type LIKE '%SpotUsage%') THEN 'Spot' 
   WHEN (((product_usagetype LIKE '%BoxUsage%') OR (product_usagetype LIKE '%DedicatedUsage:%')) AND ("line_item_line_item_type" LIKE 'SavingsPlanCoveredUsage')) or (line_item_line_item_type = 'SavingsPlanNegation') THEN 'SavingsPlan' 
   WHEN (("product_usagetype" LIKE '%BoxUsage%') AND ("line_item_line_item_type" LIKE 'DiscountedUsage')) THEN 'ReservedInstance' 
   WHEN ((("product_usagetype" LIKE '%BoxUsage%') OR ("product_usagetype" LIKE '%DedicatedUsage:%')) AND ("line_item_line_item_type" LIKE 'Usage')) THEN 'OnDemand' 
   ELSE 'Other' END) "Pricing Term"  
, case when line_item_usage_type like '%BoxUsage' or line_item_usage_type like '%DedicatedUsage' then product_instance_type else split_part (line_item_usage_type, ':', 2) end InstanceType
, round (sum (line_item_unblended_cost),2) BilledCost
, round (sum((CASE 
     WHEN line_item_usage_type LIKE '%SpotUsage%' THEN line_item_unblended_cost  
     WHEN ((product_usagetype LIKE '%BoxUsage%') OR (product_usagetype LIKE '%DedicatedUsage:%')) AND (line_item_line_item_type LIKE 'Usage') THEN line_item_unblended_cost 
     WHEN ((line_item_line_item_type LIKE 'SavingsPlanCoveredUsage')) THEN TRY_CAST(savings_plan_savings_plan_effective_cost AS double) 
     WHEN ((line_item_line_item_type LIKE 'DiscountedUsage')) THEN reservation_effective_cost
     WHEN (line_item_line_item_type = 'SavingsPlanNegation') then 0
     ELSE line_item_unblended_cost END)), 2) "AmortizedCost"   
FROM <your table>
where line_item_operation like '%RunInstance%' and line_item_product_code = 'AmazonEC2' 
and (product_instance_type <> '' or  (line_item_usage_type  like '%SpotUsage%' and line_item_line_item_type = 'Usage'))  
group by 1,2,3