technology_counts_by_integration_type.sql

select integration_type, count(distinct(org_id)) as total_org_count
      ,sum(sha256) as sha256
      ,CAST((sum(sha256) * 100.0 / count(distinct(org_id))) AS numeric(10,2)) as sha256_percent_of_orgs
      -- ,sum(amazon_ecs_agent) as amazon_ecs_agent
      ,CAST((sum(amazon_ecs_agent) * 100.0 / count(distinct(org_id))) AS numeric(10,2)) as amazon_ecs_agent_percent_of_orgs
      -- ,sum(postgres) as postgres
      ,CAST((sum(postgres) * 100 / count(distinct(org_id))) AS numeric(10,2)) as postgres_ecs_agent_percent_of_orgs
      -- ,sum(mongo) as mongo
      ,CAST((sum(mongo) * 100 / count(distinct(org_id))) AS numeric(10,2)) as mongo_ecs_agent_percent_of_orgs
      -- ,sum(mysql) as mysql
      ,CAST((sum(mysql) * 100 / count(distinct(org_id))) AS numeric(10,2)) as mysql_percent_of_orgs
      -- ,sum(nginx) as nginx
      ,CAST((sum(nginx) * 100 / count(distinct(org_id))) AS numeric(10,2)) as nginx_percent_of_orgs
      -- ,sum(elasticsearch) as elasticsearch
      ,CAST((sum(elasticsearch) * 100 / count(distinct(org_id))) AS numeric(10,2)) as elasticsearch_percent_of_orgs
      -- ,sum(heapster) as heapster
      ,CAST((sum(heapster) * 100 / count(distinct(org_id))) AS numeric(10,2)) as heapster_percent_of_orgs
      -- ,sum(ubuntu) as ubuntu
      ,CAST((sum(ubuntu) * 100 / count(distinct(org_id))) AS numeric(10,2)) as ubuntu_percent_of_orgs
      -- ,sum(rabbitmq) as rabbitmq
      ,CAST((sum(rabbitmq) * 100 / count(distinct(org_id))) AS numeric(10,2)) as rabbitmq_percent_of_orgs
      -- ,sum(cadvisor) as cadvisor
      ,CAST((sum(cadvisor) * 100 / count(distinct(org_id))) AS numeric(10,2)) as cadvisor_percent_of_orgs
      -- ,sum(metadata) as metadata
      ,CAST((sum(metadata) * 100 / count(distinct(org_id))) AS numeric(10,2)) as metadata_percent_of_orgs
      -- ,sum(dns) as dns
      ,CAST((sum(dns) * 100 / count(distinct(org_id))) AS numeric(10,2)) as dns_percent_of_orgs
      -- ,sum(healthcheck) as healthcheck
      ,CAST((sum(healthcheck) * 100 / count(distinct(org_id))) AS numeric(10,2)) as healthcheck_percent_of_orgs
      
      
    from ( select integration_type, dh.org_id as org_id
      , max(CASE when image_names ilike '%sha256%'  THEN 1 ELSE 0 END) as sha256
      , max(CASE when image_names ilike '%amazon-ecs-agent%'  THEN 1 ELSE 0 END) as amazon_ecs_agent
      , max(CASE when image_names ilike '%postgres%'  THEN 1 ELSE 0 END) as postgres
      , max(CASE when image_names ilike '%mongo%'  THEN 1 ELSE 0 END) as mongo
      , max(CASE when image_names ilike '%mysql%'  THEN 1 ELSE 0 END) as mysql
      , max(CASE when image_names ilike '%nginx%'  THEN 1 ELSE 0 END) as nginx
      , max(CASE when image_names ilike '%elasticsearch%'  THEN 1 ELSE 0 END) as elasticsearch
      , max(CASE when image_names ilike '%heapster%'  THEN 1 ELSE 0 END) as heapster
      , max(CASE when image_names ilike '%ubuntu%'  THEN 1 ELSE 0 END) as ubuntu
      , max(CASE when image_names ilike '%rabbitmq%'  THEN 1 ELSE 0 END) as rabbitmq
      , max(CASE when image_names ilike '%cadvisor%'  THEN 1 ELSE 0 END) as cadvisor
      , max(CASE when image_names ilike '%metadata%'  THEN 1 ELSE 0 END) as metadata
      , max(CASE when image_names ilike '%dns%'  THEN 1 ELSE 0 END) as dns
      , max(CASE when image_names ilike '%healthcheck%'  THEN 1 ELSE 0 END) as healthcheck
      from datamart.dim_host dh
      join datamart.fact_host_integration fhi on fhi.host_id = dh.id
      where image_names != ''
      group by integration_type, dh.org_id
      )
      group by 1