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 SQL Queries page for a detailed description of the available tables and views like work_items, work_items_display, histories, statuses, users, etc.

Basic Queries

1. Count Work Items by Status

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

SELECT
    status,
    COUNT(*) AS work_item_count
FROM work_items_display
GROUP BY status
ORDER BY work_item_count DESC;

2. List Work Items Assigned to a Specific User

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

SELECT
    "key",
    issuetype,
    status
FROM work_items_display
WHERE assignee = 'Display Name of User';

3. 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.

-- Note: Ensure the 'created' field is selected in the Database Configuration
SELECT
    "key",
    issuetype,
    status,
    created -- Assuming 'created' is the field ID for the creation date
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.

4. 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.

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';

5. 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.

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;

Joining Tables

6. 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.

SELECT
    i."key",
    i.status,
    i.assignee AS assignee_display_name,
    json_extract_string(u.data, 'emailAddress') AS assignee_email
FROM work_items_display i
LEFT JOIN users u ON i.assignee = json_extract_string(u.data, 'displayName') -- Adjust join condition based on how assignee is stored
LIMIT 100;

Note: The exact JSON path (emailAddress, displayName) might vary depending on the Jira API version and data structure.

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

SQL Queries Reports