s3://cm-cur-012345678901/	
  cur/012345678901/
    cid-cur/
      20241001-20241101/
        cid-cur-Manifest.json
      20241101-20241201/
        cid-cur-Manifest.json
      20241201-20250101/
        cid-cur-Manifest.json
      20250101-20250201/
        cid-cur-Manifest.json
      cid-cur/
        year=2024/
          month=10/
            cid-cur-1.snappy.parquet
          month=11/
            cid-cur-1.snappy.parquet
          month=12/
            cid-cur-1.snappy.parquet
        year=2025/
          month=01/
            cid-cur-1.snappy.parquet

SELECT
  distinct bill_billing_period_start_date 
FROM
  mcur.cur
ORDER BY
  bill_billing_period_start_date desc;

SELECT
  line_item_usage_account_id, round(sum("line_item_unblended_cost"),2) as cost
FROM
  mcur.cur
WHERE
  year = '2024' AND month = '10'
GROUP BY
  line_item_usage_account_id
ORDER BY
  cost desc;

SELECT
  line_item_product_code, 
  line_item_line_item_description, 
  SUM(line_item_unblended_cost) AS sum_line_item_unblended_cost 
FROM 
  mcur.cur
WHERE 
  year = '2024' AND month = '11'
  AND line_item_product_code = 'AmazonEC2'
  AND line_item_line_item_type NOT IN ('Tax','Refund','Credit')
GROUP BY 
  line_item_product_code, 
  line_item_line_item_description
ORDER BY 
  sum_line_item_unblended_cost DESC;

SELECT
  bill_payer_account_id,
  bill_billing_period_start_date,
  line_item_usage_account_id,
  savings_plan_savings_plan_a_r_n,
  line_item_product_code, 
  line_item_usage_type,
  sum(line_item_usage_amount) as Usage, 
  line_item_line_item_description,
  pricing_public_on_demand_rate,
  sum(pricing_public_on_demand_cost) as PublicCost,
  savings_plan_savings_plan_rate,
  sum(savings_plan_savings_plan_effective_cost) as SavingsPlanCost 
FROM
  mcur.cur
WHERE
  line_item_line_item_Type like 'SavingsPlanCoveredUsage' AND year = '2025' AND month = '1'
GROUP BY
  bill_payer_account_id,
  bill_billing_period_start_date,
  line_item_usage_account_id,
  savings_plan_savings_plan_a_r_n,
  line_item_product_code,
  line_item_usage_type,
  line_item_unblended_rate,
  line_item_line_item_description,
  pricing_public_on_demand_rate,
  savings_plan_savings_plan_rate;

{
	"Version": "2008-10-17",
	"Statement": [
		{ ** 既存ポリシー **
		},
		{
			"Sid": "1",
			"Effect": "Allow",
			"Principal": {
				"AWS": [
					"arn:aws:iam::111111111111:role/cur-parquet-replication",
					"arn:aws:iam::222222222222:role/cur-parquet-replication",
					"arn:aws:iam::333333333333:role/cur-parquet-replication"
				]
			},
			"Action": [
				"s3:ReplicateDelete",
				"s3:ReplicateObject",
				"s3:ReplicateTags",
				"s3:ObjectOwnerOverrideToBucketOwner"
			],
			"Resource": "arn:aws:s3:::cm-cur-999999999999/*"
		}
	]
}

SELECT 
  line_item_product_code,
  round(sum("line_item_unblended_cost"),2) as cost FROM
  mcur.cur
WHERE
  year = '2024' AND month = '12'
GROUP BY
  line_item_product_code
ORDER BY
  cost desc
LIMIT 10;

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 mcur.cur
WHERE year = '2024'
	AND month = '11'
  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;