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 |