Notify user survey 2022

notify-user-survey-2022.sql

-- We are aware that this query can return duplicate users (ie you would send an email twice to the same person).
-- This is if they have two services that have sent the same number of notifications. 
-- They should be manually removed from the data before sending

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,
  sum(CASE WHEN permissions.permission = 'manage_settings' THEN 1 END) as num_of_live_services_manager_of,
  most_used_service.service_name
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)

-- COMMENT: This join is the number of successful logins per user 
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

-- COMMENT: This big join is the name of the service most used by each user
-- where we only include services that are live and active and we measure
-- 'most used' as the number of notifications sent in the ft_billing table 
join (
  select
    users.id as user_id,
    notifications_sent_per_service.service_name,
    notifications_sent_per_service.num_notifications_sent_per_service
  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
    services.id as service_id,
    services.name as service_name,
    sum(ft_billing.notifications_sent) as num_notifications_sent_per_service
    from ft_billing
    join services on ft_billing.service_id = services.id
    where bst_date > '2021-06-27'
    group by 1, 2
    order by 3 desc
    ) as notifications_sent_per_service on notifications_sent_per_service.service_id = services.id
  join (
    select
      users.id as user_id,
      max(num_notifications_sent_per_service) as num_notifications_sent_per_service
    from users
    join user_to_service on user_to_service.user_id = users.id
    join services on user_to_service.service_id = services.id
    left outer join permissions on (services.id = permissions.service_id and users.id = permissions.user_id)
    join (
      select services.id as service_id, services.name as service_name, sum(ft_billing.notifications_sent) as num_notifications_sent_per_service
      from ft_billing
      join services on ft_billing.service_id = services.id
      where bst_date > '2021-06-27'
      group by 1, 2
      order by 3 desc
      ) as notifications_sent_per_service on notifications_sent_per_service.service_id = services.id
    WHERE services.active = true
    and services.restricted = false
    and services.count_as_live = true
    group by 1
    order by 1 desc
  ) as most_used_service on users.id = most_used_service.user_id and most_used_service.num_notifications_sent_per_service =   notifications_sent_per_service.num_notifications_sent_per_service
  WHERE services.active = true
  and services.restricted = false
  and services.count_as_live = true
  group by 1,2,3
) as most_used_service on most_used_service.user_id = users.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
order by 3;