Advanced Reports - SQL Query Examples

This page provides practical examples of SQL queries you can use with the Advanced Reports app, based on the database schema generated from your Jira data.

Refer to the Local Insights page for a detailed description of the available tables and views like work_items, work_items_display, histories, statuses, users, etc.

Basic Queries

Count Work Items by Status

This query uses the work_items_display view to count how many work items exist for each status name.

Example Result:

status	work_item_count
In Progress	45
To Do	32
Done	128
In Review	18

-- Note: Ensure the 'status' field is selected in the Database Configuration
SELECT
    status,
    COUNT(*) AS work_item_count
FROM work_items_display
GROUP BY status
ORDER BY work_item_count DESC;

List Work Items Assigned to a Specific User

Replace 'Display Name of User' with the actual display name.

Example Result:

key	issuetype	status
PROJ-123	Story	In Progress
PROJ-456	Bug	To Do
PROJ-789	Task	In Review

-- Note: Ensure the 'issuetype', 'status' fields are selected in the Database Configuration
SELECT
    "key",
    issuetype,
    status
FROM work_items_display
WHERE assignee = 'Display Name of User';

Find Work Items Created in the Last 30 Days

This query uses the created field (assuming it’s configured and available in the work_items view) to find recently created work items.

Example Result:

key	issuetype	status	created
PROJ-901	Story	To Do	2025-05-25 09:15:00
PROJ-902	Bug	In Progress	2025-05-28 14:30:00
PROJ-903	Task	Done	2025-06-01 11:45:00

-- Note: Ensure the 'created', 'issuetype', 'status' fields are selected in the Database Configuration
SELECT
    "key",
    issuetype,
    status,
    created
FROM work_items
WHERE created >= current_date - INTERVAL '30' DAY;

History-Based Queries (Time in Status)

These queries require the “Include historical data (changelog)” option to be enabled in the Database Configuration.

The key to all Time in Status analysis is the seconds_in_state column of the histories table — it records how long a work item held a given field value (see the database schema). Filtering on field_id = 'status' gives you the time spent in each status, which you can aggregate any way you like: per work item, per status, per assignee, per work item type, or as percentiles. See also the Bottleneck Detection example further down for percentile-based analysis.

For a full guide to status-time analysis — including time in the current status, stuck-item detection, and cycle time from status categories — see the dedicated Time in Status page.

Calculate Average Time Spent in ‘In Progress’ Status (in Days)

This query uses the histories table and joins with statuses to filter by status name. It calculates the average duration work items spent in the ‘In Progress’ status.

Example Result:

avg_days_in_progress
3.45

-- Note: Ensure the 'status' field is selected in the Database Configuration
SELECT
    AVG(h.seconds_in_state) / (60*60*24.0) AS avg_days_in_progress
FROM histories h
JOIN statuses s ON h.field_id = 'status' AND h.value = s.id
WHERE s.name = 'In Progress';

Total Time Spent by Each Work Item in Each Status (in Hours)

This query groups by work item key and status name to show the total time each work item spent in every status it transitioned through.

This query uses the histories table, so make sure “Include work item histories for selected fields” is enabled in the Database Configuration.

Example Result:

work_item_key	status_name	total_hours_in_status
PROJ-123	In Progress	72.5
PROJ-123	In Review	24.0
PROJ-123	To Do	8.5
PROJ-456	Done	96.0

-- Note: Ensure the 'status' field is selected in the Database Configuration
SELECT
    i."key" AS work_item_key,
    s.name AS status_name,
    SUM(h.seconds_in_state) / (60*60.0) AS total_hours_in_status
FROM histories h
JOIN work_items i ON h.work_item_id = i.id
JOIN statuses s ON h.field_id = 'status' AND h.value = s.id
GROUP BY i."key", s.name
ORDER BY i."key", total_hours_in_status DESC;

Time in Status Report (Statuses as Columns)

A classic “Time in Status” report: one row per work item, one column per status, with the number of days spent in each. This uses DuckDB’s PIVOT statement, which creates the status columns dynamically — no need to list your statuses by hand.

Example Result:

work_item_key	To Do	In Progress	In Review	Done
PROJ-123	1.5	4.2	1.0	12.3
PROJ-456	0.3	8.7	2.5	5.1
PROJ-789	6.0	2.1	0.5	1.8

-- Note: Ensure the 'status' field is selected in the Database Configuration
PIVOT (
    SELECT
        i."key" AS work_item_key,
        s.name AS status_name,
        h.seconds_in_state / 86400.0 AS days_in_status
    FROM histories h
    JOIN work_items i ON h.work_item_id = i.id
    JOIN statuses s ON h.field_id = 'status' AND h.value = s.id
)
ON status_name
USING ROUND(SUM(days_in_status), 1)
GROUP BY work_item_key
ORDER BY work_item_key;

Average Time in Status by Work Item Type

Compare how long different work item types (Bug, Story, Task, …) spend in each status. This is a quick way to spot, for example, that bugs linger in review far longer than stories.

Example Result:

work_item_type	status_name	avg_days	median_days	items
Bug	In Review	4.8	3.2	34
Story	In Progress	3.9	2.7	58
Bug	In Progress	2.6	1.9	41
Story	In Review	1.4	1.1	52

-- Note: Ensure the 'status' and 'issuetype' fields are selected in the Database Configuration
SELECT
    it.name AS work_item_type,
    s.name AS status_name,
    ROUND(AVG(h.seconds_in_state) / 86400.0, 1) AS avg_days,
    ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY h.seconds_in_state) / 86400.0, 1) AS median_days,
    COUNT(DISTINCT h.work_item_id) AS items
FROM histories h
JOIN work_items i ON h.work_item_id = i.id
JOIN issuetypes it ON it.id = i.issuetype
JOIN statuses s ON h.field_id = 'status' AND h.value = s.id
GROUP BY it.name, s.name
ORDER BY avg_days DESC;

Joining Tables

List Work Items with Assignee Email

This query joins work_items_display with the users table to retrieve the email address associated with the assignee. It assumes the user email is stored within the data JSON field of the users table.

Example Result:

key	status	assignee_display_name	assignee_email
PROJ-123	In Progress	John Smith	john.smith@company.com
PROJ-456	To Do	Jane Doe	jane.doe@company.com
PROJ-789	Done	Mike Johnson	mike.johnson@company.com

-- Note: Ensure the 'status', 'assignee' fields are selected in the Database Configuration
SELECT
    i."key",
    i.status,
    i.assignee AS assignee_display_name,
    u.emailAddress AS assignee_email
FROM work_items_display i
LEFT JOIN users u ON i.assignee = u.displayName
LIMIT 100;

Time-based Analysis

Cycle Time Analysis by Team and Work Item Type

Show the average cycle time for work items by assignee and work item type, including median cycle time and total items.

Required fields: assignee, issuetype, resolutiondate, created.

Example Result:

assignee	work_item_type	avg_cycle_time	median_cycle_time	total_items
John Smith	Bug	8.5	6.0	12
Jane Doe	Story	12.3	10.5	25
Mike Johnson	Task	4.2	3.5	18

SELECT
    users.displayName as assignee,
    issuetypes.name as work_item_type,
    AVG(cycle_time_days) as avg_cycle_time,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY cycle_time_days) as median_cycle_time,
    COUNT(*) as total_items
FROM (
    SELECT
        wi.assignee,
        wi.issuetype,
        (EXTRACT(EPOCH FROM wi.resolutiondate - wi.created) / 86400) as cycle_time_days
    FROM work_items wi
    WHERE wi.resolutiondate IS NOT NULL
) cycle_data
LEFT JOIN users ON users.id = cycle_data.assignee
LEFT JOIN issuetypes ON issuetypes.id = cycle_data.issuetype
GROUP BY users.displayName, work_item_type
ORDER BY avg_cycle_time DESC;

Historical Trend Analysis

Sprint Velocity Trends with Predictive Insights

This query helps you understand how much work your team completes in each ‘sprint’ (a short work cycle). It calculates the ‘velocity’ for each sprint, which is the total amount of ‘Story Points’ from tasks that were finished and marked as resolved within that sprint.

It also compares each sprint’s velocity to the one before it, showing you how much the team’s speed has changed. Finally, it provides a ‘rolling average’ of the last three sprints’ velocities, giving you a smoother picture of the team’s typical performance over time, rather than just looking at individual sprints.

Required fields: resolutiondate.

Required custom fields: {{sprint_customfield}} (Sprint), {{story_points_customfield}} (Story Points). Include Sprint and Story Points in the Database Configuration, and substitute the custom field IDs with the actual ones from your Jira instance (e.g. customfield_10004 for Sprint and customfield_10009 for Story Points).

Example Result:

sprint name	velocity	prev velocity	velocity change percent	rolling avg velocity
Sprint 15	42	38	10.53	40.0
Sprint 16	35	42	-16.67	38.3
Sprint 17	48	35	37.14	41.7
Sprint 18	44	48	-8.33	42.3

WITH completed_sprint_items AS (
    SELECT
        s.id AS sprint_id,
        s.name AS sprint_name,
        s.startDate AS sprint_start_date,
        s.endDate AS sprint_end_date,
        wi.{{story_points_customfield}} AS story_points
    FROM
        work_items AS wi
    JOIN
        sprints AS s ON s.id = wi.{{sprint_customfield}}[1]
    WHERE
        wi.resolutiondate IS NOT NULL
        AND wi.resolutiondate <= s.endDate
        AND wi.{{story_points_customfield}} IS NOT NULL
        AND wi.{{story_points_customfield}} > 0
),
sprint_velocity AS (
    SELECT
        sprint_id,
        sprint_name,
        sprint_start_date,
        SUM(story_points) AS velocity,
        COUNT(*) AS stories_completed,
        LAG(SUM(story_points), 1) OVER (ORDER BY sprint_start_date) AS prev_velocity
    FROM
        completed_sprint_items
    GROUP BY
        sprint_id, sprint_name, sprint_start_date
)
SELECT
    sprint_name as "sprint name",
    velocity,
    prev_velocity as "prev velocity",
    CASE
        WHEN prev_velocity IS NOT NULL AND prev_velocity > 0 THEN
            ROUND(((velocity - prev_velocity) * 100.0) / prev_velocity, 2)
        ELSE NULL
    END AS "velocity change percent",
    AVG(velocity) OVER (ORDER BY sprint_start_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS "rolling avg velocity"
FROM
    sprint_velocity
ORDER BY
    sprint_start_date;

Cross-Project Dependencies

Blocked Items Impact Analysis

This SQL query analyzes blocking relationships between work items across different projects. It identifies instances where a work item entered a “blocked” status and then determines which work item (and its associated project) was causing the block, and which work item (and its project) was blocked.

For each blocking incident, it calculates how long the blocked work item remained in the “blocked” status. If an item is still blocked, the current time is used to calculate the duration.

The query then groups these incidents by the blocking project and the blocked project. For each pair of projects, it reports:

• The total number of times items from the first project blocked items from the second project (blocked_items).
• The average duration (in days) that these items were blocked (avg_days_blocked).
• The count of items that were blocked for more than 7 days (items_blocked_over_week).

Finally, it filters the results to only show project pairs that have had more than 5 blocking incidents and orders them by the average blocking duration in descending order, showing the longest average blocks first.

Example Result:

blocker_project	blocked_project	blocked_items	avg_days_blocked	items_blocked_over_week
Infrastructure	Frontend	12	8.5	7
Backend	Mobile	8	6.2	3
Platform	Analytics	6	4.8	1

SELECT
    blocker_project,
    blocked_project,
    COUNT(*) AS blocked_items,
    AVG(days_blocked) AS avg_days_blocked,
    SUM(CASE WHEN days_blocked > 7 THEN 1 ELSE 0 END) AS items_blocked_over_week
FROM (
    SELECT
        blocker.project AS blocker_project,
        blocked.project AS blocked_project,
        EXTRACT(EPOCH FROM COALESCE(h2.date, NOW()) - h1.date) / 86400 AS days_blocked
    FROM histories h1
    JOIN work_items blocked ON h1.work_item_id = blocked.id
    JOIN issue_links il
        ON il.source_issue_id = blocked.id OR il.target_issue_id = blocked.id
    JOIN work_items blocker
        ON blocker.id = CASE
            WHEN il.source_issue_id = blocked.id THEN il.target_issue_id
            ELSE il.source_issue_id
        END
    LEFT JOIN histories h2 ON h2.work_item_id = h1.work_item_id
        AND h2.field_id = h1.field_id
        AND h2.date > h1.date
    WHERE h1.field_id = 'status'
        AND h1.value = 'blocked_status_id'
) blocking_analysis
GROUP BY blocker_project, blocked_project
HAVING COUNT(*) > 5
ORDER BY avg_days_blocked DESC;

Resource Allocation & Capacity Planning

Team Capacity vs. Workload Analysis

Note

This query demonstrates use of custom tables. To run it, you’ll need to create a custom table by uploading it from CSV file. Example file is provided here. For information on how to create custom tables, refer to the Custom Tables page.

Required fields: assignee, status.

Required custom fields: {{story_points_customfield}} (Story Points). Include Story Points in the Database Configuration, and substitute the custom field IDs with the actual ones from your Jira instance (e.g. customfield_10009).

This query calculates the workload, capacity, and utilization percentage for each team. It uses predefined team capacities from the custom_team_capacity table and sums up “Story Points” for work items currently “In Progress” as the current workload. The final output shows each team’s total capacity, current workload, remaining available capacity, and a utilization percentage, ordered from highest to lowest utilization.

This helps in understanding how busy each team is relative to its capacity.

Example Result:

team_name	capacity_points	current_workload	available_capacity	utilization_percent
Frontend Team	40	38	2	95.00
Backend Team	50	42	8	84.00
Mobile Team	30	18	12	60.00
QA Team	25	12	13	48.00

WITH team_capacity AS (
    SELECT team_name, capacity_points FROM custom_team_capacity
),
current_workload AS (
    SELECT
        users.displayName as team_name,
        SUM({{story_points_customfield}}) as current_points,
        COUNT(*) as active_items
    FROM work_items wi
    LEFT JOIN users ON users.id = wi.assignee
    JOIN statuses s ON s.id = wi.status
    JOIN status_categories sc ON sc.id = s.statusCategoryId
    WHERE sc.name IN ('In Progress')
    GROUP BY team_name
)
SELECT
    tc.team_name,
    tc.capacity_points,
    COALESCE(cw.current_points, 0) as current_workload,
    tc.capacity_points - COALESCE(cw.current_points, 0) as available_capacity,
    ROUND((COALESCE(cw.current_points, 0) / tc.capacity_points::FLOAT) * 100, 2) as utilization_percent
FROM team_capacity tc
LEFT JOIN current_workload cw ON tc.team_name = cw.team_name
ORDER BY utilization_percent DESC;

Quality & Technical Debt Analysis

Bug Introduction Rate by Component

Required fields: created, component, issue_links, issuetype, resolutiondate.

This SQL query analyzes the rate at which bugs are reported after their associated parent stories are completed, broken down by software component.

It first identifies all ‘Bug’ work items that are linked to a ‘parent story’ (a larger piece of work) that has a resolution date (meaning it’s completed). For each such bug, it captures its creation date and the parent story’s completion date.

Then, for each software component, it calculates:

• Total Bugs: The overall count of bugs linked to completed parent stories.
• Immediate Bugs: The count of bugs that were created within 7 days of their parent story being completed.
• Month Bugs: The count of bugs created within 30 days of their parent story being completed.
• Immediate Bug Rate: The percentage of ‘immediate bugs’ out of the ‘total bugs’.

The final result set includes components with at least 10 bugs, ordered by their immediate bug rate, from highest to lowest.

Example Result:

component	total_bugs	immediate_bugs	month_bugs	immediate_bug_rate_percent
Payment System	15	8	12	53.33
User Authentication	22	9	18	40.91
Search Engine	18	6	14	33.33
Report Generator	12	3	8	25.00

WITH bug_timeline AS (
    SELECT
        wi.id,
        wi.component,
        wi.created AS bug_created,
        parent_story.resolutiondate AS story_completed
    FROM work_items wi
    JOIN issue_links il
        ON il.source_issue_id = wi.id OR il.target_issue_id = wi.id
    JOIN work_items parent_story
        ON parent_story.id = CASE
            WHEN il.source_issue_id = wi.id THEN il.target_issue_id
            ELSE il.source_issue_id
        END
    JOIN issuetypes it ON it.id = wi.issuetype
    WHERE it.name = 'Bug'
        AND parent_story.resolutiondate IS NOT NULL
)
SELECT
    component,
    COUNT(*) AS total_bugs,
    COUNT(CASE WHEN bug_created <= story_completed + INTERVAL '7 days' THEN 1 END) AS immediate_bugs,
    COUNT(CASE WHEN bug_created <= story_completed + INTERVAL '30 days' THEN 1 END) AS month_bugs,
    ROUND(
        COUNT(CASE WHEN bug_created <= story_completed + INTERVAL '7 days' THEN 1 END) * 100.0 /
        COUNT(*), 2
    ) AS immediate_bug_rate_percent
FROM bug_timeline
WHERE component IS NOT NULL
GROUP BY component
HAVING COUNT(*) >= 10
ORDER BY immediate_bug_rate_percent DESC;

Advanced Workflow Analysis

Bottleneck Detection with Statistical Significance

Required fields: status.

This query uses histories table. To generate the table enable “Include historical data (changelog)” option in the Database Configuration.

This query analyzes how much time work items spend in different statuses within your workflow.

It calculates the average time, the 95th percentile time (meaning 95% of items complete that status within this time), and the variability for each status. Based on these metrics, it categorizes statuses as ‘High Variability Bottleneck’, ‘Consistent Bottleneck’, or ‘Normal Flow’ to help identify stages where work items might be getting stuck or experiencing inconsistent delays. It only considers statuses with at least 5 historical entries for reliable analysis.

Example Result:

status_name	avg_days	p95_days	bottleneck_type	coefficient_of_variation
Waiting for Approval	8.5	28.2	High Variability Bottleneck	1.85
Code Review	6.2	12.1	Consistent Bottleneck	0.95
In Progress	3.8	9.5	Normal Flow	1.12
Testing	2.1	5.8	Normal Flow	0.76

WITH status_times AS (
    SELECT
        work_item_id,
        field_id,
        value as status_id,
        seconds_in_state / 86400.0 as days_in_status
    FROM histories
    WHERE field_id = 'status' AND seconds_in_state > 0
),
status_stats AS (
    SELECT
        json_extract_string(statuses."data", 'name') as status_name,
        AVG(days_in_status) as avg_days,
        STDDEV(days_in_status) as stddev_days,
        PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY days_in_status) as p95_days,
        COUNT(*) as sample_size
    FROM status_times st
    LEFT JOIN statuses ON statuses.id = st.status_id
    GROUP BY status_name
    HAVING COUNT(*) >= 5
)
SELECT
    status_name,
    avg_days,
    p95_days,
    CASE
        WHEN p95_days > avg_days * 3 THEN 'High Variability Bottleneck'
        WHEN avg_days > 5 THEN 'Consistent Bottleneck'
        ELSE 'Normal Flow'
    END as bottleneck_type,
    ROUND(stddev_days / avg_days, 2) as coefficient_of_variation
FROM status_stats
ORDER BY avg_days DESC;

Note

These examples provide a starting point. You can adapt and combine them to perform more complex analyses tailored to your specific needs.