Skip to content
Admire Help

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, with the columns you defined.

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

Query in English or SQL

Section titled “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 Admire drafts the query for you. It 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.

Closed deals per month, broken down by stage

A safe subset of SQL

Section titled “A safe subset of SQL”

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

  • SELECT only: no inserting, updating, or deleting data.
  • A whitelist of functions and casts: the common ones for reporting (aggregates, dates, string and math functions) are available.
  • Org-scoped automatically: a query only ever sees your own organization’s data, and runs under a short time limit so a heavy query can’t slow things down.

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

Variables

Section titled “Variables”

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

SELECT created_at::date AS date, count(*) AS prs
FROM github_prs
WHERE created_at > :start_date::timestamptz
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; Admire runs the query per person, filling in each staff member’s ID. See Role metrics.

Examples

Section titled “Examples”

A grouped count for a dashboard chart:

Count of pull requests by state

A per-person time series for a role metric (date + value, scoped by :staff_id):

Tickets closed per day by :staff_id