-- AWS CUR Athena starter queries for monthly cost reviews.
-- Replace cur_database.cur_table with your CUR database and table.

-- 1. Monthly service spend
SELECT
  date_trunc('month', line_item_usage_start_date) AS usage_month,
  product_product_name AS service,
  SUM(line_item_unblended_cost) AS unblended_cost
FROM cur_database.cur_table
WHERE line_item_line_item_type IN ('Usage', 'DiscountedUsage', 'SavingsPlanCoveredUsage')
GROUP BY 1, 2
ORDER BY usage_month DESC, unblended_cost DESC;

-- 2. Untagged spend by account
SELECT
  line_item_usage_account_id AS account_id,
  product_product_name AS service,
  SUM(line_item_unblended_cost) AS untagged_cost
FROM cur_database.cur_table
WHERE resource_tags_user_project IS NULL
  AND line_item_usage_start_date >= date_add('day', -30, current_date)
GROUP BY 1, 2
ORDER BY untagged_cost DESC
LIMIT 50;

-- 3. Savings Plans coverage candidates
SELECT
  line_item_usage_account_id AS account_id,
  product_product_name AS service,
  product_region AS region,
  SUM(line_item_usage_amount) AS usage_amount,
  SUM(line_item_unblended_cost) AS on_demand_cost
FROM cur_database.cur_table
WHERE line_item_line_item_type = 'Usage'
  AND product_product_name IN ('Amazon Elastic Compute Cloud', 'AWS Lambda', 'Amazon Elastic Container Service')
GROUP BY 1, 2, 3
ORDER BY on_demand_cost DESC
LIMIT 100;
