Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 20 Next »

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



  • No labels