Example Queries

Practical SQL query examples that combine multiple reporting views for common analytics use cases.

This page provides ready-to-use SQL queries for common reporting scenarios. Each query combines multiple views and can be adapted to your specific needs. All queries use the reporting schema.

These queries are illustrative. Your label categories, custom fields, and workflow statuses may differ. Adjust filter values to match your configuration.

---

Operational overview

Daily issue summary by category and priority

SELECT
    i.category_name,
    i.priority,
    COUNT(*) AS issues_today,
    COUNT(*) FILTER (WHERE i.current_status = 'closed') AS closed_today
FROM reporting.issues i
WHERE i.created_at::date = CURRENT_DATE
GROUP BY i.category_name, i.priority
ORDER BY issues_today DESC;

Open actions past due date

SELECT
    a.action_number,
    a.title,
    a.due_date,
    a.current_status,
    u.first_name || ' ' || u.last_name AS assignee
FROM reporting.actions a
LEFT JOIN reporting.users u
    ON a.assignee_type = 'user'
    AND a.assignee_id = u.user_id
WHERE a.current_status NOT IN ('completed', 'cancelled')
    AND a.due_date < NOW()
ORDER BY a.due_date ASC;

---

Checklists & quality

Checklist completion rate by equipment area (last 30 days)

SELECT
    el.label_value AS area,
    cl.checklist_name,
    COUNT(*) AS total,
    COUNT(cl.closed_at) AS completed,
    ROUND(100.0 * COUNT(cl.closed_at) / COUNT(*), 1) AS completion_pct
FROM reporting.checklists cl
JOIN reporting.checks c ON cl.checklist_id = c.checklist_id
JOIN reporting.equipment_labels el
    ON c.equipment_id = el.equipment_id
    AND el.label_category = 'Area'
WHERE cl.created_at >= NOW() - INTERVAL '30 days'
GROUP BY el.label_value, cl.checklist_name
ORDER BY completion_pct ASC;

Non-compliant checks that triggered issues

SELECT
    c.check_name,
    c.value_string,
    e.name AS equipment,
    i.issue_number,
    i.title AS issue_title,
    i.current_status AS issue_status
FROM reporting.check_issues ci
JOIN reporting.checks c USING (check_id)
JOIN reporting.issues i USING (issue_id)
LEFT JOIN reporting.equipment e ON c.equipment_id = e.equipment_id
WHERE c.value_is_compliant = false
ORDER BY ci.linked_at DESC
LIMIT 50;

---

Issue resolution & root cause

Average time in each workflow status (last 90 days)

SELECT
    transition AS status,
    COUNT(*) AS times_entered,
    AVG(
        EXTRACT(EPOCH FROM (COALESCE(until, NOW()) - "from")) / 3600
    ) AS avg_hours_in_status
FROM reporting.issues_transitions it
JOIN reporting.issues i USING (issue_id)
WHERE i.created_at >= NOW() - INTERVAL '90 days'
GROUP BY transition
ORDER BY avg_hours_in_status DESC;

Issues with root cause analysis — completion funnel

SELECT
    COUNT(DISTINCT i.issue_id) AS total_issues_requiring_rca,
    COUNT(DISTINCT iww.analysis_id) AS analyses_started,
    COUNT(DISTINCT iww.analysis_id) FILTER (
        WHERE ww.status = 'completed'
    ) AS analyses_completed,
    COUNT(DISTINCT w.why_id) FILTER (
        WHERE w.is_root_cause = true
    ) AS root_causes_identified
FROM reporting.issues i
LEFT JOIN reporting.issues_why_why_analyses iww
    ON i.issue_id = iww.issue_id AND iww.is_deleted = false
LEFT JOIN reporting.why_why_analyses ww
    ON iww.analysis_id = ww.analysis_id AND ww.is_deleted = false
LEFT JOIN reporting.why_why_analyses_whys w
    ON ww.analysis_id = w.analysis_id AND w.is_deleted = false
WHERE i.root_cause_analysis_required = true;

---

Training & compliance

Skill gap overview by team

SELECT
    t.name AS team,
    COUNT(DISTINCT smn.user_id) AS employees,
    COUNT(DISTINCT (smn.user_id, smn.skill_id)) AS total_requirements,
    COUNT(DISTINCT (smn.user_id, smn.skill_id)) FILTER (
        WHERE q.qualification_id IS NOT NULL
    ) AS qualified,
    ROUND(100.0 *
        COUNT(DISTINCT (smn.user_id, smn.skill_id)) FILTER (
            WHERE q.qualification_id IS NOT NULL
        ) /
        NULLIF(COUNT(DISTINCT (smn.user_id, smn.skill_id)), 0)
    , 1) AS compliance_pct
FROM reporting.skill_matrix_need smn
JOIN reporting.lms_position_enrollments pe USING (user_id)
JOIN reporting.lms_positions_teams pt USING (position_id)
JOIN reporting.lms_teams t USING (team_id)
LEFT JOIN reporting.lms_qualifications q
    ON smn.user_id = q.user_id
    AND smn.skill_id = q.skill_id
    AND q.deleted_at IS NULL
    AND (q.expires_at IS NULL OR q.expires_at > NOW())
WHERE smn.optional = false
    AND pe.deleted_at IS NULL
    AND pt.deleted_at IS NULL
GROUP BY t.name
ORDER BY compliance_pct ASC;

Training hours per department (this year)

SELECT
    ul.label_value AS department,
    COUNT(DISTINCT q.user_id) AS trained_employees,
    SUM(q.duration_in_minutes) / 60.0 AS total_training_hours,
    ROUND(SUM(q.duration_in_minutes) / 60.0 / NULLIF(COUNT(DISTINCT q.user_id), 0), 1)
        AS avg_hours_per_employee
FROM reporting.lms_qualifications q
JOIN reporting.users_labels ul
    ON q.user_id = ul.user_id
    AND ul.label_category = 'Department'
WHERE q.acquired_at >= DATE_TRUNC('year', NOW())
    AND q.deleted_at IS NULL
    AND q.duration_in_minutes IS NOT NULL
GROUP BY ul.label_value
ORDER BY total_training_hours DESC;

---

Safety (BOS)

BOS observation trend by week

SELECT
    DATE_TRUNC('week', created_at)::date AS week,
    COUNT(*) AS total,
    COUNT(*) FILTER (WHERE reacted_positive_or_negative = 'positive') AS positive,
    COUNT(*) FILTER (WHERE reacted_positive_or_negative = 'negative') AS negative
FROM reporting.bos_entries
WHERE created_at >= NOW() - INTERVAL '6 months'
GROUP BY week
ORDER BY week;

---

Production

Production order yield with traceability non-conformances

SELECT
    po.production_order_number,
    po.sku_name,
    po.production_line,
    po.target_quantity,
    po.actual_quantity,
    ROUND(100.0 * po.actual_quantity / NULLIF(po.target_quantity, 0), 1) AS yield_pct,
    COUNT(t.traca_id) AS traca_checks,
    COUNT(t.traca_id) FILTER (WHERE t.conform = false) AS traca_fails
FROM reporting.production_orders po
LEFT JOIN reporting.traca t USING (production_order_number)
WHERE po.actual_end IS NOT NULL
    AND po.actual_end >= NOW() - INTERVAL '30 days'
GROUP BY po.production_order_number, po.sku_name, po.production_line,
         po.target_quantity, po.actual_quantity
ORDER BY po.actual_end DESC;