latest_or_versioned_images.sql

select count(distinct(org_id)) as total_org_count
      -- ,sum(only_latest) as only_latest_count
      ,CAST((sum(only_latest) * 100.0 / count(distinct(org_id))) AS numeric(10,2)) as only_latest_percent_of_orgs
      -- ,sum(only_versioned) as only_versioned_count
      ,CAST((sum(only_versioned) * 100.0 / count(distinct(org_id))) AS numeric(10,2)) as only_versioned_percent_of_orgs
      -- ,sum(both_latest_and_versioned) as both_latest_and_versioned_count
      ,CAST((sum(both_latest_and_versioned) * 100.0 / count(distinct(org_id))) AS numeric(10,2)) as both_latest_and_versioned_percent_of_orgs
      -- ,sum(neither) as neither_count
      ,CAST((sum(neither) * 100.0 / count(distinct(org_id))) AS numeric(10,2)) as neither_percent_of_orgs

      
from (
    select org_id
           , case when latest = 0 and (sha + short_sha + versioned) > 0 then 1 else 0 end as only_versioned
           , case when latest = 1 and (sha + short_sha + versioned) = 0 then 1 else 0 end as only_latest
           , case when latest = 1 and (sha + short_sha + versioned) > 0 then 1 else 0 end as both_latest_and_versioned
           , case when latest = 0 and (sha + short_sha + versioned) = 0 then 1 else 0 end as neither
    from ( select dh.org_id as org_id
      , max(CASE when image_names ilike '%:latest%'  THEN 1 ELSE 0 END) as latest
      , max(CASE when image_names ~ '[0-9a-f]{40}'  THEN 1 ELSE 0 END) as sha 
      -- sha codes have 40 digits https://en.wikipedia.org/wiki/SHA-1
      -- i.e. quay.io/datadog/bill-usage:master-4a38f171f23a1f02d008e3600fd41f3963bf0996,quay.io/datadog/nginx-bill-usage:master-
      , max(CASE when image_names ~ 'v{0,1}[0-9]+[.][0-9]+[.]{0,1}[0-9]' THEN 1 ELSE 0 END) as versioned 
      -- i.e. fluentd:v0.5.4
      , max(CASE when image_names ~ ':[0-9a-f]{7}'  THEN 1 ELSE 0 END) as short_sha 
      -- i.e. bitbucket-billing-worker:120cfa0-release or fluentd:0.5.4
      from datamart.dim_host dh
      join datamart.fact_host_integration fhi on fhi.host_id = dh.id
/*       where location = 'AWS' */
      where image_names != ''
      group by dh.org_id
      )
    )