Query: compute_instances_by_cpu_utilization_category

with compute_buckets as (
  select
unnest(array ['Unused (<1%)','Underutilized (1-10%)','Right-sized (10-90%)', 'Overutilized (>90%)' ]) as compute_bucket
),
max_averages as (
  select
    id,
    case
      when max(average) <= 1 then 'Unused (<1%)'
      when max(average) between 1 and 10 then 'Underutilized (1-10%)'
      when max(average) between 10 and 90 then 'Right-sized (10-90%)'
      when max(average) > 90 then 'Overutilized (>90%)'
    end as compute_bucket,
    max(average) as max_avg
  from
    oci_core_instance_metric_cpu_utilization_daily
  where
    date_part('day', now() - timestamp) <= 30
  group by
    id
)
select
  b.compute_bucket as "CPU Utilization",
  count(a.*)
from
  compute_buckets as b
left join max_averages as a on b.compute_bucket = a.compute_bucket
group by
  b.compute_bucket