I want to know who submitted bugs and sub-bugs on my project and how many bugs submitted by each person
select key, reported_by from Ticket where type='Bug' or type='Sub-bug'
I want to know who submitted bugs and sub-bugs for the last sprint (example of JOIN function)
select ticket.key, ticket.reported_by from Ticket join Sprint on sprint.id=any(ticket.sprints) where (type='Bug' or type='Sub-bug') and sprint.name='Sprint R48'
I want to know number of issues per Label for a particular sprint
select key, unnest(labels) as label from Ticket left join Sprint on Sprint.id = any(Ticket.sprints) where Sprint.name='Sprint R48';
I'm interested in tasks distribution between QA engineers (based on Custom Field)
select key, Responsible_QA from (select key, jsonb_array_elements(custom_fields) ->> 'name' AS test_name, jsonb_array_elements(custom_fields) ->> 'value' AS Responsible_QA from Ticket) AS testtable where test_name = 'Responsible QA'
I would like to know line changes in code per Story Point by month (join TTS with GIT)
with joinedTandC as ( select sum(Commit.lines_changed::int) as changes, Ticket.key as key, Ticket.resolved as resolved, Ticket.story_points as points from Ticket inner join Commit on Ticket.resolved > '2021-01-01' and Ticket.story_points is not null and Ticket.story_points > 0 and Commit.message is not null and Commit.lines_changed > 0 and Commit.message like concat(Ticket.key, '%') group by key, resolved, points ) select sum(changes)::float/sum(points) as "Line Changes Per Story Point", /* sum(points) as "Sorry Points",*/ /* sum(changes) as "Line Changes", */ date_trunc('month',resolved) as "Month" from joinedTandC group by "Month" order by "Month"
I wonder how many issues are in every sprint after September 2021
select ticket.key, sprint.name from Ticket join Sprint on sprint.id=any(ticket.sprints) where sprint.start_date > '2021-09-01'
I wonder how much time on average is spent for implementing a user story of a specific size
select ticket.story_points, div(worklog.timespent,60) as spent_hours from ticket join worklog on ticket.id=worklog.workitem_id where type='Story'
Note: The column spent_hours is added three times on the Y-axis and a different type of data calculation is selected in the series settings: MIN, MAX, AVG
I would like to track Velocity Trend in my project
with sprints_results as ( -- velocity by sprints select s.name, s.start_date, (select sum(story_points) from Ticket t where s.id = any(t.sprints)) as story_points from Sprint s order by s.start_date ), rolling_avg_sprints as ( -- rolling average velocity by sprints (3 sprints) select name, avg(story_points) over (ORDER BY start_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as rolling_average, start_date from sprints_results) select -- percentage of how current avg velocity is more than previous avg velocity name as "Sprint Name", rolling_average * 100 / (first_value(rolling_average) over (order by start_date ROWS between 1 preceding AND 1 preceding)) as "Velocity Trend, %" from rolling_avg_sprints
Copyright © 2024 EPAM Systems, Inc. |
---|
All Rights Reserved. All information contained herein is, and remains the property of EPAM Systems, Inc. and/or its suppliers and is protected by international intellectual property law. Dissemination of this information or reproduction of this material is strictly forbidden, unless prior written permission is obtained from EPAM Systems, Inc. |