-- modified: 2022-12-13
-- query_id: elasticcache
-- query_description: This query will output the total monthly sum per resource for all Amazon ElastiCache purchase options (including reserved instances) across all ElastiCache instances types.
-- query_columns: bill_payer_account_id,line_item_line_item_type,line_item_resource_id,line_item_usage_account_id,line_item_usage_start_date,line_item_usage_type
-- query_link: /cost/300_labs/300_cur_queries/queries/database/

SELECT -- automation_select_stmt
  bill_payer_account_id,
  line_item_usage_account_id, 
  DATE_FORMAT(line_item_usage_start_date,'%Y-%m') AS month_line_item_usage_start_date, 
  SPLIT_PART(line_item_resource_id,':',7) AS split_line_item_resource_id,
  SPLIT_PART(line_item_usage_type ,':',2) AS split_line_item_usage_type,
  CASE 
    WHEN line_item_line_item_type = 'DiscountedUsage' THEN 'Reserved Instance'
    WHEN line_item_line_item_type = 'Usage' THEN 'OnDemand' 
    ELSE 'Others' 
  END AS case_purchase_option,
  SUM(CASE 
    WHEN line_item_line_item_type = 'DiscountedUsage' THEN line_item_usage_amount 
    WHEN line_item_line_item_type = 'Usage' THEN line_item_usage_amount 
    ELSE 0 
  END) AS sum_line_item_usage_amount,
  SUM(line_item_unblended_cost) AS sum_line_item_unblended_cost,
  SUM(CASE
    WHEN line_item_line_item_type = 'DiscountedUsage' THEN reservation_effective_cost
    WHEN line_item_line_item_type = 'RIFee' THEN reservation_unused_amortized_upfront_fee_for_billing_period + reservation_unused_recurring_fee
    WHEN line_item_line_item_type = 'Fee' AND reservation_reservation_a_r_n <> '' THEN 0 
    ELSE line_item_unblended_cost 
  END) AS sum_amortized_cost,
  SUM(CASE
    WHEN line_item_line_item_type = 'RIFee' THEN -reservation_amortized_upfront_fee_for_billing_period
    ELSE 0 
  END) AS sum_ri_trueup,
  SUM(CASE
    WHEN line_item_line_item_type = 'Fee' AND reservation_reservation_a_r_n <> '' THEN line_item_unblended_cost 
    ELSE 0 
  END) AS ri_upfront_fees
FROM -- automation_from_stmt
  ${table_name} -- automation_tablename
WHERE -- automation_where_stmt
  ${date_filter} -- automation_timerange_year_month
  AND product_product_name = 'Amazon ElastiCache'
  AND product_product_family = 'Cache Instance'
  AND line_item_line_item_type  IN ('DiscountedUsage', 'Usage', 'RIFee', 'Fee')
GROUP BY -- automation_groupby_stmt
    DATE_FORMAT(line_item_usage_start_date,'%Y-%m'),
    bill_payer_account_id, 
    line_item_usage_account_id, 
    line_item_line_item_type, 
    line_item_resource_id, 
    line_item_usage_type
ORDER BY -- automation_order_stmt
    month_line_item_usage_start_date,
    sum_line_item_usage_amount desc, 
    sum_line_item_unblended_cost
; 
