Detect & correct Compute virtual machines with low utilization

with compute_virtual_machine_utilization as (
  select
    name,
    max(average) as avg_max,
    count(average) days
  from
    azure_compute_virtual_machine_metric_cpu_utilization_daily
  where
    date_part('day', now() - timestamp) <= 30
  group by
    name
  having
    max(average) < 40
),
compute_virtual_machine_current as (
  select
    i.title,
    i.id,
    i.name,
    i.region,
    i.size,
    i.resource_group,
    i.subscription_id,
    i.sp_connection_name as conn,
    i.size as instance_size,
    case
      when security_profile ->> 'securityType' = 'TrustedLaunch' then 'trusted_launch'
      else 'not_trusted_launch'
    end as trusted_launch_config,
    split_part(i.size, '_', 1) as tier
  from
    compute_virtual_machine_utilization u
    left join azure_compute_virtual_machine i on u.name = i.name
),
distinct_families as (
  select
    distinct instance_size,
    tier,
    region,
    trusted_launch_config
  from
    compute_virtual_machine_current
),
capability_values as (
  select
    instance_size,
    region,
    name,
    tier,
    capability ->> 'name' as capability_name,
    capability ->> 'value' as capability_value
  from
    (
      select
        f.instance_size,
        f.region,
        s.name,
        f.tier,
        jsonb_array_elements(capabilities) as capability
      from
        distinct_families f
        join azure_compute_resource_sku s on s.name like f.tier || '_%'
        and s.resource_type = 'virtualMachines'
        cross join jsonb_array_elements_text(s.locations) as l
      where
        l = f.region
    ) as capability_expanded
),
family_details as (
  select
    instance_size,
    name,
    tier,
    region,
    MAX(
      case
        when capability_name = 'vCPUs' then capability_value :: int
        else null
      end
    ) as vcpus,
    MAX(
      case
        when capability_name = 'MemoryGB' then capability_value :: float
        else null
      end
    ) as memorygb,
    case
      when capability_name = 'HyperVGenerations' then capability_value
      else null
    end as hyper_v_generations,
    case
      when capability_name = 'TrustedLaunchDisabled' then capability_value :: bool
      else null
    end as trusted_launch_disabled
  from
    capability_values
  group by
    instance_size,
    name,
    region,
    tier,
    hyper_v_generations,
    trusted_launch_disabled
),
ranked_families as (
  select
    instance_size,
    name,
    region,
    tier,
    hyper_v_generations,
    trusted_launch_disabled,
    RANK() over (
      partition by instance_size
      order by
        vcpus asc,
        memorygb asc
    ) as weight
  from
    family_details
)
select
  concat(
    id,
    ' (',
    title,
    ') [',
    size,
    '/',
    region,
    '/',
    resource_group,
    ']'
  ) as title,
  id,
  name as vm_name,
  size as current_type,
  resource_group,
  subscription_id,
  coalesce(
    (
      select
        name
      from
        ranked_families fd
      where
        fd.tier = c.tier
        and fd.weight < (
          select
            weight
          from
            ranked_families
          where
            name = c.size
          limit
            1
        )
      order by
        fd.weight desc
      limit
        1
    ), ''
  ) as suggested_type,
  region,
  conn
from
  compute_virtual_machine_current c;