Skip to content

Useful queries

Jump to bottom
Ian Nice edited this page Nov 8, 2024 · 75 revisions

Get all org users, their name, email, id and name of service they belong to and their permissions

\copy (SELECT services.name as servicename, users.name as username, users.email_address, STRING_AGG(permissions.permission::character varying, ',') as permissions
FROM user_to_service
JOIN users ON users.id = user_to_service.user_id
JOIN services ON user_to_service.service_id = services.id
JOIN permissions ON permissions.user_id = users.id
WHERE services.organisation_id=<orgID>
GROUP BY services.id, users.id
ORDER BY services.id, users.id) TO '~/Downloads/filename.csv' DELIMITER ',' CSV HEADER;

Users with manage service permission (for at least one trial or live service) who have logged in at least twice in the last year

WITH users_with_permission AS (
     SELECT DISTINCT u.id, u.name, u.email_address
     FROM users u
     JOIN permissions p on u.id = p.user_id AND p.permission = 'manage_settings'
     JOIN services s on s.id = p.service_id
     WHERE u.state = 'active'
     AND s.active IS true
 ),
 recent_logins AS (
     SELECT (data ->> 'user_id')::uuid AS id, count(*)
     FROM events
     WHERE
         event_type = 'sucessful_login'
         AND created_at > (now() - interval '12 months')::date
     GROUP BY 1
     HAVING count(*) >= 2
 ),
 eligible_users AS (
     SELECT uwp.*
     FROM users_with_permission uwp
     JOIN recent_logins rl ON uwp.id = rl.id
 )
 SELECT * from eligible_users

Find services sending emails without a reply to email address

select
    services.id as service_id,
    services.name,
    services.volume_email as go_live_expected_email_count,
    services.go_live_at as service_go_live_date,
    count(*) as notifications_sent_without_reply_to_in_last_7_days
from
    notifications
left join services on
    services.id = notifications.service_id
    and services.research_mode is false
    and services.restricted is false
where
    notifications.notification_type = 'email'
    and notifications.reply_to_text is null
    and services.id not in (select distinct(service_id) from service_email_reply_to)
group by
    services.id
order by
    5 desc;

Delete old preview functional test organisation users

delete from user_to_organisation uts using users u
 where uts.user_id = u.id
     and uts.organisation_id = '63b9557c-22ea-42ac-bcba-edaa50e3ae51'
     and u.email_address ~* 'notify-tests-preview\+[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}\+[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}@digital.cabinet-office.gov.uk'
     and u.created_at < (now() - interval '1 days');

Clear the organisation cache from platform admin: https://www.notify.works/platform-admin/clear-cache

Delete old preview functional test templates+folders

SSH to notify-api in preview. Use https://github.com/alphagov/notifications-manuals/wiki/Support-Runbook#ssh-into-one-of-our-ecs-instances

from app.models import Template, TemplateFolder
from app import db

for t in Template.query.filter(Template.service_id=='64f8dff2-913d-471f-8793-57b9087b6ef3', Template.name=='Untitled letter template', Template.archived==False, Template.folder==None): t.archived = True

for t in Template.query.filter(Template.service_id=='64f8dff2-913d-471f-8793-57b9087b6ef3', Template.name.ilike('%sms with placeholders%'), Template.archived==False, Template.folder==None): t.archived = True

for t in Template.query.filter(Template.service_id=='64f8dff2-913d-471f-8793-57b9087b6ef3', Template.name.ilike('%email with placeholders%'), Template.archived==False, Template.folder==None): t.archived = True

for t in Template.query.filter(Template.service_id=='64f8dff2-913d-471f-8793-57b9087b6ef3', Template.name.ilike('%edit/delete%'), Template.archived==False, Template.folder==None): t.archived = True

for t in Template.query.filter(Template.service_id=='64f8dff2-913d-471f-8793-57b9087b6ef3', Template.name.ilike('%test-parent-folder%'), Template.archived==False): t.archived = True

for t in Template.query.filter(Template.service_id=='64f8dff2-913d-471f-8793-57b9087b6ef3', Template.name.ilike('%test-child-folder%'), Template.archived==False): t.archived = True

for t in Template.query.filter(Template.service_id=='64f8dff2-913d-471f-8793-57b9087b6ef3', Template.name.ilike('%test-grandchild-folder%'), Template.archived==False): t.archived = True

db.session.commit()
db.session.begin()

for tf in TemplateFolder.query.filter(TemplateFolder.service_id == '64f8dff2-913d-471f-8793-57b9087b6ef3', TemplateFolder.name.ilike('test-grandchild-folder %')):
    db.session.delete(tf)

db.session.commit()
db.session.begin()

for tf in TemplateFolder.query.filter(TemplateFolder.service_id == '64f8dff2-913d-471f-8793-57b9087b6ef3', TemplateFolder.name.ilike('test-child-folder %')):
    db.session.delete(tf)

db.session.commit()
db.session.begin()

for tf in TemplateFolder.query.filter(TemplateFolder.service_id == '64f8dff2-913d-471f-8793-57b9087b6ef3', TemplateFolder.name.ilike('test-parent-folder %')):
    db.session.delete(tf)

db.session.commit()

Lists of users to send satifaction survey to 2024

/* Users who are a member of at least one live service: */

\copy (
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' AND services.restricted = false THEN 1 END) as num_of_live_services_manager_of,
  most_used_service.service_name AS most_used_service_name,
  most_used_service.org_name AS most_used_service_org,
  most_used_service.org_type AS 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 > '2023-04-30'
  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 live_service_usage.num_sent DESC) as service_name,
  FIRST_VALUE(organisation_name) OVER (PARTITION BY users.id ORDER BY live_service_usage.num_sent DESC) as org_name,
  FIRST_VALUE(organisation_type) OVER (PARTITION BY users.id ORDER BY live_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 > '2023-04-30'
      group by 1, 2, 3, 4
  ) as live_service_usage on user_to_service.service_id = live_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 > '2023-04-30'
group by 1,2,3,4,5,10,11,12
order by 3
) to '/Users/pea.tyczynska/Downloads/satisfaction_survey_users_live_services.csv' delimiter ',' csv header;


/*
=======================

Users with only trial mode services: */

\copy (
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_trial_services_member_of
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 (
  select (data ->> 'user_id')::uuid as user_id, count(*) as num_of_logins
  from events where event_type = 'sucessful_login'
  and created_at > '2023-04-30'
  group by 1
  ) as count_of_logins on users.id = count_of_logins.user_id
WHERE services.active = true
and services.restricted = true
and services.count_as_live = true
and users.state = 'active'
and users.platform_admin = false
and users.logged_in_at > '2023-04-30'
and users.id NOT IN (
    SELECT users.id FROM users join user_to_service on user_to_service.user_id = users.id
join services on user_to_service.service_id = services.id WHERE services.active = true
and services.restricted = false
and services.count_as_live = true
)
group by 1,2,3,4,5
order by 3
) to '/Users/pea.tyczynska/Downloads/satisfaction_survey_users_trial_services.csv' delimiter ',' csv header;

List of potential users to send satisfaction survey to 2022

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;

List of potential users to send satisfaction survey to 2021

select
  users.name,
  email_address,
  users.created_at,
  logged_in_at,
  sum(case when services.restricted = true then 1 else 0 end) as num_trial_services,
  sum(case when services.restricted = false then 1 else 0 end) as num_live_services,
  string_agg(distinct (organisation.name || '--' || organisation.organisation_type), '|') as org_name_types
from users
left outer join user_to_service on user_to_service.user_id = users.id
left outer join services on user_to_service.service_id = services.id
left outer join organisation on services.organisation_id = organisation.id
where (services.active is null or services.active = true)
and (services.count_as_live is null or services.count_as_live = true)
and users.state = 'active'
and users.platform_admin = false
group by 1,2,3,4

List of users with manage_settings or manage_api_keys permissions

Useful when we need to send an email to services.

select distinct(u.name), u.email_address, s.name service_name
from services s
join user_to_service us on (s.id = us.service_id)
join users u on (us.user_id = u.id)
join permissions p on (s.id = p.service_id and u.id = p.user_id)
where p.permission in ('manage_settings', 'manage_api_keys')
and s.active = True
and s.id in ('service_id', 'service_id')
order by s.name;

List of users with manage_settings or manage_api_keys permissions for all live services

Useful if we need to email all live services an update about Notify, this will copy a CSV file to your Downloads folder. You might need to copy this into an editor from the PSQL command line tool (sometimes the editor doesn't like the linebreaks when pasting this query). To access an editor type \e.

\copy (select s.id service_id,
       s.name service_name,
       u.name user_name,
       u.email_address
 from users u
 join user_to_service us on (u.id = us.user_id)
 join services s on (s.id = us.service_id)
 join permissions p on (s.id = p.service_id and u.id = p.user_id)
where p.permission = 'manage_api_keys'
and s.restricted = False
and s.research_mode = False
and s.active = True
and s.name not ilike ('%test%')
order by s.name) To '~/Downloads/service_managers.csv' delimiter ',' CSV HEADER

Daily notifications by channel

select
  bst_date,
  sum(case when notification_type = 'sms' then notifications_sent else 0 end) as sms,
  sum(case when notification_type = 'letter' then notifications_sent else 0 end) as letter,
  sum(case when notification_type = 'email' then notifications_sent else 0 end) as email
from ft_billing
where bst_date >= '2021-10-01' and bst_date < '2021-11-01'
group by bst_date
order by bst_date asc;

Chris’ big gist of queries

https://gist.github.com/quis/5076e25dae9014771dee65f24daf2682

Text message status by provider

select count(*), notification_status, sent_by as provider
from notifications
where notification_type = 'sms'
and key_type != 'test'
and notification_status != 'created'
and created_at >= '2020-04-24 00:00'      -- Add the correct date here
group by notification_status, provider
order by notification_status, provider;

Number of sms messages and billable units sent for time period

select provider, sum(notifications_sent) as notifications_sent, sum(billable_units) as billable_units
from ft_billing
where notification_type = 'sms'
and bst_date >= '2020-10-01'
and bst_date < '2020-11-01'
group by provider;

Number of total letters sent and cost for time period

select bst_date, postage, rate as cost_per_letter, sum(notifications_sent) as num_letters, rate * sum(notifications_sent) as total_cost
from ft_billing
where notification_type = 'letter' 
and bst_date >= '2021-04-01'
and bst_date <= '2021-06-30'
group by bst_date, postage, rate
order by bst_date, postage, rate;

The free allowance and number of billable units used per service

(This example is for the start of the 2020 financial year to yesterday)

select services.id as service_id, services.name as service_name, annual_billing.free_sms_fragment_limit as free_allowance, coalesce(sum(billable_units * rate_multiplier), 0) as units_used from services
join annual_billing on services.id = annual_billing.service_id
left outer join ft_billing on services.id = ft_billing.service_id and bst_date >= '2020-04-01'
where services.active = 't'
and services.restricted = 'f'
and services.count_as_live = 't'
and financial_year_start = 2020
and (bst_date is null or bst_date >= '2020-04-01')
and ft_billing.notification_type = 'sms'
group by(services.id, annual_billing.free_sms_fragment_limit)
order by services.name;

Total volumes for all services.

select service_id,
       service_name,
       sum(total_sms) total_sms,
       sum(total_sms_fragments) as total_sms_fragments,
       free_sms_fragment_limit,
       sum(total_letters) total_letters,
       sum(total_letter_cost) total_letter_cost,
       sum(total_emails) total_emails
from (
    select b.service_id,
           s.name service_name,
           case when notification_type = 'sms' then sum(notifications_sent) else 0 end total_sms,
           case when notification_type = 'sms' then sum(billable_units * b.rate_multiplier) else 0 end total_sms_fragments,
           a.free_sms_fragment_limit,
           case when notification_type = 'letter' then sum(notifications_sent) else 0 end as total_letters,
            case when notification_type = 'letter' then sum(notifications_sent * b.rate) else 0 end as total_letter_cost,
           case when notification_type = 'email' then sum(notifications_sent) else 0 end as total_emails
     from ft_billing b
     join services s on s.id = b.service_id
     join annual_billing a on s.id = a.service_id
     where bst_date >= '2020-04-01'
     and bst_date <= '2021-03-31'
     and a.financial_year_start = 2020
    group by b.service_id, s.name, a.free_sms_fragment_limit, notification_type
    ) as results
group by service_id, service_name, free_sms_fragment_limit
order by 2;

Total volumes on the platform

select sum(total_sms) total_sms,
       sum(total_letters) total_letters,
       sum(total_emails) total_emails
from (
select case when notification_type = 'sms' then sum(notifications_sent) else 0 end total_sms,
       case when notification_type = 'letter' then sum(notifications_sent) else 0 end as total_letters,
       case when notification_type = 'email' then sum(notifications_sent) else 0 end as total_emails
 from ft_billing b
 where bst_date >= '2020-05-01'
 and bst_date <= '2021-05-31'
group by notification_type
    ) as results;

Count of letters with first class postage grouped by service ID

SELECT COUNT(*) AS total, service_id FROM notifications
WHERE notification_type = 'letter'
AND key_type = 'normal'
AND postage = 'first'
AND created_at > (now() - interval '7 days')
AND notification_status IN ('created', 'sending', 'delivered')
GROUP BY service_id
ORDER BY total DESC
LIMIT 20;

Live Services - SMS and letter notifications and costs for a financial year

select
 s.name,
 s.id,
 o.name,
 o.id,
 sum(case when ft_b.notification_type = 'sms' then ft_b.billable_units else 0 end) as sms_fragments,
 sum(case when ft_b.notification_type = 'sms' then ft_b.notifications_sent else 0 end) as sms_notifications,
 sum(case when ft_b.notification_type = 'letter' then ft_b.notifications_sent else 0 end) as letter_notifications,
 sum(case when ft_b.notification_type = 'letter' then ft_b.notifications_sent * ft_b.rate else 0 end) as letter_cost
 from ft_billing ft_b
 join services s on ft_b.service_id = s.id
 join organisation o on s.organisation_id = o.id
 where not s.restricted and s.count_as_live and s.active
 and bst_date between '2020-04-01' and '2021-03-31'
 group by 1, 2, 3, 4
 order by 3, 1;

Count of live services/organisations sending at least one message that week/month

select
    date_trunc('month', ftns.bst_date) :: date as dt, -- change this if you want weekly, daily, etc
    count(distinct s.organisation_id) as count -- change this to `count(distinct s.id)` if you want service counts
from
    ft_notification_status ftns
    join services s on ftns.service_id = s.id
where
    s.count_as_live
    and not s.restricted
group by 1 order by 1

International SMS sent by country

select c.country_name,
       count(*)                                                                                                      as num_notifications_sent,
       sum(CASE WHEN notification_status = 'delivered' THEN 1 ELSE 0 END)                                            as num_notifications_delivered,
       sum(CASE WHEN notification_status = 'sent' THEN 1 ELSE 0 END)                                                 as num_notifications_sent,
       sum(CASE WHEN notification_status = 'temporary-failure' THEN 1 ELSE 0 END)                                    as num_notifications_temp_failure,
       sum(CASE WHEN notification_status = 'permanent-failure' THEN 1 ELSE 0 END)                                    as num_notifications_perm_failure,
       (sum(CASE WHEN notification_status = 'temporary-failure' THEN 1 ELSE 0 END) +
        sum(CASE WHEN notification_status = 'permanent-failure' THEN 1 ELSE 0 END)) / nullif(sum(1), 0)::float *
       100                                                                                                           as failure_percentage
from (select CASE
                 WHEN phone_prefix = '1' THEN 'Canada & United States & Dominican Republic'
                 WHEN phone_prefix = '7' THEN 'South Ossetia & Kazakhstan & Abkhazia & Russian Federation'
                 WHEN phone_prefix = '20' THEN 'Egypt'
                 WHEN phone_prefix = '27' THEN 'South Africa'
                 WHEN phone_prefix = '30' THEN 'Greece'
                 WHEN phone_prefix = '31' THEN 'Netherlands'
                 WHEN phone_prefix = '32' THEN 'Belgium'
                 WHEN phone_prefix = '33' THEN 'France'
                 WHEN phone_prefix = '34' THEN 'Spain'
                 WHEN phone_prefix = '36' THEN 'Hungary'
                 WHEN phone_prefix = '39' THEN 'Italy'
                 WHEN phone_prefix = '40' THEN 'Romania'
                 WHEN phone_prefix = '41' THEN 'Switzerland'
                 WHEN phone_prefix = '43' THEN 'Austria'
                 WHEN phone_prefix = '44' THEN 'Guernsey & Isle of Man & Jersey'
                 WHEN phone_prefix = '45' THEN 'Denmark'
                 WHEN phone_prefix = '46' THEN 'Sweden'
                 WHEN phone_prefix = '47' THEN 'Norway'
                 WHEN phone_prefix = '48' THEN 'Poland'
                 WHEN phone_prefix = '49' THEN 'Germany'
                 WHEN phone_prefix = '51' THEN 'Peru'
                 WHEN phone_prefix = '52' THEN 'Mexico'
                 WHEN phone_prefix = '53' THEN 'Cuba'
                 WHEN phone_prefix = '54' THEN 'Argentina'
                 WHEN phone_prefix = '55' THEN 'Brazil'
                 WHEN phone_prefix = '56' THEN 'Chile'
                 WHEN phone_prefix = '57' THEN 'Colombia'
                 WHEN phone_prefix = '58' THEN 'Venezuela'
                 WHEN phone_prefix = '60' THEN 'Malaysia'
                 WHEN phone_prefix = '61' THEN 'Australia'
                 WHEN phone_prefix = '62' THEN 'Indonesia'
                 WHEN phone_prefix = '63' THEN 'Philippines'
                 WHEN phone_prefix = '64' THEN 'New Zealand'
                 WHEN phone_prefix = '65' THEN 'Singapore'
                 WHEN phone_prefix = '66' THEN 'Thailand'
                 WHEN phone_prefix = '81' THEN 'Japan'
                 WHEN phone_prefix = '82' THEN 'Korea, Republic of'
                 WHEN phone_prefix = '84' THEN 'Vietnam'
                 WHEN phone_prefix = '86' THEN 'China'
                 WHEN phone_prefix = '90' THEN 'Turkey & Northern Cyprus'
                 WHEN phone_prefix = '91' THEN 'India'
                 WHEN phone_prefix = '92' THEN 'Pakistan'
                 WHEN phone_prefix = '93' THEN 'Afghanistan'
                 WHEN phone_prefix = '94' THEN 'Sri Lanka'
                 WHEN phone_prefix = '95' THEN 'Myanmar'
                 WHEN phone_prefix = '98' THEN 'Iran'
                 WHEN phone_prefix = '211' THEN 'South Sudan'
                 WHEN phone_prefix = '212' THEN 'Morocco'
                 WHEN phone_prefix = '213' THEN 'Algeria'
                 WHEN phone_prefix = '216' THEN 'Tunisia'
                 WHEN phone_prefix = '218' THEN 'Libya'
                 WHEN phone_prefix = '220' THEN 'Gambia'
                 WHEN phone_prefix = '221' THEN 'Senegal'
                 WHEN phone_prefix = '222' THEN 'Mauritania'
                 WHEN phone_prefix = '223' THEN 'Mali'
                 WHEN phone_prefix = '224' THEN 'Guinea'
                 WHEN phone_prefix = '225' THEN 'Cote d''Ivoire'
                 WHEN phone_prefix = '226' THEN 'Burkina Faso'
                 WHEN phone_prefix = '227' THEN 'Niger'
                 WHEN phone_prefix = '228' THEN 'Togo'
                 WHEN phone_prefix = '229' THEN 'Benin'
                 WHEN phone_prefix = '230' THEN 'Mauritius'
                 WHEN phone_prefix = '231' THEN 'Liberia'
                 WHEN phone_prefix = '232' THEN 'Sierra Leone'
                 WHEN phone_prefix = '233' THEN 'Ghana'
                 WHEN phone_prefix = '234' THEN 'Nigeria'
                 WHEN phone_prefix = '235' THEN 'Chad'
                 WHEN phone_prefix = '236' THEN 'Central African Republic'
                 WHEN phone_prefix = '237' THEN 'Cameroon'
                 WHEN phone_prefix = '238' THEN 'Cape Verde'
                 WHEN phone_prefix = '239' THEN 'Sao Tome and Principe'
                 WHEN phone_prefix = '240' THEN 'Equatorial Guinea'
                 WHEN phone_prefix = '241' THEN 'Gabon'
                 WHEN phone_prefix = '242' THEN 'Congo'
                 WHEN phone_prefix = '243' THEN 'Congo, Democratic Republic of'
                 WHEN phone_prefix = '244' THEN 'Angola'
                 WHEN phone_prefix = '245' THEN 'Guinea-Bissau'
                 WHEN phone_prefix = '246' THEN 'British Indian Ocean Territory'
                 WHEN phone_prefix = '248' THEN 'Seychelles'
                 WHEN phone_prefix = '249' THEN 'Sudan'
                 WHEN phone_prefix = '250' THEN 'Rwanda, Republic of'
                 WHEN phone_prefix = '251' THEN 'Ethiopia'
                 WHEN phone_prefix = '252' THEN 'Somalia'
                 WHEN phone_prefix = '253' THEN 'Djibouti, Republic of'
                 WHEN phone_prefix = '254' THEN 'Kenya'
                 WHEN phone_prefix = '255' THEN 'Tanzania'
                 WHEN phone_prefix = '256' THEN 'Uganda'
                 WHEN phone_prefix = '257' THEN 'Burundi'
                 WHEN phone_prefix = '258' THEN 'Mozambique'
                 WHEN phone_prefix = '260' THEN 'Zambia'
                 WHEN phone_prefix = '261' THEN 'Madagascar'
                 WHEN phone_prefix = '262' THEN 'Reunion'
                 WHEN phone_prefix = '263' THEN 'Zimbabwe'
                 WHEN phone_prefix = '264' THEN 'Namibia'
                 WHEN phone_prefix = '265' THEN 'Malawi'
                 WHEN phone_prefix = '266' THEN 'Lesotho'
                 WHEN phone_prefix = '267' THEN 'Botswana'
                 WHEN phone_prefix = '268' THEN 'Eswatini'
                 WHEN phone_prefix = '269' THEN 'Comoros'
                 WHEN phone_prefix = '297' THEN 'Aruba'
                 WHEN phone_prefix = '298' THEN 'Faroe Islands'
                 WHEN phone_prefix = '299' THEN 'Greenland'
                 WHEN phone_prefix = '350' THEN 'Gibraltar'
                 WHEN phone_prefix = '351' THEN 'Portugal'
                 WHEN phone_prefix = '352' THEN 'Luxembourg'
                 WHEN phone_prefix = '353' THEN 'Ireland'
                 WHEN phone_prefix = '354' THEN 'Iceland'
                 WHEN phone_prefix = '355' THEN 'Albania'
                 WHEN phone_prefix = '356' THEN 'Malta'
                 WHEN phone_prefix = '357' THEN 'Cyprus'
                 WHEN phone_prefix = '358' THEN 'Finland'
                 WHEN phone_prefix = '359' THEN 'Bulgaria'
                 WHEN phone_prefix = '370' THEN 'Lithuania'
                 WHEN phone_prefix = '371' THEN 'Latvia'
                 WHEN phone_prefix = '372' THEN 'Estonia'
                 WHEN phone_prefix = '373' THEN 'Moldova'
                 WHEN phone_prefix = '374' THEN 'Armenia'
                 WHEN phone_prefix = '375' THEN 'Belarus'
                 WHEN phone_prefix = '376' THEN 'Andorra'
                 WHEN phone_prefix = '377' THEN 'Monaco'
                 WHEN phone_prefix = '378' THEN 'San Marino, Republic of'
                 WHEN phone_prefix = '380' THEN 'Ukraine'
                 WHEN phone_prefix = '381' THEN 'Serbia'
                 WHEN phone_prefix = '382' THEN 'Montenegro'
                 WHEN phone_prefix = '383' THEN 'Kosovo'
                 WHEN phone_prefix = '385' THEN 'Croatia'
                 WHEN phone_prefix = '386' THEN 'Slovenia'
                 WHEN phone_prefix = '387' THEN 'Bosnia and Herzegovina'
                 WHEN phone_prefix = '389' THEN 'Macedonia'
                 WHEN phone_prefix = '420' THEN 'Czech Republic'
                 WHEN phone_prefix = '421' THEN 'Slovakia'
                 WHEN phone_prefix = '423' THEN 'Liechtenstein'
                 WHEN phone_prefix = '500' THEN 'Falkland Islands'
                 WHEN phone_prefix = '501' THEN 'Belize'
                 WHEN phone_prefix = '502' THEN 'Guatemala'
                 WHEN phone_prefix = '503' THEN 'El Salvador'
                 WHEN phone_prefix = '504' THEN 'Honduras'
                 WHEN phone_prefix = '505' THEN 'Nicaragua'
                 WHEN phone_prefix = '506' THEN 'Costa Rica'
                 WHEN phone_prefix = '507' THEN 'Panama'
                 WHEN phone_prefix = '508' THEN 'Saint Pierre and Miquelon'
                 WHEN phone_prefix = '509' THEN 'Haiti'
                 WHEN phone_prefix = '590' THEN 'Guadeloupe'
                 WHEN phone_prefix = '591' THEN 'Bolivia'
                 WHEN phone_prefix = '592' THEN 'Guyana'
                 WHEN phone_prefix = '593' THEN 'Ecuador'
                 WHEN phone_prefix = '594' THEN 'French Guiana'
                 WHEN phone_prefix = '595' THEN 'Paraguay'
                 WHEN phone_prefix = '596' THEN 'Martinique'
                 WHEN phone_prefix = '597' THEN 'Suriname'
                 WHEN phone_prefix = '598' THEN 'Uruguay'
                 WHEN phone_prefix = '599' THEN 'Curacao (former Netherlands Antilles)'
                 WHEN phone_prefix = '670' THEN 'Timor L''este'
                 WHEN phone_prefix = '672' THEN 'Norfolk Island'
                 WHEN phone_prefix = '673' THEN 'Brunei Darussalam'
                 WHEN phone_prefix = '674' THEN 'Nauru'
                 WHEN phone_prefix = '675' THEN 'Papua New Guinea'
                 WHEN phone_prefix = '676' THEN 'Tonga'
                 WHEN phone_prefix = '677' THEN 'Solomon Islands'
                 WHEN phone_prefix = '678' THEN 'Vanuatu'
                 WHEN phone_prefix = '679' THEN 'Fiji'
                 WHEN phone_prefix = '680' THEN 'Palau'
                 WHEN phone_prefix = '682' THEN 'Cook Islands'
                 WHEN phone_prefix = '685' THEN 'Samoa'
                 WHEN phone_prefix = '687' THEN 'New Caledonia'
                 WHEN phone_prefix = '689' THEN 'French Polynesia'
                 WHEN phone_prefix = '691' THEN 'Micronesia, Federated States of'
                 WHEN phone_prefix = '692' THEN 'Marshall Islands'
                 WHEN phone_prefix = '852' THEN 'Hong Kong'
                 WHEN phone_prefix = '853' THEN 'Macau'
                 WHEN phone_prefix = '855' THEN 'Cambodia'
                 WHEN phone_prefix = '856' THEN 'Laos'
                 WHEN phone_prefix = '880' THEN 'Bangladesh'
                 WHEN phone_prefix = '886' THEN 'Taiwan'
                 WHEN phone_prefix = '960' THEN 'Maldives'
                 WHEN phone_prefix = '961' THEN 'Lebanon'
                 WHEN phone_prefix = '962' THEN 'Jordan'
                 WHEN phone_prefix = '963' THEN 'Syria'
                 WHEN phone_prefix = '964' THEN 'Iraq'
                 WHEN phone_prefix = '965' THEN 'Kuwait'
                 WHEN phone_prefix = '966' THEN 'Saudi Arabia'
                 WHEN phone_prefix = '967' THEN 'Yemen'
                 WHEN phone_prefix = '968' THEN 'Oman'
                 WHEN phone_prefix = '970' THEN 'Palestinian Territory'
                 WHEN phone_prefix = '971' THEN 'United Arab Emirates'
                 WHEN phone_prefix = '972' THEN 'Israel'
                 WHEN phone_prefix = '973' THEN 'Bahrain'
                 WHEN phone_prefix = '974' THEN 'Qatar'
                 WHEN phone_prefix = '975' THEN 'Bhutan'
                 WHEN phone_prefix = '976' THEN 'Mongolia'
                 WHEN phone_prefix = '977' THEN 'Nepal'
                 WHEN phone_prefix = '992' THEN 'Tajikistan'
                 WHEN phone_prefix = '993' THEN 'Turkmenistan'
                 WHEN phone_prefix = '994' THEN 'Azerbaijan'
                 WHEN phone_prefix = '995' THEN 'Georgia'
                 WHEN phone_prefix = '996' THEN 'Kyrgyzstan'
                 WHEN phone_prefix = '998' THEN 'Uzbekistan'
                 WHEN phone_prefix = '1242' THEN 'Bahamas'
                 WHEN phone_prefix = '1246' THEN 'Barbados'
                 WHEN phone_prefix = '1264' THEN 'Anguilla'
                 WHEN phone_prefix = '1268' THEN 'Antigua and Barbuda'
                 WHEN phone_prefix = '1284' THEN 'Virgin Islands, British'
                 WHEN phone_prefix = '1345' THEN 'Cayman Islands'
                 WHEN phone_prefix = '1441' THEN 'Bermuda'
                 WHEN phone_prefix = '1473' THEN 'Grenada'
                 WHEN phone_prefix = '1649' THEN 'Turks and Caicos Islands'
                 WHEN phone_prefix = '1664' THEN 'Montserrat'
                 WHEN phone_prefix = '1684' THEN 'American Samoa'
                 WHEN phone_prefix = '1721' THEN 'Sint Maarten'
                 WHEN phone_prefix = '1758' THEN 'Saint Lucia'
                 WHEN phone_prefix = '1767' THEN 'Dominica, Commonwealth of'
                 WHEN phone_prefix = '1784' THEN 'Saint Vincent and The Grenadines'
                 WHEN phone_prefix = '1868' THEN 'Trinidad and Tobago'
                 WHEN phone_prefix = '1869' THEN 'Saint Kitts and Nevis'
                 WHEN phone_prefix = '1876' THEN 'Jamaica'
                 ELSE 'Unknown'
                 END as country_name,
             notification_status
      from notifications
      where notification_type = 'sms'
        and key_type = 'normal'
        and international is true) as c
group by 1
order by 1;

Data for this query was taken from https://github.com/datasets/country-codes/blob/master/data/country-codes.csv and then manually edited into https://docs.google.com/spreadsheets/d/1kAVLoFbnxDJOkg2YsaJeKCMm0rjAA4YLnfLFaz1dIwA/edit#gid=1095522260 to give just the data needed to generate the query.

For an organisation, show % delivery times for SMS, by day for the previous month, by buckets

This is used for the monthly DWP reports

Should be easily adaptable to run for a specific service, for a different notification type, or for different buckets.

By default, this will run for the previous month, however you can change the dates by changing the n.created_at values to hardcoded dates. It's best to run the query for one month at time since it takes a long time to fetch the data

WITH notif_timings AS (
    SELECT
        n.created_at::date AS "created_date",
        EXTRACT(EPOCH FROM n.updated_at) - EXTRACT(EPOCH FROM n.created_at) AS "duration"
    FROM notification_history AS n
    WHERE
        service_id = 'SERVICE-ID-HERE'
        AND key_type = 'normal'
        AND notification_type = 'sms'
        AND n.created_at >= date_trunc('month', date_trunc('month', NOW())::date - interval '1 day')::date  /* First day of last month */
        AND n.created_at < date_trunc('month', NOW())::date  /* First day of this month */
        AND notification_status = 'delivered'
)
SELECT
    created_date,
    TRIM(TO_CHAR(created_date, 'Day')) AS "created_weekday",
    COUNT(*) FILTER(WHERE duration < 60) AS "< 60 seconds (#)",
    ROUND(((COUNT(*) FILTER(WHERE duration < 60)) / COUNT(*)::float * 100)::numeric, 2) AS "< 60 seconds (%)",
    COUNT(*) FILTER(WHERE duration >= 60 AND duration < 300) AS "1 - 5 minutes (#)",
    ROUND(((COUNT(*) FILTER(WHERE duration >= 60 AND duration < 300)) / COUNT(*)::float * 100)::numeric, 2) AS "1 - 5 minutes (%)",
    COUNT(*) FILTER(WHERE duration >= 300 AND duration < 600) AS "5 - 10 minutes (#)",
    ROUND(((COUNT(*) FILTER(WHERE duration >= 300 AND duration < 600)) / COUNT(*)::float * 100)::numeric, 2) AS "5 - 10 minutes (%)",
    COUNT(*) FILTER(WHERE duration >= 600 AND duration < 1800) AS "10 - 30 minutes (#)",
    ROUND(((COUNT(*) FILTER(WHERE duration >= 600 AND duration < 1800)) / COUNT(*)::float * 100)::numeric, 2) AS "10 - 30 minutes (%)",
    COUNT(*) FILTER(WHERE duration >= 1800 AND duration < 3600) AS "30 - 60 minutes (#)",
    ROUND(((COUNT(*) FILTER(WHERE duration >= 1800 AND duration < 3600)) / COUNT(*)::float * 100)::numeric, 2) AS "30 - 60 minutes (%)",
    COUNT(*) FILTER(WHERE duration >= 3600 AND duration < 86400) AS "1 - 24 hours (#)",
    ROUND(((COUNT(*) FILTER(WHERE duration >= 3600 AND duration < 86400)) / COUNT(*)::float * 100)::numeric, 2) AS "1 - 24 hours (%)",
    COUNT(*) FILTER(WHERE duration >= 86400) AS ">24 hours (#)",
    ROUND(((COUNT(*) FILTER(WHERE duration >= 86400)) / COUNT(*)::float * 100)::numeric, 2) AS ">24 hours (%)"
FROM notif_timings
GROUP BY created_date

Find services which have sent >1000 notifications of a given type in the last 15 minutes

select date_trunc('minute', created_at), service_id, key_type, notification_type, count(*)
from notifications
where created_at >= date_trunc('minute', now()) - interval '15 minutes'
group by 1, 2, 3, 4
having count(*) > 1000
order by 1, 3 desc;

Export Notification Data for Services to CSV (Manual process)

Query to extract notification data for specific services into a CSV file. The process includes handling multiple runs by appending data to the same file and using the last created_at timestamp from each run to avoid duplicate rows.

Before we start the process it might be useful to run a count of all notifications available for the service so you know how many rows are expected

SELECT COUNT(*) FROM notifications where service_id = 'your-service-id'

1. Set Parameters for the Query Before running the query, you must set the following parameters:

  • :service_id: Replace this placeholder with the actual service_id for the service you are querying
  • :notification_type: Define which notification types you are interested in ('sms', 'email', 'letter'). You can include multiple types.
  • :start_time: Replace this with the earliest data you want to return from your query.
WHERE n.service_id = 'your-service-id'
AND n.notification_type IN ('sms', 'email')
AND n.created_at >= '2024-08-24 00:00:00'

2. First Run: Export Data and Write CSV with Headers Please note this query must be run with the pgcli tool, not psql.

In the first query run, you will generate the CSV file with the column headers included. This will export up to 250K rows, at a time. At this moment we have this limitation because when we try to retrieve higher numbers, our application is affected.

\copy (
    SELECT
        n.to AS "Recipient",                          -- Recipient
        n.client_reference AS "Reference",            -- Reference
        t.name AS "Template",                         -- Template
        n.notification_type AS "Type",                -- Type
        u.name AS "Sent by",                          -- Sent by (Created by name)
        u.email_address AS "Sent by email",           -- Sent by email (Created by email)
        j.original_file_name AS "Job",                -- Job (Job original file name)
        CASE
            WHEN n.notification_type = 'email' THEN
                CASE n.notification_status
                    WHEN 'failed' THEN 'Failed'
                    WHEN 'technical-failure' THEN 'Technical failure'
                    WHEN 'temporary-failure' THEN 'Inbox not accepting messages right now'
                    WHEN 'permanent-failure' THEN 'Email address doesn’t exist'
                    WHEN 'delivered' THEN 'Delivered'
                    WHEN 'sending' THEN 'Sending'
                    ELSE n.notification_status
                END
            WHEN n.notification_type = 'sms' THEN
                CASE n.notification_status
                    WHEN 'failed' THEN 'Failed'
                    WHEN 'technical-failure' THEN 'Technical failure'
                    WHEN 'temporary-failure' THEN 'Phone not accepting messages right now'
                    WHEN 'permanent-failure' THEN 'Phone number doesn’t exist'
                    WHEN 'delivered' THEN 'Delivered'
                    WHEN 'sending' THEN 'Sending'
                    ELSE n.notification_status
                END
            WHEN n.notification_type = 'letter' THEN
                CASE n.notification_status
                    WHEN 'technical-failure' THEN 'Technical failure'
                    WHEN 'permanent-failure' THEN 'Permanent failure'
                    WHEN 'sending' THEN 'Accepted'
                    WHEN 'created' THEN 'Accepted'
                    WHEN 'delivered' THEN 'Received'
                    ELSE n.notification_status
                END
            ELSE n.notification_status
        END AS "Status",                              -- Status
        n.created_at AS "Time",                       -- Time (Created at)
        a.name AS "API key name"                      -- API key name
    FROM notifications n
    LEFT JOIN templates_history t ON n.template_id = t.id AND n.template_version = t.version
    LEFT JOIN users u ON n.created_by_id = u.id
    LEFT JOIN jobs j ON n.job_id = j.id
    LEFT JOIN api_keys a ON n.api_key_id = a.id
    WHERE n.service_id = :service_id
        -- Filter by notification_type
        AND n.notification_type IN (:notification_type)
        AND n.created_at >= :start_timestamp
    ORDER BY n.created_at ASC -- Sorting by time so we can keep adding date manually
    LIMIT 250000  -- Limit to 250k rows for each batch
) TO '~/Downloads/batch_report.csv' DELIMITER ',' CSV HEADER;
  • Output: This will generate a CSV file (batch_report.csv) in the ~/Downloads/ folder, complete with headers.

3. After First Run:

3. 1. Use the Last created_at Value

Once the first query run completes:

  1. 1.1. Open the CSV File:
    • Open the CSV file generated in the first run and navigate to the last row.
    • Find the Time (Created at) column and copy the value of the last created_at timestamp (e.g., '2024-09-01 12:34:56').
  1. 1.2. Modify the Query for the Next Run:
    • In subsequent runs, update the n.created_at timestamp in the following line in the WHERE clause:
AND n.created_at > '2024-09-01 12:34:56'  -- Replace with the actual last created_at value of the last row from the previous run

3. 2. Export Data Without Headers for Subsequent Runs For subsequent query runs, remove the CSV HEADER option to append the new rows to the same CSV file.

From:

TO '~/Downloads/batch_report.csv' DELIMITER ',' CSV HEADER;

To:

TO '~/Downloads/temp_batch_report.csv' DELIMITER ',' CSV;
  • Output: The new rows will overwrite existing ones on temp_batch_report.csv file.

3. 3. Append Data to the Existing CSV File Using cat

After each manual query run, you need to append the data from the temporary file to the main CSV file. Use the following command:

cat ~/Downloads/temp_batch_report.csv >> ~/Downloads/batch_report.csv

By following these steps, you can extract and append notification data to the same CSV file across multiple runs.

4. Split and Compress CSV Files into a ZIP

Use the following script to split and compress large CSV files into multiple smaller parts, ensuring they don't exceed 25 MB, and then compress them into a ZIP file. The 25MB limit is in place because that's the max file size we can send via email.

You can run the script directly from the terminal but if you are unsure, here are a few steps to create a file and run it

    1. Create the directory
mkdir -p ~/scripts
    1. Create the file
nano ~/scripts/split_and_compress_with_zip.sh
    1. Paste script and save changes
#!/bin/bash

# Define the file path
csv_file="$HOME/Downloads/batch_report.csv"
zip_file="$HOME/Downloads/batch_report.zip"

# Check if the file exists
if [ -f "$csv_file" ]; then
    # Compress the file into a .zip archive without including directory structure
    zip -j "$zip_file" "$csv_file"
    echo "File compressed successfully: $zip_file"
else
    echo "File not found: $csv_file"
fi
    1. Make script executable and run it
chmod +x ~/scripts/split_and_compress_with_zip.sh

bash ~/scripts/split_and_compress_with_zip.sh
  • Output will look something like the following:
adding: batch_report_part_aa.csv (deflated 86%)
adding: batch_report_part_ab.csv (deflated 60%)

5. Delete existing files from your env

  • Don't forget to remove the downloaded reports as these are personal details from users.
Clone this wiki locally