Data Adapter queries
Use these examples to execute SQL queries using Data Adapter
Overview
Once you have successfully connected your preferred tool to Data Adapter, you should be able to execute your own SQL queries.
Amazon Athena supports a subset of the Data Definition Language (DDL) and Data Manipulation Language (DML) statements, functions, operators, and data types.
AWS Athena SQL reference
For more information on supported statements and caveats, please see the AWS SQL reference for Athena. This includes the full list of supported SQL functions.
This guide covers some common queries and functions to help get you started.
Data dictionary for navigating your Root data
To help you in navigate Root's data structure and tables, we have created a comprehensive data dictionary. It offers detailed information on data elements, their types, keys and interrelationships. It also provides hints on how you can use the data to extract meaningful insights for your business.
Click here to view the data dictionary. We hope it helps you maximise the value of the Data Adapter and streamline data analysis processes.
Working with JSON fields
Due to the highly configurable nature of the Root platform, many fields are represented as JSON objects or arrays that are returned as plain VARCHAR strings. The Athena Querying JSON guide contains good examples of how to work with these fields.
Use the API reference to understand the shape of JSON data
Many of the JSON fields you will encounter when using Data Adapter are described in our API reference.
For example, if you want to understand the shape of the
created_byJSON field on thepaymentstable, go to the List policy payments endpoint and expand the200 Successresponse to see a schema of thepaymentobject.
Here are examples of how to extract data from a JSON field. These examples query the charges JSON array on the policies table. To understand which properties to expect on each charges object, see the policy object on the API reference.
SELECT
policy_id,
-- first charge
JSON_EXTRACT_SCALAR(charges, '$[0].name')
AS "charge_0_name",
CAST(JSON_EXTRACT(charges, '$[0].amount') AS INTEGER)
AS "charge_0_amount",
-- second charge
JSON_EXTRACT_SCALAR(charges, '$[1].name')
AS "charge_1_name",
CAST(JSON_EXTRACT(charges, '$[1].amount') AS INTEGER)
AS "charge_1_amount",
-- last charge
JSON_ARRAY_GET(charges, -1)
AS "charges_last",
JSON_EXTRACT_SCALAR(JSON_ARRAY_GET(charges, -1), '$.name')
AS "charges_last_name",
CAST(JSON_EXTRACT(JSON_ARRAY_GET(charges, -1), '$.amount') AS INTEGER)
AS "charges_last_amount"
FROM
policies
LIMIT 100
;
Working with dates
You can find a good overview of working with dates in the PrestoDB documentation. Dates on the Root platform are generally represented as ISO 8601 strings in the UTC timezone.
All entities have a created_at field, allowing to fetch entities from a certain timeframe, like how many policies were created in the last month:
SELECT
count(*)
FROM
policies
WHERE
-- specify dates relative to now (eg. not older than 1 month)
AND created_at > NOW() - INTERVAL '1' MONTH
Below are examples of converting date strings to timestamps. These examples query the schedule_versions JSON array on the policies table. To understand which properties to expect on each schedule version object, refer to the policy object on the API reference.
SELECT
policy_id,
JSON_ARRAY_GET(schedule_versions, -1)
AS "last_schedule_version",
-- parse string to ensure valid date
from_iso8601_timestamp(
JSON_EXTRACT_SCALAR(
JSON_ARRAY_GET(schedule_versions, -1),
'$.createdAt'
)
) AS "last_schedule_created",
-- cast to timestamp for further comparison
CAST (
from_iso8601_timestamp(
JSON_EXTRACT_SCALAR(
JSON_ARRAY_GET(schedule_versions, -1),
'$.createdAt'
)
)
AS timestamp
) AS "last_schedule_created_timestamp"
FROM
policies
LIMIT 100
If you want to work with dates in a particular timezone, please see Athena's list of supported timezones and available timezone functions. When using the format_datetime function, also have a look at the available formatting options.
Below are examples of working with timezones. These examples convert the created_at date on the policies table to different timezones.
SELECT
policy_id,
created_at,
-- display timestamp in specific timezone
created_at AT TIME ZONE 'UTC' AS "created_at_utc",
created_at AT TIME ZONE 'Africa/Johannesburg' AS "created_at_jhb",
created_at AT TIME ZONE 'America/Los_Angeles' AS "created_at_la",
-- format timestamp
format_datetime(
created_at AT TIME ZONE 'Africa/Johannesburg',
'E, d MMMM y H:m:s Z'
) AS "created_at_format"
FROM
policies
WHERE
-- specify range in specific timezone
created_at
BETWEEN timestamp '2022-11-01 00:00:00 Africa/Johannesburg'
AND timestamp '2022-12-01 00:00:00 Africa/Johannesburg'
-- specify dates relative to now (eg. not older than 1 year)
AND created_at > NOW() - INTERVAL '1' YEAR
LIMIT 100
Creating views
To simplify analytical queries, you can make use of views to extract data from complex fields.
Names of views must end with
_viewThe name of any view created on the Root Data adapter must end with
_view. For example,dinosure_policy_viewormy_custom_event_view.
In the example below, we start with aSELECT query that extracts or combines the desired data from the policy_events table:
- Extracting the
policy_idfrom the persistence key and giving it an alias. - Extracting event
typeand with its own alias. - If the event was initiated by a user (and not via API or the system itself), we extract the UUID of that user with the alias
requested_by_user_id.
The result of this query will make it easier to find and/or join events for specific policies, and perform further analysis.
SELECT
regexp_replace(
persistence_key,
'^.+-policy:',
''
) AS "policy_id",
created_at,
json_extract_scalar(data, '$.type') AS "type",
sequence,
version,
data,
CASE
json_extract_scalar(data, '$.metadata.requested_by.type')
WHEN 'user' THEN json_extract_scalar(
data,
'$.metadata.requested_by.id'
)
END AS "requested_by_user_id",
persistence_key
FROM
policy_events
LIMIT 100
Once we have confirmed that the query is returning the expected results, we can use the CREATE VIEW query:
CREATE VIEW policy_events_view AS
SELECT
regexp_replace(
persistence_key,
'^.+-policy:',
''
) AS "policy_id",
created_at,
json_extract_scalar(data, '$.type') AS "type",
sequence,
version,
data,
CASE
json_extract_scalar(data, '$.metadata.requested_by.type')
WHEN 'user' THEN json_extract_scalar(data, '$.metadata.requested_by.id')
END AS "requested_by_user_id",
persistence_key
FROM
policy_events
Now, we can use this view in the same way as any other table in further queries. In this case, we are using the view to combine information from the policy_events table with information from the policies and users tables.
SELECT
pol.policy_id,
ev.created_at AS "activated_at",
usr.email AS "activated_by"
FROM policies AS pol
LEFT JOIN policy_events_view AS ev
ON ev.policy_id = pol.policy_id
AND ev.type = 'policy_activated'
LEFT JOIN users AS usr
ON usr.id = ev.requested_by_user_id
LIMIT 100
If the view was created for temporary analyses, don't forget to DROP the view when you are done:
DROP TABLE policy_events_view
Collections & billing operations
For background on these queries, see the Billing overview guide, as well as the other guides in the "Billing & payments" category.
SELECT
*
FROM
payments
WHERE
environment = 'production' -- Limits results to the production environment
AND finalized_at >= CURRENT_DATE - INTERVAL '1' month -- Limits results to where the finalized_at is within the last month
AND status = 'failed' -- Limits results to payments with a status of failed
ORDER BY
finalized_at; -- Orders the results from oldest to most recent finalized payment.
SELECT
policy_id,
payment_method_id,
collection_type,
payment_id,
description AS payment_description,
amount, -- In cents
currency,
status AS payment_status,
failure_code,
failure_reason,
json_extract_scalar(created_by, '$.type') AS created_by,
cast(billing_date AS date) AS billing_date,
cast(created_at AS date) AS payment_created_at,
cast(action_date AS date) AS action_date,
cast(submitted_at AS date) AS submitted_date,
cast(finalized_at AS date) AS finalized_at,
json_extract_scalar(finalized_by, '$.type') AS finalized_by,
json_extract_scalar(submitted_by, '$.type') AS submitted_by
FROM
payments
WHERE
environment = 'production' -- Limits results to the production environment.
AND status = 'failed' -- Limits results to only failed payments.
AND finalized_at >= CURRENT_DATE - INTERVAL '1' month -- Limits the results by only including a finalized_at date of within the last month.
ORDER BY finalized_at -- Orders the results from oldest to most recent finalized payment.
;
SELECT
pm.payment_method_id,
pm.type,
pm.banv_status,
pm.blocked_reason,
pm.updated_at,
pm.policyholder_id,
ph.policy_ids AS linked_policies,
pm.account_holder
FROM
payment_methods AS pm
LEFT JOIN policyholders AS ph ON ph.policyholder_id = pm.policyholder_id -- Linking the policyholders table to return linked data
WHERE
pm.environment = 'production' -- Limits results to the production environment.
AND pm.banv_status = 'blocked' -- Limits results to return payment methods with blocked status.
ORDER BY
pm.updated_at DESC; -- Orders the results from most recent to oldest updated payment method.
SELECT
id AS refund_id,
cast(created_at AS date) AS created_at,
json_extract_scalar(created_by, '$.type') AS created_by_type, -- Extracts the type from the created_by object.
json_extract_scalar(created_by, '$.id') AS created_by_id, -- Extracts the type from the created_by object.
status AS refund_status,
type,
amount AS refund_amount, -- Returns an amount in cents.
description,
json_extract_scalar(linked_entities, '$.policyId') AS policy_id, -- Extracts the policy_id from the linked_entities object.
-- finalised_at, -- Commented out because it will be a null value if status is pending.
-- json_extract_scalar(finalised_by, '$.type') AS finalized_by_type, -- Commented out because it will be a null value if status is pending.
-- json_extract_scalar(finalised_by, '$.id') AS finalized_by_id, -- Commented out because it will be a null value if status is pending.
-- rejection_reason, -- Commented out because it will be a null value if status is pending.
-- proof_of_payment_id, -- Commented out because it will be a null value if status is pending.
action_date,
payee AS payee_details
FROM
payout_requests
WHERE
type = 'policy_refund'
AND status = 'pending'
AND environment = 'prodcution' -- Limits results to the production environment.
ORDER BY
created_at -- Orders the results from oldest to most recent refund request.
;
WITH cte_ledger AS ( -- Creating a common table expression to simplify data in the main query.
SELECT
policy_id,
sum(amount) AS balance
FROM
policy_ledger
GROUP BY
policy_id
)
SELECT
pol.policy_number,
pol.policy_id,
pol.policyholder_id,
pol.product_module_id,
json_extract_scalar (pol.module, '$.type') AS module_type,
cast(pol.start_date AS date) AS policy_start_date,
cast(pol.end_date AS date) AS policy_end_date,
pol.status,
pol.billing_day,
pol.monthly_premium / 100.0 AS monthly_premium,
pl.balance / 100.0 AS ledger_balance
FROM
policies pol
LEFT JOIN cte_ledger pl ON pl.policy_id = pol.policy_id
WHERE
pol.environment = 'production' -- Limits results to the production environment.
AND pl.balance < 0 -- Limits data returned to policies with balances less than zero.
ORDER BY
pl.policy_id
;
WITH cte_ledger_balance AS ( -- Creating a common table expression to simplify data in the main query.
SELECT
pol.policy_id,
json_extract_scalar(module, '$.type') AS module_type, -- Returns the module type extracted from the module object.
pol.currency,
pol.monthly_premium,
sum(coalesce(pl.amount, 0)) AS balance,
CASE WHEN pol.monthly_premium = 0 THEN
0
ELSE
(sum(cast(coalesce(pl.amount, 0) AS double)) / cast(coalesce(monthly_premium, 0) AS double))
END AS arrears_num,
(sum(coalesce(pl.amount, 0)) / coalesce(monthly_premium, 0))
FROM
policies pol
LEFT JOIN policy_ledger pl ON pl.policy_id = pol.policy_id
WHERE
pol.environment = 'production' -- Limits results to the production environment.
AND pol.status = 'active' -- Limits results to active policies.
GROUP BY -- Group like cases together.
pol.policy_id,
json_extract_scalar (module, '$.type'),
pol.currency,
pol.monthly_premium
),
cte_aging AS ( -- Creating a common table expression to simplify data in the main query.
SELECT
module_type,
CASE WHEN arrears_num >= 0 THEN
'paidUp'
ELSE
concat(cast(cast(- floor(arrears_num) AS int) AS varchar), '_month')
END AS arrears_month,
count(policy_id) AS policy_count,
currency,
sum(balance) AS total_amount_cents
FROM
cte_ledger_balance AS lb
GROUP BY
module_type,
CASE WHEN arrears_num >= 0 THEN
'paidUp'
ELSE
concat(cast(cast(- floor(arrears_num) AS int) AS varchar), '_month')
END,
currency
)
SELECT -- Main query.
* -- Returns everything from the cte_aging expression.
FROM
cte_aging
ORDER BY -- Orders the data based of the below-mentioned columns.
module_type,
length(arrears_month),
arrears_month;
Payments overview
Use this query to get an overview of all payments (premium collections) for policies issued under your organisation. Limit results to a given date range or payment status.
Failed payments
Use this query to investigate and analyse failed premium collections.
Blocked payment methods
Use this query to investigate and analyse blocked payment methods.
Pending refunds
Use this query to get a view of pending premium refunds, for example due to policies cancelled within the cooling off period or refunds generated manually by your tem on the dashboard
Ledger balances
Use this query to get a view on whether policies in your organisation are paid up or whether there are arrears premiums outstanding.
Policy aging
Use this query for a different view on the buildup of arrears premiums across policies.
Claims
For background on these queries, see the Claims guide.
SELECT
*
FROM
claims
WHERE
environment = 'production' -- Limits results to the production environment
ORDER BY created_at; -- Orders the results from oldest to newest created claim
SELECT
id AS claim_id,
cast(created_at AS date) AS created_at,
json_extract_scalar(created_by, '$.type') AS created_by_type, -- Extracts the type from the created_by object.
json_extract_scalar(created_by, '$.id') AS created_by_id, -- Extracts the type from the created_by object.
status AS claim_status,
type,
amount AS claim_amount, -- Returns an amount in cents.
description,
json_extract_scalar(linked_entities, '$.policyId') AS policy_id, -- Extracts the policy_id from the linked_entities object.
-- finalised_at, -- Commented out because it will be a null value if status is pending.
-- json_extract_scalar(finalised_by, '$.type') AS finalized_by_type, -- Commented out because it will be a null value if status is pending.
-- json_extract_scalar(finalised_by, '$.id') AS finalized_by_id, -- Commented out because it will be a null value if status is pending.
-- rejection_reason, -- Commented out because it will be a null value if status is pending.
-- proof_of_payment_id, -- Commented out because it will be a null value if status is pending.
action_date,
payee AS payee_details
FROM
payout_requests
WHERE
type = 'claim_payout'
AND status = 'pending'
AND environment = 'sandbox' -- Limits results to the production environment.
ORDER BY
created_at -- Orders the results from oldest to most recent claim request.
;
Claims overview
Use this query to get an overview of claims submitted against policies under your organisation
Pending claims
Use this query to get more details on claims awaiting resolution.
Policies
For background on these queries, see the Managing policies guide.
SELECT
*
FROM
policies
WHERE
environment = 'production' -- Limits results to the production environment
AND status = 'active' -- Limits results to active policies
ORDER BY
created_at; -- Orders the results from oldest to newest created policy
SELECT
pol.policy_number,
pol.policy_id,
pol.policyholder_id,
pol.product_module_id,
pol.package_name,
cast(pol.created_at AS date) AS created_at,
cast(pol.start_date AS date) AS policy_start_date,
cast(pol.end_date AS date) AS policy_end_date,
cast(pol.status_updated_at AS date) AS status_updated_at,
status,
pol.billing_day,
pol.billing_frequency,
pol.sum_assured / 100.0 AS sum_assured,
pol.monthly_premium / 100.0 AS monthly_premium,
sum(pl.amount)/100.0 as ledger_balance
FROM
policies pol
LEFT JOIN policy_ledger pl ON pl.policy_id = pol.policy_id
WHERE
pol.environment = 'production' -- Limits results to the production environment.
AND cast(pol.status_updated_at as date) >= CURRENT_DATE - interval '1' day -- Limits the results by only including policies lapsed in the last day.
AND pol.status IN ('lapsed', 'not_taken_up') -- Limits results to statuses of lapsed and not_taken_up.
GROUP BY
pol.policy_number,
pol.policy_id,
pol.policyholder_id,
pol.product_module_id,
pol.package_name,
cast(pol.created_at AS date),
cast(pol.start_date AS date),
cast(pol.end_date AS date),
cast(pol.status_updated_at AS date),
status,
pol.billing_day,
pol.billing_frequency,
pol.sum_assured / 100.0,
pol.monthly_premium / 100.0
;
WITH cte_ledger AS (
SELECT
pol.policy_id,
pol.policy_number,
pol.status,
pol.billing_day,
pol.monthly_premium,
sum(pl.amount) AS balance
FROM
policy_ledger pl
LEFT JOIN policies pol ON pol.policy_id = pl.policy_id
WHERE
pol.environment = 'production'
AND pol.status = 'active' -- Include only active policies
AND pol.monthly_premium != 0 -- Exclude policies with a zero monthly premium
GROUP BY
pol.policy_id,
pol.policy_number,
pol.status,
pol.monthly_premium,
pol.billing_day
)
SELECT
*
FROM
cte_ledger
WHERE
date_diff ('day', CURRENT_DATE, date_add ('day', billing_day, date_trunc('month', CURRENT_DATE) - interval '1' day)) = 10 -- Limits results to the set amount of days until the policy should be billed
AND balance / monthly_premium <= -2 -- Limits results to the set amount of premiums in arreas
;
SELECT
sequence,
persistence_key,
regexp_replace(persistence_key, '^.+-policy:', '') AS policy_id,
substr(persistence_key, strpos(persistence_key, ':') + 1, strpos(persistence_key, '-pol') - 5) AS organization_id, -- Includes ""dev_"" in the front of the org_id if entry is in Sandbox.
from_unixtime (created_at / 1000) AS created_at,
version,
data
FROM
policy_events
WHERE
persistence_key NOT LIKE '%dev%'
ORDER BY
sequence;
SELECT
date_format(date(created_at), 'yyyy-mm') AS created_period,
json_extract_scalar(module, '$.type') AS module_type,
status,
currency,
round(cast(sum(monthly_premium / 100.0) AS double), 2) AS sum_of_premium,
round(cast(sum(sum_assured / 100.0) AS double), 2) AS total_sum_assured
FROM
policies
WHERE
environment = 'production'
GROUP BY
date_format(date(created_at), 'yyyy-mm'),
json_extract_scalar (module, '$.type'),
status,
currency
ORDER BY
date_format(date(created_at), 'yyyy-mm'),
json_extract_scalar(module, '$.type'),
status
Policies overview
Use this query to get an overview of all policies issued under your organisation. You can limit results by policy status.
Lapsed / NTU
Use this query to find policies that recently lapsed or were marked "not taken up" (due to the first collection failing).
Policies expected to lapse
Use this query to find all policies that are expected to lapse by their next collection date.
Policy events
Get a handle on the events that make up the policy history, including all policy updates.
Policyholders & notifications
SELECT
*
FROM
policyholders
WHERE
environment = 'production' -- Limits results to the production environment
AND element_at(split (replace(replace(policy_ids, '[', ''), ']',''), ','), 1) != '' -- Limits results to policyholders that have a policy linked to them.
ORDER BY created_at; -- Orders the results from oldest to newest created policyholder.
SELECT
notification_id,
cast(created_at AS date) AS created_at,
json_extract_scalar (created_by, '$.type') AS created_by_type, -- Extracts the type from the created_at object.
channel,
notification_type,
json_extract_scalar (linked_entities, '$.policyId') AS policy_id, -- Extracts the policy_id from the linked_entities object.
json_extract_scalar (linked_entities, '$.policyholderID') AS policyholder_id, -- Extracts the policyholder_id from the linked_entities object.
json_extract_scalar (linked_entities, '$.claimId') AS claim_id, -- Extracts the claim_id from the linked_entities object.
data,
status,
failed_at,
failure_reason
FROM
notifications
WHERE
environment = 'production' -- Limits results to the production environment.
ORDER BY
created_at; -- Orders the results from oldest to most recent notification.
Policyholders
Get an overview of policyholders under your organisation, or segment policyholders by location or gender.
For further background, see the General settings guide and the Policyholders section of the API reference.
Notifications
Understand whether important notifications are reaching policyholders, and take action in the event of failed notifications.
For further background, see the Customer notifications overview guide and the Notifications section of the API reference.
Organisation set-up
SELECT
u.id AS user_id,
u.email,
u.state,
u.created_at,
our.organization_role_id,
orgr.name,
orgr.description
FROM
users u
LEFT JOIN organization_user_roles our ON our.user_id = u.id
LEFT JOIN organization_roles orgr ON orgr.organization_role_id = our.organization_role_id;
SELECT
product_module_definition_id,
product_module_id,
version_major,
version_minor,
created_at,
json_extract_scalar(created_by, '$.type') AS created_by_type, -- Extracts the type from the created_by object.
json_extract_scalar(created_by, '$.id') AS created_by_id, -- Extracts the type from the created_by object.
json_extract_scalar(published_by, '$.type') AS created_by_type, -- Extracts the type from the created_by object.
json_extract_scalar(published_by, '$.id') AS created_by_id, -- Extracts the type from the created_by object.
json_extract_scalar(json_extract(json_extract(settings, '$.billing'), '$.retries'), '$.retryFailedPayments') AS retryFailedPayments,
json_extract_scalar(json_extract(json_extract(settings, '$.billing'), '$.retries'), '$.daysBetweenFailedPaymentRetries') AS daysBetweenFailedPaymentRetries,
json_extract_scalar(json_extract(settings, '$.billing'), '$.currency') AS billing_currency,
json_extract_scalar(json_extract(settings, '$.billing'), '$.premiumType') AS billing_frequency,
json_extract(json_extract(settings, '$.billing'), '$.paymentMethodTypes') AS paymentMethodTypes,
json_extract_scalar(json_extract(json_extract(settings, '$.billing'), '$.primaryMethod'), '$.type') AS primaryMethodType,
json_extract_scalar(json_extract(settings, '$.billing'), '$.proRataMinimum') AS proRataMinimum,
json_extract_scalar(json_extract(settings, '$.billing'), '$.billBeforeWeekend') AS billBeforeWeekend,
json_extract_scalar(json_extract(settings, '$.billing'), '$.allowStartDateChange') AS allowStartDateChange,
json_extract_scalar(json_extract(settings, '$.billing'), '$.naedoArrearsPolicies') AS naedoArrearsPolicies,
json_extract_scalar(json_extract(settings, '$.billing'), '$.proRataBillingOnIssue') AS proRataBillingOnIssue,
json_extract_scalar(json_extract(settings, '$.billing'), '$.enableBillingOnSandbox') AS enableBillingOnSandbox,
json_extract_scalar(json_extract(settings, '$.billing'), '$.proRataBillingDisabled') AS proRataBillingDisabled,
json_extract_scalar(json_extract(settings, '$.billing'), '$.paymentSubmissionLeadTime') AS paymentSubmissionLeadTime,
json_extract_scalar(json_extract(settings, '$.billing'), '$.shouldCreateExternalPayments') AS shouldCreateExternalPayments,
json_extract_scalar(json_extract(settings, '$.billing'), '$.consecutiveFailedPaymentsAllowed') AS consecutiveFailedPaymentsAllowed
FROM
product_module_definitions
-- WHERE product_module_id = 'enter product_module_id here' -- Can be included to only extract the billing settings from a single product module.
ORDER BY
product_module_id,
version_major,
version_minor
;
SELECT
pmc.payment_method_config_id,
pmc.payment_method_type,
pmc.provider_id,
pmp.name AS provider_name,
pmp.description AS provider_description,
pmp.provider_type,
pmc.payment_method_config_key,
pmc.is_default,
pmp.is_default_for_new_orgs,
cast(pmc.created_at AS date) AS created_at,
cast(pmc.updated_at AS date) AS updated_at,
pmc.product_module_id,
pmc.billing_strategy_settings,
pmc.config AS payment_method_configuration,
pmp.config AS provider_configuration
FROM
payment_method_configs AS pmc
LEFT JOIN payment_method_providers AS pmp ON pmp.payment_method_provider_id = pmc.provider_id -- Joining an additional table to include more relevant data.
WHERE
pmc.environment = 'production' -- Limits results to the production environment.
ORDER BY
pmp.provider_type -- Orders the results from alphabetically using the provider type.
;
Users
Understand which users operate on Root on behalf of your organisation together with their roles and permissions. For further background, see the User management section of the help centre.
Billing settings
Get the billing configs that apply to collections for different product modules. For more details see the Billing settings guide and the Billing & payments guides.
Payment provider config
Get the config for different payment providers enabled for your organisation. For further background see the Billing & payments section.
Regulatory
SELECT
pol.policy_number,
pol.status AS policy_status,
pay.action_date AS payment_date,
pay.finalized_at AS finalized_date,
pay.status AS payment_status,
cast(pol.start_date AS date) AS policy_start_date,
date_diff('year', pol.start_date, CURRENT_DATE) AS policy_age,
json_extract_scalar(pol.module, '$.type') AS module_type,
date_diff('year', ph.date_of_birth, CURRENT_DATE) AS policyholder_age,
pay.description AS payment_description,
pol.monthly_premium / 100.0 AS monthly_premium, -- Returns an amount that has been converted from cents.
pay.amount / 100.0 AS amount_collected -- Returns an amount that has been converted from cents.
FROM
payments pay
LEFT JOIN policies pol ON pay.policy_id = pol.policy_id
LEFT JOIN policyholders ph ON pol.policyholder_id = ph.policyholder_id
WHERE
pay.environment = 'production' -- Limits results to the production environment.
AND pay.status = 'successful' -- Limits results to only successful payments.
AND pay.finalized_at >= CURRENT_DATE - INTERVAL '1' month -- Limits the results by only including a finalized_at date of within the last month.
;
SELECT
c.claim_number,
c.claim_id,
cast(c.created_at AS date) AS claim_created_at,
cast(c.updated_at AS date) AS claim_updated_at,
c.status AS claim_status,
c.approval_status,
c.policyholder_id,
c.policy_id,
json_extract_scalar (pol.module, '$.type') AS module_type,
pol.policy_number,
pol.status AS policy_status,
cast(pol.start_date AS date) AS policy_start_date,
date_diff ('year', pol.start_date, c.created_at) AS policy_age_at_claim_creation,
date_diff ('year', ph.date_of_birth, c.created_at) AS policyholder_age_at_claim_creation,
max(cast(pay.payment_date AS timestamp)) FILTER (WHERE pay.status = 'successful') AS last_successful_payment,
c.claimant AS claimant_details,
c.incident_date,
c.incident_type,
c.incident_cause,
c.requested_amount / 100.0 AS requested_amount,
c.granted_amount / 100.0 AS granted_amount,
c.rejection_reason
FROM
claims c
LEFT JOIN policies pol ON c.policy_id = pol.policy_id
LEFT JOIN policyholders ph ON c.policyholder_id = ph.policyholder_id
LEFT JOIN payments pay ON pay.policy_id = c.policy_id
WHERE
c.environment = 'production' -- Limits results to the production environment.
GROUP BY
c.claim_number,
c.claim_id,
cast(c.created_at AS date),
cast(c.updated_at AS date),
c.status,
c.approval_status,
c.policyholder_id,
c.policy_id,
json_extract_scalar (pol.module, '$.type'),
pol.policy_number,
pol.status,
cast(pol.start_date AS date),
date_diff ('year', pol.start_date, c.created_at),
date_diff ('year', ph.date_of_birth, c.created_at),
c.claimant,
c.incident_date,
c.incident_type,
c.incident_cause,
c.requested_amount,
c.granted_amount,
c.rejection_reason;
Payments bordereaux
Captures premium collection and policy status data for submission to regulators and re-insurers.
Claims bordereaux
Captures claims data for submission to regulators and re-insurers.
Updated 4 months ago