Querying your data (SQL)

Once data is in an artifact source, queries turn it into the numbers behind your dashboards and role metrics. Each source behaves like a table you can select from, named by the source’s Reference ID, with the columns you defined.

The dashboard widget editor: a live line chart preview of a monthly figure beside the query, written in plain English. — A query powers each chart widget. Describe it in English or write the SQL, then preview the chart.

Query in English or SQL

You don’t need to know SQL to query your data. Wherever you write a query (a dashboard widget or a role metric), you can work in two modes:

English mode: describe what you want in plain language and the query is drafted for you. The drafting knows your sources’ tables and columns, so the result usually just works.
SQL mode: write or fine-tune the query yourself.

The two stay in sync, so a good way to work is to start in English to get a correct query fast, then switch to SQL to adjust it if you want more control. You can move between the two at any time.

Billable hours per month, broken down by service

A safe subset of SQL

Queries are read-only SQL, a deliberately limited subset of PostgreSQL:

SELECT only: no inserting, updating, or deleting data.
An approved list of functions and casts: the common ones for reporting, such as aggregate, window, date, string, math and JSON functions.
Your organization’s data only: a query never sees another organization’s data.
A time limit: a query that runs too long is stopped.

It’s safe to let anyone explore data with a query.

Variables

A query can take variables (:name placeholders you fill in at run time) so one query answers many questions:

SELECT session_date AS date, count(*) AS sessions
FROM sessions
WHERE status = 'completed'
AND session_date >= :start_date::date
GROUP BY 1
ORDER BY 1

Dashboards supply variables through interactive controls (a date picker, a team selector, and so on). See Dashboards.
Role metrics must include a :staff_id variable; the query runs per person, filling in each staff member’s ID. See Role metrics.

Examples

A grouped count for a dashboard chart:

Count of sessions by status

A per-person time series for a role metric (date + value, scoped by :staff_id), here an RBT’s treatment-fidelity checks:

Average treatment-fidelity score per day for :staff_id