Useful Notify reports

all organisations.sql

select
  name,
  organisation_type,
  crown,
  (
    select
      string_agg(domain.domain::text, ', ')
    from
      domain 
    where
      domain.organisation_id = organisation.id
  ) as domains
from
  organisation
join
  domain on organisation.id = domain.organisation_id
order by
  1 asc
;

Biggest teams.sql

select 
  count(*),
  services.name,
  'https://www.notifications.service.gov.uk/services/' || services.id
from 
  user_to_service
join 
  services on service_id = services.id
group by
  services.name, services.id
order by 
  1 desc
;

combinations of permissions.sql

select
  (
    select
      string_agg(
        permissions.permission::text,
        ','
        order by permissions.permission
      )
    from
      permissions
    where 
      permissions.user_id = users.id and
      permissions.service_id = services.id
  ) as permission_set, count(*)
from 
  user_to_service 
join 
  services on user_to_service.service_id = services.id
join 
  users on user_to_service.user_id = users.id  
where 
  services.restricted = false and 
  users.platform_admin = false
group by
  permission_set
order by
  count desc
;

delivery time for 'verify reply-to address' template.sql

select
  '@' || split_part("to", '@', 2) as "Domain",
  notification_status as "Status",
  age(updated_at, sent_at) as "Delivery time"
from notifications
  where template_id = 'a42f1d17-9404-46d5-a647-d013bdfca3e1'
order by
  "Delivery time" desc
;

group letters by print day.sql

select
  created_at,
  date_trunc(
    'day',
    (created_at at time zone 'Z') at time zone 'Europe/London'
    + interval '6 hours 30 minutes'
  ) as print_day
from
  notifications
where
  notification_type = 'letter'
order by
  1 desc
;

history of live services created_by_id.sql

select
  services_history.id, services_history.name, services_history.updated_at, users.email_address
from
  services_history
join
  users on services_history.created_by_id = users.id
join
  services on services.id = services_history.id
where
  services.restricted = false and 
  services_history.updated_at is not null
order by
  services_history.id desc,
  services_history.updated_at desc
limit
  100;

how many services for email auth users.sql

select
  count_services as number_of_services,
  count(*) as number_of_email_auth_users
  from
    (
      select
        users.id,
        count(user_to_service.service_id) as count_services
      from 
        users
      join
        user_to_service ON user_to_service.user_id = users.id
      join
        services ON services.id = user_to_service.service_id
      where
        users.auth_type = 'email_auth'
        and services.restricted = false
      group by
        users.id  
    ) as user_services
  group by 
    count_services
  order by 
    1 asc
;

jobs with unprocessed rows.sql

select
  count(n.id), j.notification_count, j.service_id, n.job_id
from
  notifications n
join
  jobs j on n.job_id = j.id
where
  j.job_status = 'finished'                                                                                                                                                   group by j.notification_count, j.service_id, n.job_id
having
  count(n.id) != j.notification_count;

letter contact blocks.sql

select
  services.name,
  'https://www.notifications.service.gov.uk/services/' || services.id as Dashboard,
  count(distinct(service_letter_contacts.id)) as "Number of letter contact blocks",
  count(distinct(service_letter_contact_id)) as "Number in use",
  count(distinct case when service_letter_contact_id is null then 1 end) as "Using blank letter contact block"
from
  templates
join
  services on templates.service_id = services.id
join 
  service_letter_contacts on service_letter_contacts.service_id = services.id
where
  template_type = 'letter'
  and templates.archived = false
  and services.active = true
  and service_letter_contacts.archived = false
group by
  1,
  2
order by
  3 desc,
  4 desc
;

live and trial services by user.sql

select 
  'https://www.notifications.service.gov.uk/users/' || users.id as profile,
  (
    select 
      count(*)
    from
      services
    where 
      services.id in (
        select
          service_id
        from
          user_to_service
        where
          user_id = users.id
      )
      and 
      services.restricted = false
  ) as count_of_live_services,
  (
    select 
      count(*)
    from
      services
    where 
      services.id in (
        select
          service_id
        from
          user_to_service
        where
          user_id = users.id
      )
      and 
      services.restricted = true
  ) as count_of_trial_services
from
  users
join
  user_to_service on users.id = user_to_service.user_id
join 
  services on services.id = user_to_service.service_id
where 
  users.platform_admin = false
;

local gov services with big allowance.sql

select
  name, services.id, financial_year_start, free_sms_fragment_limit
from
  services
join
  annual_billing on services.id = annual_billing.service_id
where
  annual_billing.free_sms_fragment_limit > 25000 and organisation_type != 'central'
order by 
  service_id, financial_year_start
;

monthly active live services.sql

-- This query doesn’t run in production, it’s too slow
select
  date_trunc('month', notification_history.created_at),
  count(distinct service_id)
from
  notification_history
join
  services on services.id = service_id
where
  notification_history.created_at >= '2018-06-1'
  and
  services.restricted = false
  and
  key_type = 'normal'
group by 1
order by 1 desc
;

most jobs from contact lists.sql

select
  'https://www.notifications.service.gov.uk/services/' || service_id as Dashboard,
  count(*)
from
  jobs
where
  contact_list_id is not null
group by
  service_id
order by
  count desc
;

Notifications per hour by service.sql

select
  date_trunc('hour', notification_history.created_at),
  count(*) from notification_history
where
  service_id = ''
group by
  1
order by
  1 desc
;

Number of users of live services.sql

select 
  count(distinct(user_id))
from 
  user_to_service
join 
  services on user_to_service.service_id = services.id
join 
  users on user_to_service.user_id = users.id
where 
  services.restricted = false and 
  services.active = true and 
  users.platform_admin = false
;

one off letters.sql

select
  "to", created_by_id::text
from notifications
where notification_type = 'letter' and created_by_id is not null
;

Org service mismatch.sql

select
  services.id as "Service ID",
  services.name as "Service name",
  services.organisation_type as "Service organisation type",
  organisation.organisation_type as "Organisation type",  
  organisation.name as "Organisation name",
  annual_billing.free_sms_fragment_limit as "Allowance",
  annual_billing.financial_year_start as "Financial year"
from 
  services
join
  organisation_to_service on organisation_to_service.service_id = services.id
join
  organisation on organisation_to_service.organisation_id = organisation.id  
join
  annual_billing on annual_billing.service_id = services.id
where
  services.restricted = false
  and services.count_as_live = true
  and services.organisation_type != organisation.organisation_type
order by
  "Organisation name" desc,
  "Financial year" asc
;

recent scheduled jobs.sql

select
  original_file_name, scheduled_for, notification_count, job_status, 'https://www.notifications.service.gov.uk/services/' || service_id || '/jobs/' || id as job_page
from 
  jobs
where
  scheduled_for is not null
order by
  scheduled_for desc
limit
  25
;

rows skipped when processing jobs.sql

SELECT
  row.n from generate_series(0, 49999) as row(n)  # Where 49999 is expected notification count - 1
left outer join
  notifications n on job_row_number = row.n and job_id = ''
where
  n.id is null;

schools using notify.sql

select 
  services.name as "Service name",
  'https://www.notifications.service.gov.uk/services/' || services.id as "Dashboard",
  services.created_at as "Created at",
  services.restricted as "Trial mode",
  services.go_live_at as "Go live at",
  (
    select
      count(*)
    from
      templates 
    where
      templates.service_id = services.id
      and templates.archived = false
  ) as "Number of templates",
  (
    select
      sum(notifications_sent)
    from
      ft_billing
    where
      ft_billing.service_id = services.id
      and ft_billing.notification_type = 'email'
  ) as "Emails sent",
  (
    select
      sum(notifications_sent)
    from
      ft_billing
    where
      ft_billing.service_id = services.id
      and ft_billing.notification_type = 'sms'
  ) as "Text messages sent",  
  (
    select
      sum(notifications_sent)
    from
      ft_billing
    where
      ft_billing.service_id = services.id
      and ft_billing.notification_type = 'letter'
  ) as "Letters sent"  
from
  services 
where
  services.organisation_type = 'school_or_college'
limit 
  100000
;

services sending to fake phone numbers.sql

select
  count(*) as count_of_messages,
  sum(billable_units) as billable_units,
  '£' || round(sum(billable_units) * 0.016, 2)::text as spent,
  'https://www.notifications.service.gov.uk/services/' || service_id as dashboard
from
  notifications
where
  normalised_to ilike '447700900%'
  and key_type != 'test'
group by
  service_id
order by
  spent desc
;

services using email auth.sql

select
  services.name,
  organisation.name
from
  services
join
  service_permissions on services.id = service_permissions.service_id
join
  organisation on services.organisation_id = organisation.id
where
  service_permissions.permission = 'email_auth'
  and services.count_as_live = true
  and services.restricted = false
  and services.active = true
order by
  2, 1
;

services with basic view.sql

select 
  now(),
  services.name,
  'https://www.notifications.service.gov.uk/services/' || services.id
from 
  service_permissions
join 
  services on service_id = services.id
where 
  permission = 'caseworking'
;

Services with folders.sql

select
  count(*) as "Number of folders",
  services.name as "Service",  
  'https://www.notifications.service.gov.uk/services/' || services.id as "Dashboard"
from
  template_folder
join
  services on template_folder.service_id = services.id 
where
  services.id not in (
    select
      distinct services.id
    from
      services
    join
      user_to_service on user_to_service.service_id = services.id
    join
      users on users.id = user_to_service.user_id
    where
      users.platform_admin = true
  )  
group by
  services.id, services.name
order by
  "Number of folders" desc
;

Services without an organisation.sql

select
  services.name,
  'https://www.notifications.service.gov.uk/services/' || services.id as dashboard,
  split_part(users.email_address, '@', 2) as created_by_email_domain
from 
  services
join
  users on services.created_by_id = users.id
where
  services.id not in (
    select
      service_id
    from
      organisation_to_service
  )
order by
  services.created_at desc
;

teams using email address or phone number as placeholder.sql

select
  service_id, services.restricted, templates.id
from
  templates
join
  services on templates.service_id = services.id
where
  template_type = 'sms'
  and templates.archived = false
  and content ilike '%((phone_number%' or content ilike '%((phone number%' or content ilike '%((phonenumber'
order by
  service_id;

select
  service_id, services.restricted, templates.id
from
  templates
join
  services on templates.service_id = services.id
where
  template_type = 'sms'
  and templates.archived = false
  and content ilike '%((email_address%' or content ilike '%((email address%' or content ilike '%((emailaddress'
order by
  service_id;

teams with most contact lists.sql

select
  services.name as "Service name",
  'https://www.notifications.service.gov.uk/services/' || services.id as Dashboard,
  SUM(CASE WHEN archived IS false THEN 1 ELSE 0 END) as Current,
  SUM(CASE WHEN archived IS true THEN 1 ELSE 0 END) as Archived
from
  service_contact_list
join
  services on service_contact_list.service_id = services.id
group by
  1, 2
order by
  Current desc,
  Archived desc
;

teams with the most templates.sql

select 
  count(*),
  services.name,
  'https://www.notifications.service.gov.uk/services/' || services.id
from 
  templates
join 
  services on service_id = services.id  
where 
  templates.archived = false
group by
  services.name, services.id
order by 
  1 desc
;

teams with the most uploaded letters.sql

SELECT
  services.name,
  'https://www.notifications.service.gov.uk/services/' || notifications.service_id AS Dashboard,
  count(*)
FROM
  notifications
JOIN
  templates ON notifications.template_id = templates.id
JOIN
  services ON notifications.service_id = services.id
WHERE
  notification_type = 'letter'
  AND notifications.created_by_id IS NOT NULL
  AND templates.hidden = TRUE
  AND notifications.notification_status != 'cancelled'
GROUP BY
  1, 2
ORDER BY
  COUNT DESC;

teams with the most users sending jobs.sql

select 
  count(distinct(jobs.created_by_id)), services.name
from
  jobs
join 
  services on services.id = jobs.service_id
where 
  services.restricted = false and 
  services.active = true
group by
  services.name
order by
  count desc
;

template type combinations.sql

select 
  ttypes, count(ttypes) from (
    select
      service_id,
      string_agg(distinct(template_type::text), ',') as ttypes
    from
      templates
    join
      services on services.id = templates.service_id
    where
      services.restricted = false and 
      templates.archived = false
    group
      by service_id
  ) as template_types_by_service_id
  group by(ttypes)
  order by count desc
;

users who have been removed from folders.sql

select
  users.name as "Name",
  'https://www.notifications.service.gov.uk/services/' || tf.service_id as "Dashboard",
  tf.name as "Folder name"
from
  user_to_service us
join
  template_folder tf on (us.service_id = tf.service_id)
join
  users on us.user_id = users.id
where
  user_id not in (
    select
      user_id
    from
      user_folder_permissions
    where
      user_folder_permissions.template_folder_id = tf.id
  );

users without specific permission.sql

select
  email_address, user_permissions
from (
  select
    email_address,
    string_agg(permissions.permission::text, ',') as user_permissions
  from
    users
  join
    permissions on users.id = permissions.user_id
  group
    by
      users.email_address,
      users.created_at
  order
    by users.created_at desc
) as users_and_permissions
where 
  user_permissions not ilike '%view_activity%'
;

who only has send messages permission.sql

select
  users.email_address as email_address,
  (
    select
      string_agg(
        permissions.permission::text,
        ','
        order by permissions.permission
      )
    from
      permissions
    where 
      permissions.user_id = users.id and
      permissions.service_id = services.id
  ) as permission_set
from 
  user_to_service 
join 
  services on user_to_service.service_id = services.id
join 
  users on user_to_service.user_id = users.id  
where 
  (
    select
      string_agg(
        permissions.permission::text,
        ','
        order by permissions.permission
      )
    from
      permissions
    where 
      permissions.user_id = users.id and
      permissions.service_id = services.id
  ) = 'send_texts,send_emails,send_letters'
;

who's signed the agreement.sql

select 
  organisation.name as "Organisation",
  'https://www.notifications.service.gov.uk/organisations/' || organisation.id  as "Dashboard",
  users.name || '(' || users.email_address || ')' as "Signed by",
  agreement_signed_on_behalf_of_name || '(' || agreement_signed_on_behalf_of_email_address || ')' as "Signed on behalf of"
from
  organisation
join 
  users on users.id = agreement_signed_by_id
;