SELECT
	line_item_usage_account_id,
	product_region,
    CONCAT(
    SPLIT(line_item_resource_id, '/')[SAFE_OFFSET(0)],
    IFNULL(CONCAT('/', SPLIT(line_item_resource_id, '/')[SAFE_OFFSET(1)]), '')
  ) AS resource_prefix,
  round(SUM(line_item_unblended_cost),2) AS total_cost
FROM `gcp-digital-bi`.aws_nonprod_billing.`385595570414_aws_billing_data`
WHERE line_item_product_code = 'AmazonECR'
AND line_item_line_item_type = 'Usage'
AND `year` = 2025
AND `month` = 7
GROUP BY line_item_usage_account_id,product_region,resource_prefix
ORDER BY product_region,total_cost DESC