Skip to main content

Example Analytics Queries

BETA: The reporting database is currently in beta and may still evolve. It is available by opt-in only. To join the beta or ask questions, contact support@azumuta.com.

Before You Begin

After you connect, select your workspace schema. Replace company_<your-workspace-id> with your configured schema name. Set the session time zone to UTC for consistent monthly totals.

set search_path to "company_<your-workspace-id>";
set time zone 'UTC';

When a sync detects a deleted source record, it removes the corresponding reporting rows. Archived records can remain. Older databases can retain a deleted_at column, but the current sync does not maintain it. These queries do not use that column. See Deletions and other conventions.

Recordings by Status

Count recordings by stored status. Only recordings with an instruction version are included. These can include planned audits that have not started. See Audit recordings in the reporting database.

select status, count(*) as recordings
from recording
group by status
order by recordings desc;

Issues by Board Column

Count Continuous Improvement issues by board and column. Group by the identifiers to keep columns with the same name separate. This includes archived issues. To exclude archived issues, add and archived_at is null to the filter.

select board_id, column_id, column_name, count(*) as issues
from issue
where board_type = 'continuous-improvement'
group by board_id, column_id, column_name
order by issues desc;

Issues Created per Month

Count issues by creation month in UTC. This includes all board types and archived issues that remain in the reporting database.

select date_trunc('month', created_at) as month,
       count(*) as issues
from issue
where 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 seconds. This query excludes recordings without a duration. It shows the average for the 20 work instructions with the most remaining recordings. It does not filter by status or recording type.

select workinstruction_id,
       count(*) as recordings,
       round(avg(actual_duration)::numeric, 1) as avg_seconds
from recording
where actual_duration is not null
group by workinstruction_id
order by recordings desc
limit 20;

Count order items associated with a zone

Counting unit: one product-order work instruction item. Replace <zone-id> with the zone’s identifier. This includes all association types and retains queued items without recordings. It does not filter order or recording status.

select count(*) as order_items
from product_order_workinstruction as item
where exists (
    select 1
    from product_order_workinstruction_spot as zone
    where zone.product_order_workinstruction_id = item.id
      and zone.spot_id = '<zone-id>'
);

EXISTS checks for an association without adding rows to the count. No recording join is required. One item can relate to several zones, so counts for different zones can overlap. Do not add those counts to calculate a workspace total. See Zone associations and assignment for source priority and reassignment limits.

Count recordings worked on by each operator

Counting unit: one distinct recording per operator. This query includes normal step visits with a user ID. It excludes double-check visits and does not filter recording status or type.

select user_id,
       count(distinct recording_id) as recordings
from instruction_visit
where type = 'normal'
  and user_id is not null
group by user_id
order by recordings desc, user_id;

Repeated visits do not increase an operator’s count for the same recording. Shared recordings count for multiple operators, so operator totals can overlap. An open visit may not have a row yet. This measures recorded participation, not assignment or successful completion. See Planned users and actual operators.

Compare applicable steps with recorded answers

Output unit: one applicable step per recording. Start from recording_step.active = true to exclude steps that do not apply to the selected variants. Left join normal-visit totals by both recording and step to retain steps without totals.

select step.recording_id,
       step.instruction_step_id,
       totals.visits,
       totals.answer_attempts,
       totals.final_answer,
       case
           when totals.id is null then 'No normal-visit totals'
           when totals.final_answer is null then 'No recorded answer'
           else 'Answer recorded'
       end as answer_evidence
from recording_step as step
left join instruction_total_visit as totals
    on totals.recording_id = step.recording_id
   and totals.instruction_step_id = step.instruction_step_id
   and totals.type = 'normal'
where step.active = true
order by step.recording_id, step.instruction_step_id;

The query includes all recording statuses and types that have step rows, including unfinished and cancelled work. It excludes double-check totals. Keep the type condition in the join: a filter in WHERE would remove steps without totals.

The labels describe evidence, not pass or fail. Some steps require no answer. An open visit or a blocked check attempt can also explain a missing answer. NULL totals mean no matching row, not zero attempts. See Visits, attempts, and applicable steps before calculating an answer-completion rate.

Tips

  • Join related identifiers. For example, recording.workinstruction_id matches workinstruction.id.
  • Use the built-in column descriptions to check field meanings and units.
  • Aggregate and filter in SQL before you load data into your BI tool to reduce the amount of data transferred.