Attempt 2 for a nicer query of the Notify user survey 2022

notify-user-survey-2022-attempt2.sql

select
  users.id,
  users.name,
  users.email_address,
  users.logged_in_at as last_login,
  count_of_logins.num_of_logins,
  count(distinct services.id) as num_live_services_member_of,
  count(distinct organisation.id) as num_of_orgs_member_of,
  count(distinct organisation.organisation_type) as num_of_org_types_member_of,
  count(CASE WHEN permissions.permission = 'manage_settings' THEN 1 END) as num_of_live_services_manager_of,
  most_used_service.service_name,
  most_used_service.org_name,
  most_used_service.org_type
from users
join user_to_service on user_to_service.user_id = users.id
join services on user_to_service.service_id = services.id
join organisation on services.organisation_id = organisation.id
left outer join permissions on (services.id = permissions.service_id and users.id = permissions.user_id)
join (
  select (data ->> 'user_id')::uuid as user_id, count(*) as num_of_logins
  from events where event_type = 'sucessful_login'
  and created_at > '2021-06-24'
  group by 1
  ) as count_of_logins on users.id = count_of_logins.user_id
join (
  SELECT
  distinct users.id,
  FIRST_VALUE(service_name) OVER (PARTITION BY users.id ORDER BY service_usage.num_sent DESC) as service_name,
  FIRST_VALUE(organisation_name) OVER (PARTITION BY users.id ORDER BY service_usage.num_sent DESC) as org_name,
  FIRST_VALUE(organisation_type) OVER (PARTITION BY users.id ORDER BY service_usage.num_sent DESC) as org_type
  FROM users
  join user_to_service on user_id = users.id
  join (
      select
      services.id,
      services.name as service_name,
      organisation.name as organisation_name,
      organisation.organisation_type,
      sum(notifications_sent) as num_sent
      from ft_billing
      join services on services.id = ft_billing.service_id
      join organisation on services.organisation_id = organisation.id
      WHERE services.active = true
      and services.restricted = false
      and services.count_as_live = true
      and bst_date > '2021-06-27'
      group by 1, 2, 3, 4
  ) as service_usage on user_to_service.service_id = service_usage.id
) as most_used_service on users.id = most_used_service.id
WHERE services.active = true
and services.restricted = false
and services.count_as_live = true
and users.state = 'active'
and users.platform_admin = false
and users.logged_in_at > '2021-06-27'
group by 1,2,3,4,5,10,11,12
order by 3;