...
I want to know who submitted bugs and sub-bugs on my project and how many bugs submitted by each person
Code Block | ||||
---|---|---|---|---|
| ||||
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)
Code Block | ||||
---|---|---|---|---|
| ||||
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
Code Block | ||||
---|---|---|---|---|
| ||||
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)
Code Block | ||||
---|---|---|---|---|
| ||||
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 wonder how many issues are in every sprint after September 2021
Code Block | ||||
---|---|---|---|---|
| ||||
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
Code Block | ||||
---|---|---|---|---|
| ||||
select ticket.story_points, div(worklog.timespent,60) as spent_hours from ticket join worklog on ticket.id=worklog.workitem_id where type='Story' |
...
I would like to track Velocity Trend in my project
Code Block | ||||
---|---|---|---|---|
| ||||
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 |
...