turbot/aws_insights

Query: sns_topic_monthly_forecast_table

Usage

powerpipe query aws_insights.query.sns_topic_monthly_forecast_table

SQL

with monthly_costs as (
select
period_start,
period_end,
case
when date_trunc('month', period_start) = date_trunc('month', CURRENT_DATE::timestamp) then 'Month to Date'
when date_trunc('month', period_start) = date_trunc('month', CURRENT_DATE::timestamp - interval '1 month') then 'Previous Month'
else to_char (period_start, 'Month')
end as period_label,
period_end::date - period_start::date as days,
sum(unblended_cost_amount)::numeric::money as unblended_cost_amount,
(sum(unblended_cost_amount) / (period_end::date - period_start::date ) )::numeric::money as average_daily_cost,
date_part('days', date_trunc ('month', period_start) + '1 MONTH'::interval - '1 DAY'::interval ) as days_in_month,
sum(unblended_cost_amount) / (period_end::date - period_start::date ) * date_part('days', date_trunc ('month', period_start) + '1 MONTH'::interval - '1 DAY'::interval )::numeric::money as forecast_amount
from
aws_cost_by_service_usage_type_monthly as c
where
service = 'Amazon Simple Notification Service'
and date_trunc('month', period_start) >= date_trunc('month', CURRENT_DATE::timestamp - interval '1 month')
group by
period_start,
period_end
)
select
period_label as "Period",
unblended_cost_amount as "Cost",
average_daily_cost as "Daily Avg Cost"
from
monthly_costs
union all
select
'This Month (Forecast)' as "Period",
(select forecast_amount from monthly_costs where period_label = 'Month to Date') as "Cost",
(select average_daily_cost from monthly_costs where period_label = 'Month to Date') as "Daily Avg Cost";

Dashboards

The query is used in the dashboards: