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_by JSON field on the payments table, go to the List policy payments endpoint and expand the 200 Success response to see a schema of the payment object.

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 _view
The name of any view created on the Root Data adapter must end with _view. For example, dinosure_policy_view or my_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_id from the persistence key and giving it an alias.
Extracting event type and 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.

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.

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;

Claims

For background on these queries, see the Claims guide.

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.

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.
        ;

Policies

For background on these queries, see the Managing policies guide.

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.

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

Policyholders & notifications

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.

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.

Organisation set-up

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.

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.
;

Regulatory

Payments bordereaux

Captures premium collection and policy status data for submission to regulators, underwriters and re-insurers.

Claims bordereaux

Captures claims data for submission to regulators, underwriters and re-insurers.

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;