Athen Cost ans usage report query

We can slightly modify this query found on Well-Architected Labs page to achieve that: https://wellarchitectedlabs.com/cost/300_labs/300_cur_queries/queries/global/#bill-details-by-service To get resource details for top 10 resources in the last week you would add line_item_resource_id field to the Select statement, and also modify the first line with DATE FORMAT so that it doesn't produce an aggregated date by Month. When an additional SELECT ITEM is added, you must also make sure that field is included in the GROUP BY statement. Because one field was inserted into that list, the field designated as "3" which means the 3rd select field, is now the 4th, so you change that to a "4" as well. The WHERE clause can be modified to do a date range filter as described here: https://wellarchitectedlabs.com/cost/300_labs/300_cur_queries/query_help/#filtering-by-date
Then to get the "Top 10", add a field in the ORDER BY section for "line_item_resource_id DESC" (this is descending order) and a LIMIT 10 to show only the top 10. Also, modify the start and end dates in the WHERE clause example below to match the date range you are looking for. Finally, to retrieve only cost items that are associated to a resource ID, you should add another WHERE clause to filter out any lines with blank resource id value. Costs such as Marketplace purchases etc will have a blank value for resource id.

I believe this modified version will work (make sure you change the ${table_name} to match the name of your Athena table for your CUR report.

SELECT 
      line_item_usage_start_date,
      bill_bill_type,
      line_item_resource_id,
      CASE
        WHEN (product_product_family = 'Data Transfer') THEN 'Data Transfer' 
        ELSE replace(replace(replace(product_product_name, 'Amazon '),'Amazon'),'AWS ') 
      END AS product_product_name,
      product_location,
      line_item_line_item_description,
      SUM(line_item_unblended_cost) AS sum_line_item_unblended_cost,
      SUM(line_item_usage_amount) AS sum_line_item_usage_amount
    FROM 
      ${table_name}
    WHERE line_item_usage_start_date >= CAST('2023-01-01 00:00:00' AS TIMESTAMP) 
      AND line_item_usage_start_date < CAST('2023-01-08 01:23:45' AS TIMESTAMP)
      AND line_item_resource_id <> ''

    GROUP BY 
      1,
      bill_bill_type,
      line_item_resource_id,
      4,
      product_location,
      line_item_line_item_description
    HAVING SUM(line_item_usage_amount) > 0
    ORDER BY 
      line_item_resource_id DESC,
      line_item_usage_start_date,
      bill_bill_type,
      product_product_name,
      product_location,
      line_item_line_item_description
    LIMIT 10;