Overview

Amazon Athena allows users to query data using a subset of standard SQL syntax. Read more about the statements, functions, operators, and data types supported by Athena in their SQL reference.

These are likely to be the most useful guides for most users:

• DML statements guide - Explains how you can extract and manipulate data.
• SQL functions guide - Lists the supported SQL functions.

To help you get started, this guide provides examples of some of the most important SQL statements that you can use to query your data on Root.

Dates

All dates, excluding dates stored in JSON objects, are stored as a TIMESTAMP data type in the UTC timezone. Check out the date and time functions guide on AWS for the full list of available functions.

Timezones

All standard dates are in the UTC timezone. To determine the date in a different timezone you can convert the date using a UTC offset. To do this you can use the at_timezone function.

SELECT
    policy_number,
    at_timezone(start_date, 'UTC+2') AS "start_date",
    at_timezone(created_at, 'UTC+2') AS "created_at"
FROM
    policies;

Intervals

Timestamps allow you to perform operations like adding and subtracting time, which is useful when you want to return results based on a date condition (e.g. for a specific period).

To add or subtract time from a timestamp you can use the INTERVAL statement.

SELECT
    policy_number,
    start_date,
    created_at
FROM
    policies
WHERE 
    start_date > now() - INTERVAL '30' DAY;

Dates included in JSON objects

Dates included in JSON objects are in the ISO 8601 format and need to be parsed into a TIMESTAMP data type before using the date and time functions on them. To parse the date you can use the from_iso8601_timestamp function .

select
    from_iso8601_timestamp(json_extract_scalar(module,
    '$.module_data_date')) as module_data_date
from
    policies

JSON objects

JSON objects are saved as a string (VARCHAR) on tables. You can extract data from and find values in JSON objects.

Extracting properties

To extract properties from a JSON object or array you can use the json_extract function. The json_extract function searches the JSON string using a JSON path-like expression using dot . and [x] notation, similar to how you would access properties in JavaScript objects or arrays.

SELECT
    policy_number,
    json_extract(module,'$.type') as type,
    json_extract(module,'$.charges[0].description') as charges_description,
    json_extract(module,'$.charges[0].amount') as charges_amount
FROM
    policies

Working with extracted properties

When you use the json_extract function, the extracted data is returned as a JSON string. To perform additional tasks on the returned data you need to convert it to a different data type.

One option is to use the json_extract_scalar function which returns the extracted data as a VARCHAR type. The selected property must be a scalar value (boolean, number or string) for the function to work.

SELECT
    policy_number,
    json_extract(module,'$.charges[0].description') as charges_description,
    json_extract(module,'$.charges[0].amount') as charges_amount
FROM
    policies
WHERE
    json_extract_scalar(module, '$.type') = 'template_funeral';

Another option is to cast the data to a different type after you have extracted it using the json_extract function.

SELECT
    policy_number,
    json_extract(module,'$.charges[0].description') as charges_description,
    json_extract(module,'$.charges[0].amount') as charges_amount
FROM
    policies
WHERE
    CAST(json_extract(module, '$.type') AS VARCHAR) = 'template_funeral';

Join tables

Data related to a particular entity can be distributed across multiple tables. For example, policy attachments are stored in the attachments table. You can use standard SQL JOIN statements to combine related data across different tables.

SELECT
    pol.policy_number,
    att."type",
    att.filename,
    att.url
FROM
    policies pol
JOIN
    attachments att ON pol.policy_id = att.resource_id
WHERE
    pol.status = 'active'

Miscellaneous examples

Return the top 10 agents (dashboard users), by number of policies issued, over the past 30 days.

SELECT
    u.first_name,
    u.last_name,
    u.email,
    count(*)
FROM
    policies p
JOIN 
    users u ON u.id = cast(json_extract(p.created_by, '$.id') as varchar)
WHERE
    p.created_at > now() - interval '30' day
    AND p.environment = 'production'
GROUP BY
    u.first_name,
    u.last_name,
    u.email
ORDER BY
    count(*) DESC
LIMIT 10;

Return blocked payment methods for policies issued in the past 30 days.

SELECT
    pm.*
FROM
    policies p
LEFT JOIN payment_methods pm ON
    p.payment_method_id = pm.payment_method_id
WHERE
    p.environment = 'production'
    AND p.status = 'active'
  AND p.created_at > now() - interval '30' day
    AND pm.blocked_reason IS NOT NULL;

Determine in which areas policyholders live

SELECT
    country,
    city,
    suburb,
    area_code,
    count(*)
FROM
    policyholders
WHERE
    environment = 'production'
GROUP BY
    country,
    city,
    suburb,
    area_code
ORDER BY
    country ASC;