SQL Query Examples

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

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

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

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

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

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

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;

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;

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.inward_issue = blocked.id
    JOIN work_items blocker ON il.outward_issue = blocker.id
    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;

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;

WITH bug_timeline AS (
    SELECT 
        wi.id,
        wi.component,
        wi.created as bug_created,
        parent_story.resolutiondate as story_completed
    FROM work_items wi
    LEFT JOIN issue_links il ON il.inward_issue = wi.id
    LEFT JOIN work_items parent_story ON il.outward_issue = parent_story.id
    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;

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;