Cost Optimization

SELECT
  bill_payer_account_id,
  line_item_usage_account_id,
  SPLIT_PART(line_item_resource_id, ':', 6) AS split_line_item_resource_id,
  product_region,
  pricing_unit,
  sum_line_item_usage_amount,
  CAST(cost_per_resource AS DECIMAL(16, 8)) AS sum_line_item_unblended_cost
FROM
	(
	SELECT
	  line_item_resource_id,
	  product_region,
	  pricing_unit,
	  line_item_usage_account_id,
	  bill_payer_account_id,
	  SUM(line_item_usage_amount) AS sum_line_item_usage_amount,
	  SUM(SUM(line_item_unblended_cost)) OVER (PARTITION BY line_item_resource_id) AS cost_per_resource,
	  SUM(SUM(line_item_usage_amount)) OVER (PARTITION BY line_item_resource_id, pricing_unit) AS usage_per_resource_and_pricing_unit,
	  COUNT(pricing_unit) OVER (PARTITION BY line_item_resource_id) AS pricing_unit_per_resource
	FROM
	  ${table_name}
	WHERE
	  line_item_product_code = 'AWSELB'
	  -- get previous month
	  AND month = CAST(month(current_timestamp + -1 * INTERVAL '1' MONTH) AS VARCHAR)
	  -- get year for previous month
	  AND year = CAST(year(current_timestamp + -1 * INTERVAL '1' MONTH) AS VARCHAR)
	  AND line_item_line_item_type = 'Usage'
	GROUP BY
	  line_item_resource_id,
	  product_region,
	  pricing_unit,
	  line_item_usage_account_id,
	  bill_payer_account_id
	)
WHERE
  -- filter only resources which ran more than half month (336 hrs)
  usage_per_resource_and_pricing_unit > 336
  AND pricing_unit_per_resource = 1
ORDER BY
  cost_per_resource DESC;

SELECT
  line_item_usage_account_id, 
  CASE 
    WHEN reservation_reservation_a_r_n <> '' THEN split_part(reservation_reservation_a_r_n,':',5)
    WHEN savings_plan_savings_plan_a_r_n <> '' THEN split_part(savings_plan_savings_plan_a_r_n,':',5)
    ELSE 'NA'
  END AS ri_sp_owner_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_model, 
	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 instance_type, 
	ROUND(SUM (line_item_unblended_cost),2) sum_line_item_unblended_cost, 
	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) amortized_cost  
FROM 
	${table_name}    
WHERE
	${date_filter}
	AND 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, -- account id
3, -- pricing model
4, -- instance type
2  -- ri_sp_owner_id
ORDER BY
pricing_model,
sum_line_item_unblended_cost DESC
;

with rds_aurora_io_optimized_data as (
-- io_usage
SELECT
    (compute_usage.compute_amortized_cost + storage_usage.storage_usage_unblended_cost + SUM(line_item_unblended_cost)) - (1.3 * compute_usage.compute_amortized_cost + 2.25 * storage_usage.storage_usage_unblended_cost) as savings,
    io_usage.line_item_usage_account_id as accounts,
    SUM(line_item_unblended_cost) as io_usage_unblended_cost,
    compute_usage.compute_amortized_cost as compute_usage_amortized_cost,
    storage_usage.storage_usage_unblended_cost as storage_usage_unblended_cost,
    io_usage.month,
    io_usage.year
FROM 
    ${table_name}
    io_usage,

    -- storage_usage
    (SELECT 
        line_item_usage_account_id,
        SUM(line_item_unblended_cost) as storage_usage_unblended_cost, 
        month, 
        year
    FROM 
         ${table_name}
    WHERE 
        line_item_usage_type LIKE '%Aurora:StorageUsage'
        AND line_item_usage_amount != 0.0
        ${date_filter} -- use partitions to optimize query
    GROUP BY 
        line_item_usage_account_id,
        month, 
        year
    ) storage_usage,

    -- compute_usage
    (SELECT 
        line_item_usage_account_id,
        SUM(line_item_usage_amount) AS compute_usage_usage_amount, 
        SUM(line_item_blended_cost) as compute_usage_blended_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)) "compute_amortized_cost", 
        month, 
        year
    FROM 
        ${table_name}
    WHERE 
        (line_item_resource_id LIKE '%cluster:cluster-%' OR line_item_resource_id LIKE '%db:%')
        AND product_database_engine IN ('Aurora MySQL','Aurora PostgreSQL')
        AND line_item_usage_amount != 0.0
        ${date_filter}  -- use partitions to optimize query
    GROUP BY 
        line_item_usage_account_id,
        month, 
        year
    ) compute_usage

-- io_usage continued
WHERE 
    line_item_usage_type LIKE '%Aurora:StorageIOUsage'
    AND storage_usage.line_item_usage_account_id = compute_usage.line_item_usage_account_id
    AND storage_usage.line_item_usage_account_id = io_usage.line_item_usage_account_id
    AND storage_usage.month = compute_usage.month
    AND storage_usage.year = compute_usage.year
    AND storage_usage.month = io_usage.month
    AND storage_usage.year = io_usage.year
    AND line_item_line_item_type IN ('DiscountedUsage', 'Usage')
    ${date_filter} -- use partitions to optimize query
GROUP BY 
    io_usage.line_item_usage_account_id,
    storage_usage.storage_usage_unblended_cost,
    compute_usage.compute_amortized_cost,
    storage_usage.storage_usage_unblended_cost,
    io_usage.month,
    io_usage.year
ORDER BY 
    savings DESC
)

SELECT 
    SUM(savings) as potential_savings, 
    accounts,
    io_usage_unblended_cost,
    compute_usage_amortized_cost,
    storage_usage_unblended_cost,
    month,
    year
FROM 
    rds_aurora_io_optimized_data
WHERE
    savings > 0  -- display only positive savings
    -- add additional account filtering here
GROUP BY 
    accounts,
    io_usage_unblended_cost,
    compute_usage_amortized_cost,
    storage_usage_unblended_cost,
    month,
    year
ORDER BY
    potential_savings DESC
;

SELECT
  bill_payer_account_id,
  line_item_usage_account_id,
  SPLIT_PART(line_item_resource_id, '/', 2) AS split_line_item_resource_id,
  product_region,
  product_operating_system,
  product_bundle_description,
  product_software_included,
  product_license,
  product_rootvolume,
  product_uservolume,
  pricing_unit,
  sum_line_item_usage_amount,
  CAST(total_cost_per_resource AS DECIMAL(16, 8)) AS "sum_line_item_unblended_cost(incl monthly fee)"
FROM
  (
  SELECT
    bill_payer_account_id,
    line_item_usage_account_id,
	line_item_resource_id,
	product_operating_system,
	pricing_unit,
	product_region,
	product_bundle_description,
	product_rootvolume,
	product_uservolume,
	product_software_included,
	product_license,
	SUM(line_item_usage_amount) AS sum_line_item_usage_amount,
	SUM(SUM(line_item_unblended_cost)) OVER (PARTITION BY line_item_resource_id) AS total_cost_per_resource,
	SUM(SUM(line_item_usage_amount)) OVER (PARTITION BY line_item_resource_id, pricing_unit) AS usage_amount_per_resource_and_pricing_unit
	FROM
	  ${table_name}
	WHERE
	  line_item_product_code = 'AmazonWorkSpaces' 
	  -- get previous month
	  AND CAST(month AS INT) = CAST(month(current_timestamp + -1 * INTERVAL '1' MONTH) AS INT) 
	  -- get year for previous month
	  AND CAST(year AS INT) = CAST(year(current_timestamp + -1 * INTERVAL '1' MONTH) AS INT)
	  AND line_item_line_item_type = 'Usage'
	  AND line_item_usage_type LIKE '%AutoStop%'
	GROUP BY
	  line_item_usage_account_id,
	  line_item_resource_id,
	  product_operating_system,
	  pricing_unit,
	  product_region,
	  product_bundle_description,
	  product_rootvolume,
	  product_uservolume,
	  bill_payer_account_id,
	  product_software_included,
	  product_license
  )     
WHERE
  -- return only workspaces which ran more than 80 hrs
  usage_amount_per_resource_and_pricing_unit > 80
ORDER BY
  total_cost_per_resource DESC,
  line_item_resource_id,
  line_item_usage_account_id,
  product_operating_system,
  pricing_unit;

SELECT
  bill_payer_account_id,
  line_item_usage_account_id,
  SPLIT_PART(line_item_resource_id, ':', 6) AS split_line_item_resource_id,
  product_region,
  pricing_unit,
  sum_line_item_usage_amount,
  CAST(cost_per_resource AS DECIMAL(16, 8)) AS sum_line_item_unblended_cost
FROM
  (
    SELECT
      line_item_resource_id,
      product_region,
      pricing_unit,
      line_item_usage_account_id,
      bill_payer_account_id,
      SUM(line_item_usage_amount) AS sum_line_item_usage_amount,
      SUM(SUM(line_item_unblended_cost)) OVER (PARTITION BY line_item_resource_id) AS cost_per_resource,
      SUM(SUM(line_item_usage_amount)) OVER (PARTITION BY line_item_resource_id, pricing_unit) AS usage_per_resource_and_pricing_unit,
      COUNT(pricing_unit) OVER (PARTITION BY line_item_resource_id) AS pricing_unit_per_resource
    FROM
      ${table_name}
    WHERE
      line_item_product_code = 'AmazonEC2'
      AND line_item_usage_type LIKE '%Nat%'
      -- get previous month
      AND month = CAST(month(current_timestamp + -1 * INTERVAL '1' MONTH) AS VARCHAR)
      -- get year for previous month
      AND year = CAST(year(current_timestamp + -1 * INTERVAL '1' MONTH) AS VARCHAR)
      AND line_item_line_item_type = 'Usage'
    GROUP BY
      line_item_resource_id,
      product_region,
      pricing_unit,
      line_item_usage_account_id,
      bill_payer_account_id
    )
WHERE
  -- filter only resources which ran more than half month (336 hrs)
  usage_per_resource_and_pricing_unit > 336
  AND pricing_unit_per_resource = 1
ORDER BY
  cost_per_resource DESC;

SELECT 
  COUNT(DISTINCT(line_item_resource_id))
FROM 
  ${table_name}
WHERE 
  line_item_product_code = 'AmazonEC2' 
  AND line_item_line_item_type = 'Usage' 
  AND bill_payer_account_id <> ''
  AND line_item_usage_account_id <> ''
  AND (CAST("concat"("year", '-', "month", '-01') AS date) = ("date_trunc"('month', current_date) - INTERVAL  '1' MONTH))
  AND line_item_usage_type LIKE '%gp%'
  AND line_item_usage_type LIKE '%EBS%'  

-- NOTE: If running this at a payer account level with millions of volumes we recommend filtering to specific accounts in line 20. You can also remove line 21 to view all EC2 EBS volumes.   
-- Step 1: Filter CUR to return all gp EC2 EBS storage usage  
WITH ebs_all AS (
  SELECT
    bill_billing_period_start_date,
	line_item_usage_start_date,
	bill_payer_account_id,
	line_item_usage_account_id,
	line_item_resource_id ,
	product_volume_api_name,
	line_item_usage_type,
	pricing_unit,
	line_item_unblended_cost,
	line_item_usage_amount
  FROM
	${table_name}
  WHERE 
    (line_item_product_code = 'AmazonEC2') 
	AND (line_item_line_item_type = 'Usage') 
	AND (CAST("concat"("year", '-', "month", '-01') AS date) = ("date_trunc"('month', current_date) - INTERVAL  '1' MONTH))
	AND bill_payer_account_id <> ''
	AND line_item_usage_account_id <> ''	  
	AND line_item_usage_type LIKE '%gp%'		 
	AND product_volume_api_name <> ''
	AND line_item_usage_type NOT LIKE '%Snap%'
	AND line_item_usage_type LIKE '%EBS%' 
),
-- Step 2: Pivot table so storage types cost and usage into separate columns
ebs_spend AS (
  SELECT DISTINCT
    bill_billing_period_start_date AS billing_period,
	date_trunc('month',line_item_usage_start_date) AS usage_date,
	bill_payer_account_id AS payer_account_id,
	line_item_usage_account_id AS linked_account_id,
	line_item_resource_id AS resource_id,
	product_volume_api_name AS volume_api_name,
	SUM(CASE
	  WHEN (((pricing_unit = 'GB-Mo' or pricing_unit = 'GB-month') or pricing_unit = 'GB-month') AND  line_item_usage_type LIKE '%EBS:VolumeUsage%') THEN line_item_usage_amount ELSE 0 
	END) AS usage_storage_gb_mo,
	SUM(CASE
	  WHEN (pricing_unit = 'IOPS-Mo' AND line_item_usage_type LIKE '%IOPS%') THEN line_item_usage_amount 
	  ELSE 0 
	END) AS usage_iops_mo,
	SUM(CASE 
	  WHEN (pricing_unit = 'GiBps-mo' AND line_item_usage_type LIKE '%Throughput%') THEN  line_item_usage_amount 
	  ELSE 0 
	END) AS usage_throughput_gibps_mo,
	SUM(CASE 
	  WHEN ((pricing_unit = 'GB-Mo' or pricing_unit = 'GB-month') AND line_item_usage_type LIKE '%EBS:VolumeUsage%') THEN (line_item_unblended_cost) 
	  ELSE 0 
	END) AS cost_storage_gb_mo,
	SUM(CASE 
	  WHEN (pricing_unit = 'IOPS-Mo' AND  line_item_usage_type LIKE '%IOPS%') THEN  (line_item_unblended_cost) 
	  ELSE 0 
	END) AS cost_iops_mo,
	SUM(CASE 
	  WHEN (pricing_unit = 'GiBps-mo' AND  line_item_usage_type LIKE '%Throughput%') THEN  (line_item_unblended_cost) 
	  ELSE 0 
	END) AS cost_throughput_gibps_mo
	FROM
	  ebs_all
	GROUP BY 
	  1, 2, 3, 4, 5,6
),
ebs_spend_with_unit_cost AS (
  SELECT 
    *,
	cost_storage_gb_mo/usage_storage_gb_mo AS current_unit_cost,
	CASE
	  WHEN usage_storage_gb_mo <= 150 THEN 'under 150GB-Mo'
	  WHEN usage_storage_gb_mo > 150 AND usage_storage_gb_mo <= 1000 THEN 'between 150-1000GB-Mo' 
	  ELSE 'over 1000GB-Mo' 
	END AS storage_summary,
	CASE
	  WHEN volume_api_name <> 'gp2' THEN 0
	  WHEN usage_storage_gb_mo*3 < 3000 THEN 3000 - 3000
	  WHEN usage_storage_gb_mo*3 > 16000 THEN 16000 - 3000
	  ELSE usage_storage_gb_mo*3 - 3000
	END AS gp2_usage_added_iops_mo,
	CASE
	  WHEN volume_api_name <> 'gp2' THEN 0
	  WHEN usage_storage_gb_mo <= 150 THEN 0 
	  ELSE 125
	END AS gp2_usage_added_throughput_gibps_mo,
	cost_storage_gb_mo + cost_iops_mo + cost_throughput_gibps_mo AS ebs_all_cost,
	CASE
	  WHEN volume_api_name = 'sc1' THEN  (cost_iops_mo + cost_throughput_gibps_mo + cost_storage_gb_mo)
	  ELSE 0
	END AS ebs_sc1_cost,
	CASE
	  WHEN volume_api_name = 'st1' THEN  (cost_iops_mo + cost_throughput_gibps_mo + cost_storage_gb_mo)
	  ELSE 0
	END AS ebs_st1_cost,
	CASE
	  WHEN volume_api_name = 'standard' THEN  (cost_iops_mo + cost_throughput_gibps_mo + cost_storage_gb_mo)
	  ELSE 0
	END AS ebs_standard_cost,
	CASE
	  WHEN volume_api_name = 'io1' THEN  (cost_iops_mo + cost_throughput_gibps_mo + cost_storage_gb_mo)
	  ELSE 0
	END AS ebs_io1_cost,
	CASE
	  WHEN volume_api_name = 'io2' THEN  (cost_iops_mo + cost_throughput_gibps_mo + cost_storage_gb_mo)
	  ELSE 0
	END AS ebs_io2_cost,
	CASE
	  WHEN volume_api_name = 'gp2' THEN  (cost_iops_mo + cost_throughput_gibps_mo + cost_storage_gb_mo)
	  ELSE 0
	END AS ebs_gp2_cost,
	CASE
	  WHEN volume_api_name = 'gp3' THEN  (cost_iops_mo + cost_throughput_gibps_mo + cost_storage_gb_mo)
	  ELSE 0
	END AS ebs_gp3_cost,
	CASE
	  WHEN volume_api_name = 'gp2' THEN cost_storage_gb_mo*0.8/usage_storage_gb_mo
	  ELSE 0
	END AS estimated_gp3_unit_cost
  FROM
    ebs_spend
),
ebs_before_map AS ( 
  SELECT DISTINCT
    billing_period,
	payer_account_id,
	linked_account_id,
	resource_id,
	volume_api_name,
	storage_summary,
	SUM(usage_storage_gb_mo) AS usage_storage_gb_mo,
	SUM(usage_iops_mo) AS usage_iops_mo,
	SUM(usage_throughput_gibps_mo) AS usage_throughput_gibps_mo,
	SUM(gp2_usage_added_iops_mo) gp2_usage_added_iops_mo,
	SUM(gp2_usage_added_throughput_gibps_mo) AS gp2_usage_added_throughput_gibps_mo,
	SUM(ebs_all_cost) AS ebs_all_cost,
	SUM(ebs_sc1_cost) AS ebs_sc1_cost,
	SUM(ebs_st1_cost) AS ebs_st1_cost ,
	SUM(ebs_standard_cost) AS ebs_standard_cost,
	SUM(ebs_io1_cost) AS ebs_io1_cost,
	SUM(ebs_io2_cost) AS ebs_io2_cost,
	SUM(ebs_gp2_cost) AS ebs_gp2_cost,
	SUM(ebs_gp3_cost) AS ebs_gp3_cost,
	/* Calculate cost for gp2 gp3 estimate using the following
	- Storage always 20% cheaper
	- Additional iops per iops-mo is 6% of the cost of 1 gp3 GB-mo
	- Additional throughput per gibps-mo is 50% of the cost of 1 gp3 GB-mo */
	SUM(CASE 
	/*ignore non gp2' */
	  WHEN volume_api_name = 'gp2' THEN ebs_gp2_cost 
	        - (cost_storage_gb_mo*0.8 
			+ estimated_gp3_unit_cost * 0.5 * gp2_usage_added_throughput_gibps_mo
			+ estimated_gp3_unit_cost * 0.06 * gp2_usage_added_iops_mo)
	  ELSE 0
	END) AS ebs_gp3_potential_savings
  FROM 
    ebs_spend_with_unit_cost 
  GROUP BY 
    1, 2, 3, 4, 5, 6)
SELECT DISTINCT
  billing_period,
  payer_account_id,
  linked_account_id,
  resource_id,
  volume_api_name,
  usage_storage_gb_mo,
  usage_iops_mo,
  usage_throughput_gibps_mo,
  storage_summary,
  gp2_usage_added_iops_mo,
  gp2_usage_added_throughput_gibps_mo,
  ebs_all_cost,
  ebs_sc1_cost,
  ebs_st1_cost ,
  ebs_standard_cost,
  ebs_io1_cost,
  ebs_io2_cost,
  ebs_gp2_cost,
  ebs_gp3_cost,
  ebs_gp3_potential_savings
FROM 
  ebs_before_map;

-- Step 1: Filter CUR to return all ebs ec2 snapshot usage data
WITH snapshot_usage_all_time AS (
  SELECT
    year,
	month,
	bill_billing_period_start_date billing_period,
	line_item_usage_start_date usage_start_date,
	bill_payer_account_id payer_account_id,
	line_item_usage_account_id linked_account_id,
	line_item_resource_id resource_id,
	(CASE 
	  WHEN (line_item_usage_type LIKE '%EBS:SnapshotArchive%') THEN 'Snapshot_Archive' 
	  WHEN (line_item_usage_type LIKE '%EBS:Snapshot%') THEN 'Snapshot' 
	  ELSE "line_item_operation" 
	END) AS snapshot_type,
	line_item_usage_amount,
	line_item_unblended_cost,
	pricing_public_on_demand_cost
  FROM
    ${table_name}
  WHERE 
    (((((bill_payer_account_id <> '') 
	AND (line_item_resource_id <> '')) 
	AND (line_item_line_item_type LIKE '%Usage%')) 
	AND (line_item_product_code = 'AmazonEC2')) 
	AND (line_item_usage_type LIKE '%EBS:Snapshot%'))
),	
-- Step 2: Return most recent billing_period and the first billing_period
request_dates AS (
  SELECT DISTINCT
    resource_id AS request_dates_resource_id, 
	MIN(usage_start_date) AS start_date
  FROM
    snapshot_usage_all_time
  WHERE
    (snapshot_type = 'Snapshot')
  GROUP BY 
    1
), 
-- Step 3: Pivot table so looking at previous month filtered for only snapshots still available in the current month
snapshot_usage_all_time_before_map AS (
  SELECT DISTINCT
    billing_period,
	request_dates.start_date,
	payer_account_id,
	linked_account_id,
	snapshot_type,
	resource_id,
	SUM(line_item_usage_amount) usage_quantity,
	SUM(line_item_unblended_cost) ebs_snapshot_cost,
	SUM(pricing_public_on_demand_cost) public_cost,
	SUM((CASE 
	  WHEN ((request_dates.start_date > (billing_period - INTERVAL  '12' MONTH)) AND (snapshot_type = 'Snapshot')) THEN line_item_unblended_cost 
	  ELSE 0 
	END)) AS ebs_snapshots_under_1yr_cost,  
	/*No savings estimate since it uses uses 100% of snapshot cost for snapshots over 6mos as savings estimate*/ 
	SUM((CASE 
	  WHEN ((request_dates.start_date <= (billing_period - INTERVAL  '12' MONTH)) AND (snapshot_type = 'Snapshot')) THEN line_item_unblended_cost 
	  ELSE 0 
	END)) AS ebs_snapshots_over_1yr_cost
  FROM
    (snapshot_usage_all_time snapshot
	LEFT JOIN request_dates ON (request_dates.request_dates_resource_id = snapshot.resource_id))
      WHERE (CAST("concat"(snapshot.year, '-', snapshot.month, '-01') AS date) = ("date_trunc"('month', current_date) - INTERVAL  '1' MONTH))
  GROUP BY 
    1, 2, 3, 4, 5, 6
)

-- Step 4: Add all data
SELECT
  billing_period,
  start_date,
  payer_account_id,
  linked_account_id,
  resource_id,
  snapshot_type,
  usage_quantity,
  ebs_snapshot_cost,
  public_cost,
  ebs_snapshots_under_1yr_cost,
  ebs_snapshots_over_1yr_cost
FROM
  snapshot_usage_all_time_before_map;

-- Step 1: Enter S3 standard savings savings assumption. Default is set to 0.3 for 30% savings 
WITH inputs AS (
  SELECT 
    * 
  FROM 
    (
    VALUES (0.3,.68,2,10,3)
	) 
	t(s3_standard_savings,
	  sia_to_glacier_instant_retrieval_storage_savings,
	  sia_to_glacier_instant_retrieval_tier1_increase,
	  sia_to_glacier_instant_retrieval_tier2_increase,
	  sia_to_glacier_instant_retrieval_retriveal_increase
    )
),

-- Step 2: Filter CUR to return all storage usage data and update ${table_name} with your CUR table table  
s3_usage_all_time AS (
  SELECT
    year,
	month,
	bill_billing_period_start_date AS billing_period,
	line_item_usage_start_date AS usage_start_date,
	bill_payer_account_id AS payer_account_id,
	line_item_usage_account_id AS linked_account_id,
	line_item_resource_id AS resource_id,
	s3_standard_savings,
	sia_to_glacier_instant_retrieval_storage_savings,
	sia_to_glacier_instant_retrieval_tier1_increase,
	sia_to_glacier_instant_retrieval_tier2_increase,
	sia_to_glacier_instant_retrieval_retriveal_increase,
	line_item_operation AS operation,
	line_item_usage_type AS usage_type,
	CASE 
	  WHEN line_item_usage_type LIKE '%EarlyDelete%' THEN 'EarlyDelete' 
	  ELSE line_item_operation 
	END AS early_delete_adjusted_operation,
	CASE
	  WHEN line_item_product_code = 'AmazonGlacier' AND line_item_operation = 'Storage' THEN 'Amazon Glacier'
	  WHEN line_item_product_code = 'AmazonS3' AND product_volume_type LIKE '%Intelligent%' AND line_item_operation LIKE '%IntelligentTiering%' THEN 'Intelligent-Tiering'	  
	  ELSE product_volume_type
	END AS storage_class_type,
	pricing_unit,
	SUM(line_item_usage_amount) AS usage_quantity,
	SUM(line_item_unblended_cost) AS unblended_cost,
	SUM(CASE
	  WHEN (pricing_unit = 'GB-Mo' AND line_item_operation like '%Storage%' AND product_volume_type LIKE '%Glacier Deep Archive%') THEN line_item_unblended_cost
	  WHEN (pricing_unit = 'GB-Mo' AND line_item_operation like '%Storage%') THEN line_item_unblended_cost 
	  ELSE 0
	END) AS s3_all_storage_cost, 
	SUM(CASE 
	  WHEN (pricing_unit = 'GB-Mo' AND line_item_operation like '%Storage%') THEN line_item_usage_amount 
	  ELSE 0 
	END) AS s3_all_storage_usage_quantity
  FROM 
	${table_name}, 
	inputs
  WHERE 
    bill_payer_account_id <> ''
	AND line_item_resource_id <> ''
	AND line_item_line_item_type LIKE '%Usage%'
	AND (line_item_product_code LIKE '%AmazonGlacier%' 
	  OR line_item_product_code LIKE '%AmazonS3%')
  GROUP BY 
    1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17
),

-- Step 3: Return most recent request date to understand if bucket is in active use
most_recent_request AS (
  SELECT DISTINCT
    resource_id,
	MAX(usage_start_date) AS last_request_date
  FROM 
    s3_usage_all_time
  WHERE 
    usage_quantity > 0
	AND operation IN ('PutObject', 'PutObjectForRepl', 'GetObject', 'CopyObject') 
	AND pricing_unit = 'Requests'
  GROUP BY 
    1
),

-- Step 4: Pivot table so storage classes into separate columns and filter for current month
month_usage AS (
  SELECT DISTINCT
    billing_period,
	date_trunc('month', usage_start_date) AS "usage_date",
	payer_account_id,
	linked_account_id,
	s3.resource_id,
	most_recent_request.last_request_date AS "last_requests",
	s3_standard_savings,
	sia_to_glacier_instant_retrieval_storage_savings,
	sia_to_glacier_instant_retrieval_tier1_increase,
	sia_to_glacier_instant_retrieval_tier2_increase,
	sia_to_glacier_instant_retrieval_retriveal_increase,
	SUM(unblended_cost) AS s3_all_cost,
	-- All Storage
    SUM(s3_all_storage_cost) AS s3_all_storage_cost,
    SUM(s3_all_storage_usage_quantity) AS s3_all_storage_usage_quantity,
	-- S3 Standard
	SUM(CASE 
	  WHEN storage_class_type = 'Standard' THEN s3_all_storage_cost 
	  ELSE 0 
	END) AS s3_standard_storage_cost, 
	SUM(CASE 
	  WHEN storage_class_type = 'Standard' THEN s3_all_storage_usage_quantity 
	  ELSE 0 
	END) AS s3_standard_storage_usage_quantity,
	-- S3 Standard Infrequent Access
	SUM(CASE 
	  WHEN storage_class_type = 'Standard - Infrequent Access' THEN s3_all_storage_cost 
	  ELSE 0 
	END) AS s3_standard_ia_storage_cost,
	SUM(CASE 
	  WHEN storage_class_type = 'Standard - Infrequent Access' THEN s3_all_storage_usage_quantity 
	  ELSE 0 
	END) AS s3_standard_ia_storage_usage_quantity,
	SUM(CASE 
	  WHEN usage_type LIKE '%Requests-SIA-Tier1%' THEN unblended_cost 
	  ELSE 0 
	END) AS s3_standard_ia_tier1_cost,
	SUM(CASE 
	  WHEN usage_type LIKE '%Requests-SIA-Tier2%' THEN unblended_cost 
	  ELSE 0 
	END) AS s3_standard_ia_tier2_cost,	
	SUM(CASE 
	  WHEN usage_type LIKE '%Retrieval-SIA%' THEN unblended_cost 
	  ELSE 0 
	END) AS s3_standard_ia_retrieval_cost,
	-- S3 One Zone Infrequent Access
	SUM(CASE 
	  WHEN storage_class_type = 'One Zone - Infrequent Access' THEN s3_all_storage_cost 
	  ELSE 0 
	END) AS s3_onezone_ia_storage_cost,
	SUM(CASE 
	  WHEN storage_class_type = 'One Zone - Infrequent Access' THEN s3_all_storage_usage_quantity 
	  ELSE 0 
	END) AS s3_onezone_ia_storage_usage_quantity,
	-- S3 Reduced Redundancy
	SUM(CASE 
	  WHEN storage_class_type = 'Reduced Redundancy' THEN s3_all_storage_cost 
	  ELSE 0 
	END) AS s3_reduced_redundancy_storage_cost,
	SUM(CASE 
	  WHEN storage_class_type = 'Reduced Redundancy' THEN s3_all_storage_usage_quantity 
	  ELSE 0 
	END) AS s3_reduced_redundancy_storage_usage_quantity,
	-- S3 Intelligent-Tiering
	SUM(CASE 
	  WHEN storage_class_type LIKE '%Intelligent%' THEN s3_all_storage_cost 
	  ELSE 0 
	END) AS s3_intelligent_tiering_storage_cost,
	SUM(CASE 
	  WHEN storage_class_type LIKE '%Intelligent%' THEN s3_all_storage_usage_quantity 
	  ELSE 0 
    END) AS s3_intelligent_tiering_storage_usage_quantity,
	-- S3 Glacier Instant Retrieval   
	SUM(CASE 
	  WHEN storage_class_type LIKE '%Instant%' AND storage_class_type NOT LIKE '%Intelligent%' THEN s3_all_storage_cost 
	  ELSE 0 
	END) AS s3_glacier_instant_retrieval_storage_cost,
	SUM(CASE 
	  WHEN storage_class_type LIKE '%Instant%' AND storage_class_type NOT LIKE '%Intelligent%' THEN s3_all_storage_usage_quantity 
	  ELSE 0 
	END) AS s3_glacier_instant_retrieval_storage_usage_quantity,
	SUM(CASE 
	  WHEN usage_type LIKE '%Requests-GIR-Tier1%' THEN unblended_cost 
	  ELSE 0 
	END) AS s3_glacier_instant_retrieval_tier1_cost,
	SUM(CASE 
	  WHEN usage_type LIKE '%Requests-GIR-Tier2%' THEN unblended_cost 
	  ELSE 0 
	END) AS s3_glacier_instant_retrieval_tier2_cost,
	SUM(CASE 
	  WHEN usage_type LIKE '%Retrieval-SIA-GIR%' THEN unblended_cost 
	  ELSE 0 
	END) AS s3_glacier_instant_retrieval_retrieval_cost,
	-- S3 Glacier Flexible Retrieval
	SUM(CASE 
	  WHEN storage_class_type = 'Amazon Glacier' THEN s3_all_storage_cost 
	  ELSE 0 
	END) AS s3_glacier_flexible_retrieval_storage_cost,
	SUM(CASE 
	  WHEN storage_class_type = 'Amazon Glacier' THEN s3_all_storage_usage_quantity 
	  ELSE 0 
	END) AS s3_glacier_flexible_retrieval_storage_usage_quantity,
	-- Glacier Deep Archive
	SUM(CASE 
	  WHEN storage_class_type = 'Glacier Deep Archive' THEN s3_all_storage_cost 
	  ELSE 0 
	END) AS s3_glacier_deep_archive_storage_storage_cost,
	SUM(CASE 
	  WHEN storage_class_type = 'Glacier Deep Archive' THEN s3_all_storage_usage_quantity 
	  ELSE 0 
	END) AS s3_glacier_deep_archive_storage_usage_quantity,
	-- Operations
	SUM(CASE 
	  WHEN operation = 'PutObject' AND pricing_unit = 'Requests' THEN usage_quantity 
	  ELSE 0 
	END) AS s3_put_object_usage_quantity,
	SUM(CASE 
	  WHEN operation = 'PutObjectForRepl' AND pricing_unit = 'Requests' THEN usage_quantity 
	  ELSE 0 
	END) AS s3_put_object_replication_usage_quantity,
	SUM(CASE 
	  WHEN operation = 'GetObject' AND pricing_unit = 'Requests' THEN usage_quantity 
	  ELSE 0 
	END) AS s3_get_object_usage_quantity,
	SUM(CASE 
	  WHEN operation = 'CopyObject' AND pricing_unit = 'Requests' THEN usage_quantity 
	  ELSE 0 
	END) AS s3_copy_object_usage_quantity,
	SUM(CASE 
	  WHEN operation = 'Inventory' THEN usage_quantity 
	  ELSE 0 
	END) AS s3_inventory_usage_quantity,
	SUM(CASE 
	  WHEN operation = 'S3.STORAGE_CLASS_ANALYSIS.OBJECT' THEN usage_quantity 
	  ELSE 0 
	END) AS s3_analytics_usage_quantity,
	SUM(CASE 
	  WHEN operation like '%Transition%' THEN usage_quantity 
	  ELSE 0 
	END) AS s3_transition_usage_quantity,
	SUM(CASE 
	  WHEN early_delete_adjusted_operation = 'EarlyDelete' THEN unblended_cost
	  ELSE 0 
	END) AS s3_early_delete_cost	
  FROM s3_usage_all_time s3
    LEFT JOIN most_recent_request ON most_recent_request.resource_id = s3.resource_id
  WHERE 
    CAST(concat(s3.year, '-', s3.month, '-01') AS date) = (date_trunc('month', current_date) - INTERVAL '1' MONTH)
  GROUP BY 
    1,2,3,4,5,6,7,8,9,10,11
)

-- Step 6: Apply KPI logic - Add or Adjust bucket name keywords based on your requirements 
SELECT DISTINCT
  billing_period,
  usage_date,
  payer_account_id,
  linked_account_id,
  resource_id,
  CASE 
    WHEN resource_id LIKE '%backup%' THEN 'backup'
	WHEN resource_id LIKE '%archive%' THEN 'archive'
	WHEN resource_id LIKE '%historical%' THEN 'historical'	
	WHEN resource_id LIKE '%log%' THEN 'log' 
	WHEN resource_id LIKE '%compliance%' THEN 'compliance'
	ELSE 'Other'
  END AS bucket_name_keywords, 
  last_requests,
  CASE
    WHEN last_requests >= (usage_date - INTERVAL  '2' MONTH) THEN 'No Action'
	WHEN s3_all_storage_cost = s3_standard_storage_cost THEN 'Potential Action'
	ELSE 'No Action'
  END AS s3_standard_underutilized_optimization,
  CASE
    WHEN ((s3_transition_usage_quantity)> 0  AND (last_requests >= (usage_date - INTERVAL  '1' MONTH)))  THEN 'No Action'
	WHEN s3_put_object_replication_usage_quantity > 0 THEN 'Potential Action'
	ELSE 'No Action' 
  END AS s3_replication_bucket_optimization,
  CASE
    WHEN s3_all_storage_cost = s3_standard_storage_cost THEN 'Yes'
	ELSE 'No' 
  END AS s3_standard_only_bucket,
  CASE
    WHEN s3_glacier_deep_archive_storage_storage_cost > 0 THEN 'in use'
    WHEN s3_glacier_flexible_retrieval_storage_cost > 0 THEN 'in use'
    WHEN s3_glacier_instant_retrieval_storage_cost > 0 THEN 'in use'
    ELSE 'not in use' 
  END AS s3_archive_in_use,
  CASE 
    WHEN s3_inventory_usage_quantity > 0 THEN 'in use' 
    ELSE 'not in use' 
  END AS s3_inventory_in_use,
  CASE 
    WHEN s3_analytics_usage_quantity > 0 THEN 'in use' 
	ELSE 'not in use' 
  END AS s3_analytics_in_use,
  CASE 
    WHEN s3_intelligent_tiering_storage_usage_quantity > 0 THEN 'in use' 
	ELSE 'not in use' 
  END AS s3_int_in_use,
  s3_standard_storage_cost * s3_standard_savings AS s3_standard_storage_potential_savings, 
  (s3_standard_ia_retrieval_cost + s3_standard_ia_tier1_cost + s3_standard_ia_tier2_cost + s3_standard_ia_storage_cost) 
    -((sia_to_glacier_instant_retrieval_storage_savings * s3_standard_ia_storage_cost)
	+(sia_to_glacier_instant_retrieval_tier1_increase * s3_standard_ia_tier1_cost)
	+(sia_to_glacier_instant_retrieval_tier2_increase * s3_standard_ia_tier2_cost)
	+(sia_to_glacier_instant_retrieval_retriveal_increase * s3_standard_ia_retrieval_cost)
  ) AS s3_glacier_instant_retrieval_potential_savings,
  s3_all_cost,
  (s3_all_cost/s3_all_storage_usage_quantity) AS s3_all_unit_cost,
  s3_all_storage_cost,
  s3_all_storage_usage_quantity,
  (s3_all_storage_cost/s3_all_storage_usage_quantity) AS s3_all_storage_unit_cost,
  s3_standard_storage_cost,
  s3_standard_storage_usage_quantity,
  (s3_standard_storage_cost/s3_standard_storage_usage_quantity) AS s3_standard_storage_unit_cost,
  s3_intelligent_tiering_storage_cost,
  s3_intelligent_tiering_storage_usage_quantity,
  (s3_intelligent_tiering_storage_cost/s3_intelligent_tiering_storage_usage_quantity) AS s3_intelligent_tiering_storage_unit_cost,
  s3_standard_ia_storage_cost,
  s3_standard_ia_storage_usage_quantity,
  (s3_standard_ia_storage_cost/s3_standard_ia_storage_usage_quantity) AS s3_standard_ia_storage_unit_cost,
  s3_onezone_ia_storage_cost,
  s3_onezone_ia_storage_usage_quantity,
  (s3_onezone_ia_storage_cost/s3_onezone_ia_storage_usage_quantity) AS s3_onezone_ia_storage_unit_cost,
  s3_reduced_redundancy_storage_cost,
  s3_reduced_redundancy_storage_usage_quantity,
  (s3_reduced_redundancy_storage_cost/s3_reduced_redundancy_storage_usage_quantity) AS s3_reduced_redundancy_storage_unit_cost,
  s3_glacier_instant_retrieval_storage_cost,
  s3_glacier_instant_retrieval_storage_usage_quantity,
  (s3_glacier_instant_retrieval_storage_cost/s3_glacier_instant_retrieval_storage_usage_quantity) AS s3_glacier_instant_retrieval_storage_unit_cost,
  s3_glacier_flexible_retrieval_storage_cost,
  s3_glacier_flexible_retrieval_storage_usage_quantity,
  (s3_glacier_flexible_retrieval_storage_cost/s3_glacier_flexible_retrieval_storage_usage_quantity) AS s3_glacier_flexible_retrieval_storage_unit_cost,
  s3_glacier_deep_archive_storage_storage_cost,
  s3_glacier_deep_archive_storage_usage_quantity,
  (s3_glacier_deep_archive_storage_storage_cost/s3_glacier_deep_archive_storage_usage_quantity)	AS s3_glacier_deep_archive_storage_unit_cost,
  s3_early_delete_cost,
  s3_transition_usage_quantity,
  s3_put_object_usage_quantity,
  s3_put_object_replication_usage_quantity,
  s3_get_object_usage_quantity,
  s3_copy_object_usage_quantity,
  s3_standard_ia_tier1_cost,
  s3_standard_ia_tier2_cost,
  s3_standard_ia_retrieval_cost,
  s3_glacier_instant_retrieval_tier1_cost,
  s3_glacier_instant_retrieval_tier2_cost,
  s3_glacier_instant_retrieval_retrieval_cost		
FROM 
  month_usage;