Beta. The reporting database is currently in beta; tables and columns used in these examples may still change.
Before You Begin
These examples assume you've connected and pointed your session at your workspace schema:
set search_path to "company_<your-workspace-id>";
Each example filters out deleted rows with where deleted_at is null so you only see current data. Remove that filter if you specifically want to include deleted records.
Recordings by Status
How many execution sessions are in each status:
select status, count(*) as recordings
from recording
where deleted_at is null
group by status
order by recordings desc;
Issues by Board Column
Where your continuous-improvement issues currently sit:
select column_name, count(*) as issues
from issue
where deleted_at is null
group by column_name
order by issues desc;
Issues Created per Month
A simple trend of new issues over time:
select date_trunc('month', created_at) as month,
count(*) as issues
from issue
where deleted_at is null
and created_at is not null
group by month
order by month;
Average Active Time per Work Instruction
actual_duration is the active working time of a recording, in milliseconds. This shows the average in seconds for the busiest work instructions:
select workinstruction_id,
count(*) as recordings,
round(avg(actual_duration) / 1000.0, 1) as avg_seconds
from recording
where deleted_at is null
and actual_duration is not null
group by workinstruction_id
order by recordings desc
limit 20;
Tips
- Join tables on their id columns — for example,
recording.workinstruction_idmatchesworkinstruction.workinstruction_id. - Use the built-in column descriptions when you're unsure what a field means.
- Aggregate and filter in SQL before pulling data into your BI tool — it's faster and lighter.