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


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.


  • No labels