idavidmcdonald

ALTER TABLE public.\\"user_to_service\\" ADD CONSTRAINT \\"uix_user_to_service\\" UNIQUE (user_id, service_id);
ALTER TABLE public.\\"user_to_organisation\\" ADD CONSTRAINT \\"uix_user_to_organisation\\" UNIQUE (user_id, organisation_id);
ALTER TABLE public.\\"template_folder\\" ADD CONSTRAINT \\"ix_id_service_id\\" UNIQUE (id, service_id);
ALTER TABLE public.\\"services\\" ADD CONSTRAINT \\"services_name_key\\" UNIQUE (name);
ALTER TABLE public.\\"services\\" ADD CONSTRAINT \\"services_email_from_key\\" UNIQUE (email_from);
ALTER TABLE public.\\"service_data_retention\\" ADD CONSTRAINT \\"uix_service_data_retention\\" UNIQUE (service_id, notification_type);
ALTER TABLE public.\\"service_callback_api\\" ADD CONSTRAINT \\"uix_service_callback_type\\" UNIQUE (service_id, callback_type);
ALTER TABLE public.\\"returned_letters\\" ADD CONSTRAINT \\"returned_letters_notification_id_key\\" UNIQUE (notification_id);
ALTER TABLE public.\\"permissions\\" ADD CONSTRAINT \\"uix_service_user_permission\\" UNIQUE (service_

idavidmcdonald

ALTER TABLE public.annual_billing ADD CONSTRAINT "annual_billing_service_id_fkey" FOREIGN KEY (service_id) REFERENCES services(id);
ALTER TABLE public.annual_billing ADD CONSTRAINT "uix_service_id_financial_year_start" UNIQUE (service_id, financial_year_start);
ALTER TABLE public.api_keys ADD CONSTRAINT "api_keys_key_type_fkey" FOREIGN KEY (key_type) REFERENCES key_types(name);
ALTER TABLE public.api_keys ADD CONSTRAINT "api_keys_secret_key" UNIQUE (secret);
ALTER TABLE public.api_keys ADD CONSTRAINT "api_keys_service_id_fkey" FOREIGN KEY (service_id) REFERENCES services(id);
ALTER TABLE public.api_keys ADD CONSTRAINT "fk_api_keys_created_by_id" FOREIGN KEY (created_by_id) REFERENCES users(id);
ALTER TABLE public.broadcast_event ADD CONSTRAINT "broadcast_event_broadcast_message_id_fkey" FOREIGN KEY (broadcast_message_id) REFERENCES broadcast_message(id);
ALTER TABLE public.broadcast_event ADD CONSTRAINT "broadcast_event_service_id_fkey" FOREIGN KEY (service_id) REFERENCES services(id);
ALTER TABLE public.broa

idavidmcdonald

templates table

id	content
1	Hey there
2	Good morning
3	Great work

notifications table

idavidmcdonald

2023-02-17 14:38:39 UTC:10.0.84.9(46296):notifydb@notifydb:[12139]:ERROR: relation "awsdms_apply_exceptions" does not exist at character 22
2023-02-17 14:38:39 UTC:10.0.84.9(46296):notifydb@notifydb:[12139]:STATEMENT: SELECT COUNT(*) FROM "awsdms_apply_exceptions"
2023-02-17 14:39:41 UTC::@:[370]:LOG: checkpoint starting: xlog
2023-02-17 14:40:03 UTC:10.0.84.9(53214):notifydb@notifydb:[14211]:ERROR: duplicate key value violates unique constraint "spatial_ref_sys_pkey"
2023-02-17 14:40:03 UTC:10.0.84.9(53214):notifydb@notifydb:[14211]:DETAIL: Key (srid)=(3819) already exists.
2023-02-17 14:40:03 UTC:10.0.84.9(53214):notifydb@notifydb:[14211]:CONTEXT: COPY spatial_ref_sys, line 1
2023-02-17 14:40:03 UTC:10.0.84.9(53214):notifydb@notifydb:[14211]:STATEMENT: COPY "public"."spatial_ref_sys" FROM STDIN WITH DELIMITER ',' CSV NULL 'attNULL' ESCAPE '\'
2023-02-17 14:40:27 UTC::@:[370]:LOG: checkpoint complete: wrote 103556 buffers (5.1%); 0 WAL file(s) added, 13 removed, 17 recycled; write=46.066 s, sync=0.100 s, tot

idavidmcdonald

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,

idavidmcdonald

-- 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,

idavidmcdonald

from datetime import date, timedelta

start_date = date(2016, 5, 17)
stop_date = date(2019, 9, 30)

date_to_process = start_date

while date_to_process <= stop_date:

    print(f"""