Compute

These are queries for AWS Services under the Compute product family .

Use the clipboard in the top right of the text boxes below to copy all of the text to be pasted.

You may need to change variables used as placeholders in your query. ${table_Name} is a common variable which needs to be replaced. Example: cur_db.cur_table

Table of Contents

EC2 Total Spend

Query Description

This query will display the top costs for all spend with the product code of ‘AmazonEC2’. This will include all pricing categories (i.e. OnDemand, Reserved etc..) as well as charges for storage on EC2 (i.e. gp2). The query will output the product code as well as the product description to provide context. It is ordered by largest to smallest spend.

Pricing

Please refer to the EC2 pricing page .

These links are provided as an example to compare CUR report output to Cost Explorer output.

Unblended Cost Link

Sample Output

Images/ec2_total_spend.png

Download SQL File

Link to Code

Copy Query

SELECT line_item_product_code, 
line_item_line_item_description, 
round(sum(line_item_unblended_cost),2) as sum_line_item_unblended_cost 
FROM ${table_name}
WHERE
year = '2020' AND (month BETWEEN '7' AND '9' OR month BETWEEN '07' AND '09')
AND line_item_product_code like '%AmazonEC2%'
AND line_item_line_item_type NOT IN ('Tax','Refund')
AND line_item_product_code like '%AmazonEC2%'
GROUP BY line_item_product_code, 
line_item_line_item_description
ORDER BY sum_line_item_unblended_cost desc

Back to Table of Contents

EC2 Hours a Day

Query Description

This query will provide the EC2 usage quantity measured in hours for each purchase option and each instance type. The output will include detailed information about the instance type, amortized cost, purchase option, and usage quantity. The output will be ordered by usage quantity in descending order.

This query will not run against CUR data from accounts which have purchased EC2 Reserved Instances or Savings Plans.

Pricing Page

Please refer to the EC2 pricing page .

Sample Output

Images/ec2runninghours.png

Download SQL File

Link to Code

Copy Query

  SELECT 
    year,
    month,
    bill_billing_period_start_date,
    date_trunc('hour', line_item_usage_start_date) as hour_line_item_usage_start_date, 
    bill_payer_account_id, 
    line_item_usage_account_id,
    (CASE 
      WHEN (line_item_usage_type LIKE '%SpotUsage%') THEN
        SPLIT_PART(line_item_usage_type, ':', 2)
      ELSE product_instance_type
      END) AS product_instance_type,
    (CASE
      WHEN (savings_plan_savings_plan_a_r_n <> '') THEN
        'SavingsPlan'
      WHEN (reservation_reservation_a_r_n <> '') THEN
        'Reserved'
      WHEN (line_item_usage_type LIKE '%Spot%') THEN
        'Spot'
      ELSE 'OnDemand' END) as purchase_option, 
      sum(CASE
        WHEN line_item_line_item_type = 'SavingsPlanCoveredUsage' THEN
          savings_plan_savings_plan_effective_cost
        WHEN line_item_line_item_type = 'DiscountedUsage' THEN
          reservation_effective_cost
        WHEN line_item_line_item_type = 'Usage' THEN
          line_item_unblended_cost
        ELSE 0 END) as amortized_cost, 
    round(sum(line_item_usage_amount), 2) usage_quantity

  FROM ${table_name}
  WHERE 
    year = '2020' AND (month BETWEEN '7' AND '9' OR month BETWEEN '07' AND '09')
    AND ( (line_item_product_code = 'AmazonEC2')
          AND (product_servicecode <> 'AWSDataTransfer')
          AND (line_item_operation LIKE '%RunInstances%')
          AND (line_item_usage_type NOT LIKE '%DataXfer%') 
        )
    AND (
          (line_item_line_item_type = 'Usage')
          OR (line_item_line_item_type = 'SavingsPlanCoveredUsage')
          OR (line_item_line_item_type = 'DiscountedUsage')
        )
  GROUP BY  
    year, 
    month,
    bill_billing_period_start_date,  
    product_instance_type,
    date_trunc('hour', line_item_usage_start_date),
    bill_payer_account_id,
    line_item_usage_account_id,
    7,
    8
  ORDER BY 
    usage_quantity DESC

Back to Table of Contents

EC2 Effective Savings Plans

Query Description

This query will provide EC2 consumption of Savings Plans across Compute resources by linked accounts. It also provides you with the savings received from these Savings Plans and which Savings Plans its connected to. The output is ordered by date.

Pricing

Please refer to the EC2 pricing page .

Sample Output

Images/ec2speffective.png

Download SQL File

Link to Code

Copy Query

    SELECT
    bill_payer_account_id,
    line_item_usage_account_id,
    DATE_FORMAT(("line_item_usage_start_date"),'%Y-%m-%d') AS day_line_item_usage_start_date, 
    SPLIT_PART(savings_plan_savings_plan_a_r_n, '/', 2) AS savings_plan_savings_plan_a_r_n,
    CASE
      savings_plan_offering_type
      WHEN 'EC2InstanceSavingsPlans' THEN 'EC2 Instance Savings Plans'
      WHEN 'ComputeSavingsPlans' THEN 'Compute Savings Plans'
      ELSE savings_plan_offering_type
    END AS "Type",
    savings_plan_region,
    CASE 
      WHEN product_product_name = 'Amazon EC2 Container Service' THEN 'Fargate'
      WHEN product_product_name = 'AWS Lambda' THEN 'Lambda'
      ELSE product_instance_type_family 
    END AS "Instance Type Family",
    SUM (TRY_CAST(line_item_unblended_cost as decimal(16, 8))) as "On Demand Cost",
    SUM(TRY_CAST(savings_plan_savings_plan_effective_cost AS decimal(16, 8))) as "Effective Cost",
    SUM(CAST(line_item_unblended_cost AS decimal(16,8))) AS sum_line_item_unblended_cost, 
    savings_plan_end_time
    FROM
    ${table_name}
  WHERE
    year = '2020' AND (month BETWEEN '7' AND '9' OR month BETWEEN '07' AND '09')
    AND savings_plan_savings_plan_a_r_n <> ''
    AND line_item_line_item_type = 'SavingsPlanCoveredUsage'
  GROUP by
          bill_payer_account_id,
        line_item_usage_account_id,
        DATE_FORMAT(("line_item_usage_start_date"),'%Y-%m-%d'),
    savings_plan_savings_plan_a_r_n,
    savings_plan_offering_type,
    savings_plan_region,
    product_instance_type_family,
    product_product_name, 
    savings_plan_end_time
  ORDER BY
    day_line_item_usage_start_date;

Back to Table of Contents

Compute with Savings Plans

Query Description

This query will provide details about Compute usage that is covered by Savings Plans. The output will include detailed information about the usage type, usage amount, Savings Plans ARN, line item description, and Savings Plans effective savings as compared to On-Demand pricing. The public pricing on-demand cost will be summed and in descending order.

Pricing

Please refer to the Savings Plans pricing page .

Sample Output

Images/compute_sp.png

Download SQL File

Link to Code

Copy Query

SELECT  
  bill_payer_account_id,
  bill_billing_period_start_date,
  line_item_usage_account_id,
  DATE_FORMAT(line_item_usage_start_date,'%Y-%m') AS month_line_item_usage_start_date,
  savings_plan_savings_plan_a_r_n,
  line_item_product_code,
  line_item_usage_type,
  sum(line_item_usage_amount) sum_line_item_usage_amount,
  line_item_line_item_description,
  pricing_public_on_demand_rate,
  sum(pricing_public_on_demand_cost) AS sum_pricing_public_on_demand_cost,
  savings_plan_savings_plan_rate,
  sum(savings_plan_savings_plan_effective_cost) AS sum_savings_plan_savings_plan_effective_cost
FROM ${table_name}
WHERE
  year = '2020' AND (month BETWEEN '7' AND '9' OR month BETWEEN '07' AND '09')
  AND line_item_line_item_type LIKE 'SavingsPlanCoveredUsage'
GROUP BY  
  bill_payer_account_id, 
  bill_billing_period_start_date, 
  line_item_usage_account_id, 
  DATE_FORMAT(line_item_usage_start_date,'%Y-%m'),
  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
ORDER BY
  sum_pricing_public_on_demand_cost DESC

Back to Table of Contents

Account Spend of Shared Savings Plan

Query Description

This query focuses on surfacing accounts which have utilized AWS Savings Plans for which they are not a buyer.

Pricing

Please refer to the Savings Plans pricing page .

Sample Output

Images/compute_sp.png

Download SQL File

Link to Code

Copy Query

SELECT year,
month,
bill_payer_account_id,
line_item_usage_account_id,
savings_plan_offering_type,
line_item_resource_id,
SUM(CAST(line_item_unblended_cost AS decimal(16, 8))) AS sum_line_item_unblended_cost,
SUM(CAST(savings_plan_savings_plan_effective_cost AS decimal(16, 8))) AS sum_savings_plan_savings_plan_effective_cost
FROM ${table_name}
WHERE
year = '2020'
AND (month BETWEEN '9' AND '12' OR month BETWEEN '09' AND '12')
AND (bill_payer_account_id = '111122223333'
AND line_item_usage_account_id = '444455556666'
AND line_item_line_item_type = 'SavingsPlanCoveredUsage'
AND savings_plan_savings_plan_a_r_n NOT LIKE '%444455556666%')
GROUP BY
year,
month,
line_item_resource_id,
line_item_usage_account_id,
bill_payer_account_id,
savings_plan_offering_type
ORDER BY sum_savings_plan_savings_plan_effective_cost DESC;

Back to Table of Contents

Lambda

Query Description

This query focuses on Lambda and the breakdown of its costs by different usage element. Split by Resource IDs you can view the usage, unblended costs and amortized cost broken down by different pricing plans. These results will be ordered by date and costs.

Pricing

Please refer to the Lambda pricing page .

Sample Output

Images/lambda_sp.png

Download SQL File

Link to Code

Copy Query

    SELECT *
      FROM
      (
        (  
          SELECT
              bill_payer_account_id,
              line_item_usage_account_id, 
              line_item_line_item_type,
              DATE_FORMAT((line_item_usage_start_date),'%Y-%m-%d') AS day_line_item_usage_start_date,
              product_region,
              CASE
                  WHEN line_item_usage_type LIKE '%%Lambda-Edge-GB-Second%%' THEN 'Lambda EDGE GB x Sec.'
                  WHEN line_item_usage_type LIKE '%%Lambda-Edge-Request%%' THEN 'Lambda EDGE Requests'
                  WHEN line_item_usage_type LIKE '%%Lambda-GB-Second%%' THEN 'Lambda GB x Sec.'
                  WHEN line_item_usage_type LIKE '%%Request%%' THEN 'Lambda Requests'
                  WHEN line_item_usage_type LIKE '%%In-Bytes%%' THEN 'Data Transfer (IN)'
                  WHEN line_item_usage_type LIKE '%%Out-Bytes%%' THEN 'Data Transfer (Out)'
                  WHEN line_item_usage_type LIKE '%%Regional-Bytes%%' THEN 'Data Transfer (Regional)'
                  ELSE 'Other'
              END as UsageType,
              line_item_resource_id,
              pricing_term,
              SUM(CAST(line_item_usage_amount AS double)) AS sum_line_item_usage_amount,
              SUM(CAST(line_item_unblended_cost AS decimal(16,8))) AS sum_line_item_unblended_cost, 
              sum(CASE
          WHEN ("line_item_line_item_type" = 'SavingsPlanCoveredUsage') THEN
          "savings_plan_savings_plan_effective_cost"
          WHEN ("line_item_line_item_type" = 'SavingsPlanRecurringFee') THEN
          ("savings_plan_total_commitment_to_date" - "savings_plan_used_commitment")
          WHEN ("line_item_line_item_type" = 'SavingsPlanNegation') THEN
          0
          WHEN ("line_item_line_item_type" = 'SavingsPlanUpfrontFee') THEN
          0
          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) "amortized_cost"
          FROM ${table_name}
            WHERE year = '2020' AND (month BETWEEN '7' AND '9' OR month BETWEEN '07' AND '09')
            AND product_product_name = 'AWS Lambda'
            AND line_item_line_item_type like '%%Usage%%'
            AND product_product_family IN ('Data Transfer', 'Serverless')
            AND line_item_line_item_type  in ('DiscountedUsage', 'Usage', 'SavingsPlanCoveredUsage')
          GROUP BY
          bill_payer_account_id,
            line_item_usage_account_id,
            DATE_FORMAT((line_item_usage_start_date),'%Y-%m-%d'),
            product_region,
            line_item_usage_type,
            line_item_resource_id,
            pricing_term,
            line_item_line_item_type
          ORDER BY 
            day_line_item_usage_start_date,
            sum_line_item_usage_amount,
            sum_line_item_unblended_cost
        )

        UNION

        (
          SELECT
            bill_payer_account_id,
              line_item_usage_account_id,
          line_item_line_item_type,
              DATE_FORMAT((line_item_usage_start_date),'%Y-%m-%d') AS day_line_item_usage_start_date,
              product_region AS Region,
              CASE
                  WHEN line_item_usage_type LIKE '%%Lambda-Edge-GB-Second%%' THEN 'Lambda EDGE GB x Sec.'
                  WHEN line_item_usage_type LIKE '%%Lambda-Edge-Request%%' THEN 'Lambda EDGE Requests'
                  WHEN line_item_usage_type LIKE '%%Lambda-GB-Second%%' THEN 'Lambda GB x Sec.'
                  WHEN line_item_usage_type LIKE '%%Request%%' THEN 'Lambda Requests'
                  WHEN line_item_usage_type LIKE '%%In-Bytes%%' THEN 'Data Transfer (IN)'
                  WHEN line_item_usage_type LIKE '%%Out-Bytes%%' THEN 'Data Transfer (Out)'
                  WHEN line_item_usage_type LIKE '%%Regional-Bytes%%' THEN 'Data Transfer (Regional)'
                  ELSE 'Other'
              END as UsageType,
              line_item_resource_id,
              CASE savings_plan_offering_type 
                  WHEN 'ComputeSavingsPlans' THEN 'Compute Savings Plans'
                  ELSE savings_plan_offering_type
              END AS ChargeType,
              SUM(CAST(line_item_usage_amount AS double)) AS sum_line_item_usage_amount,
              SUM(CAST(savings_plan_savings_plan_effective_cost AS decimal(16,8))) AS sum_savings_plan_savings_plan_effective_cost,
          sum(CASE
          WHEN ("line_item_line_item_type" = 'SavingsPlanCoveredUsage') THEN
          "savings_plan_savings_plan_effective_cost"
          WHEN ("line_item_line_item_type" = 'SavingsPlanRecurringFee') THEN
          ("savings_plan_total_commitment_to_date" - "savings_plan_used_commitment")
          WHEN ("line_item_line_item_type" = 'SavingsPlanNegation') THEN
          0
          WHEN ("line_item_line_item_type" = 'SavingsPlanUpfrontFee') THEN
          0
          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) "amortized_cost"
          
            FROM ${table_name}
            WHERE year = '2020' AND (month BETWEEN '7' AND '9' OR month BETWEEN '07' AND '09')
            AND product_product_name = 'AWS Lambda'
            AND product_product_family IN ('Data Transfer', 'Serverless')
            AND line_item_line_item_type  in ('DiscountedUsage', 'Usage', 'SavingsPlanCoveredUsage')
          GROUP BY
          bill_payer_account_id,
            line_item_usage_account_id,
            DATE_FORMAT((line_item_usage_start_date),'%Y-%m-%d'),
            product_region,
            line_item_usage_type,
            line_item_resource_id,
            savings_plan_offering_type, 
            line_item_line_item_type
          ORDER BY  
            day_line_item_usage_start_date ASC,
            sum_line_item_usage_amount DESC
        )
      ) AS aggregatedTable

      ORDER BY
        day_line_item_usage_start_date,
        sum_line_item_usage_amount,
        sum_line_item_unblended_cost;

Back to Table of Contents

Elastic Load Balancing

Query Description

This query will display cost and usage of Elastic Load Balancers which didn’t receive any traffic last month and ran for more than 336 hours (14 days). Resources returned by this query could be considered for deletion.

Pricing

Please refer to the Elastic Load Balancing pricing page .

Sample Output

Images/elb_unused_wrid.png

Download SQL File

Link to Code

Copy Query

SELECT
  bill_payer_account_id,
  line_item_usage_account_id,
  SPLIT_PART(line_item_resource_id, ':', 6) 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

Back to Table of Contents

EC2 Savings Plans Inventory

Query Description

This query will provide an inventory for EC2 Savings Plans. It will show useful information about the Savings Plans purchased including ID (ARN), type, term length, commitment (used, hourly, etc…), and utilization. Cost Explorer can also provide this information in the Inventory and Utilization reports however, this combines elements from both into a single report.

Pricing

Please refer to the Savings Plans pricing page .

Savings Plans Utilization Report

Savings Plans Inventory Report

Sample Output

Images/ec2_sp_inventory.png

Download SQL File

Link to Code

Copy Query

SELECT
  SPLIT_PART(savings_plan_savings_plan_a_r_n, '/', 2) AS split_savings_plan_savings_plan_a_r_n,
  bill_payer_account_id,
  line_item_usage_account_id,
  DATE_FORMAT((line_item_usage_start_date),'%Y-%m') AS month_line_item_usage_start_date,
  CASE
    savings_plan_offering_type
    WHEN 'EC2InstanceSavingsPlans' THEN 'EC2 Instance Savings Plans'
    WHEN 'ComputeSavingsPlans' THEN 'Compute Savings Plans'
    ELSE savings_plan_offering_type
  END AS "Type",
  savings_plan_region,
  DATE_FORMAT(
    from_iso8601_timestamp(savings_plan_start_time),
    '%Y-%m-%d'
  ) AS "Start Date",
  DATE_FORMAT(
    from_iso8601_timestamp(savings_plan_end_time),
    '%Y-%m-%d'
  ) AS "End Date",
  savings_plan_payment_option AS "savings_plan_payment_option",
  savings_plan_purchase_term AS "savings_plan_purchase_term",
  SUM(
    TRY_CAST(
      savings_plan_recurring_commitment_for_billing_period AS decimal(16, 8)
    )
  ) AS "Recurring commitment for billing period / Monthly Fee",
  SUM(
    TRY_CAST(
      savings_plan_total_commitment_to_date AS decimal(16, 8)
    )
  ) AS "Total Commit to Date",
  SUM(
    TRY_CAST(
      savings_plan_used_commitment AS decimal(16, 8)
    )
  ) AS "Used Commitment",
  avg(
    case
      when line_item_line_item_type = 'SavingsPlanRecurringFee' then TRY_CAST(
        savings_plan_total_commitment_to_date as decimal(8, 2)
      )
    end
  ) as "Hourly Commitment",
  TRY_CAST(
    (
      (
        SUM(
          TRY_CAST(
            savings_plan_used_commitment AS decimal(16, 8)
          )
        ) / SUM(
          TRY_CAST(
            savings_plan_total_commitment_to_date AS decimal(16, 8)
          )
        )
      ) * 100
    ) AS decimal(3, 0)
  ) AS "Utilization (%)"
FROM
  ${table}
WHERE
year = '2020' AND (month BETWEEN '7' AND '9' OR month BETWEEN '07' AND '09')
  AND savings_plan_savings_plan_a_r_n <> ''
  AND line_item_line_item_type = 'SavingsPlanRecurringFee'
GROUP BY
  savings_plan_savings_plan_a_r_n,
  bill_payer_account_id,
  line_item_usage_account_id,
  4,
  savings_plan_offering_type,
  savings_plan_region,
  savings_plan_start_time,
  savings_plan_end_time,
  savings_plan_payment_option,
  savings_plan_purchase_term
ORDER BY
  split_savings_plan_savings_plan_a_r_n,
  month_line_item_usage_start_date;

Back to Table of Contents

EC2 Reserved Instance Coverage

Query Description

The Reserved Instance Utilization and Coverage reports are available out-of-the-box in AWS Cost Explorer. This query provides coverage for EC2 Reserved Instances. It shows useful information about the Reserved Instances purchased including Lease ID, instance type and family, used and unused amounts, and On-Demand usage that could be covered by additional Savings Plans if this is your preferred savings method.

Cost and Usage columns are dynamic and their visibility in the Athena tables depends on usage. This query will only work if you have reserved instance usage in the table/view you are querying. If you do not have usage you will receive an error that reservation_reservation_a_r_n is an invalid column name.

Pricing

Please refer to the EC2 reserved instances pricing page .

Sample Output

Sample output includes a subset of query columns Images/ec2ricoverage.png

Download SQL File

Link to Code

Copy Query

SELECT
  bill_payer_account_id,
  line_item_usage_account_id,
  DATE_FORMAT((line_item_usage_start_date),'%Y-%m-%d') AS day_line_item_usage_start_date,
  CASE
    WHEN line_item_line_item_type IN ('Usage') THEN 'OnDemand'
    WHEN line_item_line_item_type IN ('Fee','RIFee','DiscountedUsage') THEN 'ReservedInstance'
  END AS ReservationType,
  SPLIT_PART(SPLIT_PART(reservation_reservation_a_r_n,':',6),'/',2) AS LeaseID,
  SPLIT_PART(line_item_usage_type ,':',2) AS InstanceType,
  SPLIT_PART(SPLIT_PART(line_item_usage_type ,':',2), '.', 1) AS InstanceFamily,
  CASE product_region
    WHEN NULL THEN 'Global'
    WHEN '' THEN 'Global'
    ELSE product_region
  END as Region,
  line_item_line_item_type as UsageType,
  SUM(TRY_CAST(line_item_usage_amount AS double)) AS sum_line_item_usage_amount,
  SUM(TRY_CAST(reservation_unused_quantity AS double)) AS sum_reservation_unused_quantity,
  SUM(TRY_CAST(line_item_normalized_usage_amount AS double)) AS sum_line_item_normalized_usage_amount,
  SUM(TRY_CAST(reservation_unused_normalized_unit_quantity AS double)) AS sum_reservation_unused_normalized_unit_quantity,
  SUM(CAST(reservation_effective_cost AS decimal(16,8))) AS sum_line_item_blended_cost,
  SUM(CAST(line_item_unblended_cost AS decimal(16,8))) AS sum_line_item_unblended_cost
FROM
  ${table_name}
WHERE
  year = '2020' AND (month BETWEEN '7' AND '9' OR month BETWEEN '07' AND '09')
  AND product_product_name = 'Amazon Elastic Compute Cloud'
  AND line_item_operation LIKE '%%RunInstance%%'
  AND line_item_line_item_type IN ('Usage','Fee','RIFee','DiscountedUsage')
  AND product_product_family NOT IN ('Data Transfer')
GROUP BY
  bill_payer_account_id,
  line_item_usage_account_id,
  3,
  5,
  6,
  product_region,
  line_item_line_item_type
ORDER BY
  day_line_item_usage_start_date,
  InstanceType,
  sum_line_item_unblended_cost DESC;

Back to Table of Contents