with
variables as (
select
't' as calculate_by, -- choose the basement of calculation: tickets or story points
7 as iteration_duration, --in days
1020 as iterations_to_analyze,
20 as future_iterations_num, --number of iterations to make forecast for
'no' as scope_change, --yes/no ()
200 200 as division_step --must be an even number!
),
types as (
select a.* from (values ('bug'), ('task')) a (ticket_type) -- add ticket types to make forecast
),
start_date_ as (
select date_trunc('week', current_date)::date
-
(select iterations_to_analyze*iteration_duration from variables) as start_date
),
iteration_days as (
select generate_series(a.min_d,
date_trunc('week', current_date)::date + a.iteration_duration*a.future_iterations_num,
'1 day'
)
as day_
from (
select
sd.start_date::date as min_d,
v.iteration_duration,
v.future_iterations_num
from start_date_ sd,
variables v
) a
),
buckets as (
select a.* from (values ('scope'), ('done')) a (bucket)
),
dates_buckets as (
select
itd.day_, b.bucket
from iteration_days itd, buckets b
),
ticket_keys_created as (
select
t.id,
t.created,
t.type,
t.status,
t.story_points,
'scope' as category
from
ticket t
where
lower(t.type) in (select ticket_type from types)
),
ticket_keys_done as (
select
t.id,
t.type,
t.status,
t.done_date,
t.story_points,
'done' as category
from
ticket t
where
lower(t.status) in ('closed')
and lower(t.type) in (select ticket_type from types)
and t.done_date >= (select sd.start_date::date from start_date_ sd)
),
intermediate_result as (
select
s.day_::timestamp,
count(tkc.id) as scope_tickets,
count(tkd.id) as done_tickets,
sum(tkc.story_points) as scope_story_points,
sum(tkd.story_points) as done_story_points,
(s.day_::date - (select sd.start_date::date from start_date_ sd))/(select sd.iteration_duration from variables sd)+1 as iteration_n, --also a week number or an iteration number
((s.day_::date - (select sd.start_date::date from start_date_ sd))/(select v.iteration_duration from variables v)+1)
-
((current_date - (select sd.start_date::date from start_date_ sd))/(select v.iteration_duration from variables v)+1) as "N"
from
dates_buckets s
left join
ticket_keys_created tkc
on s.day_ between tkc.created and now()
and s.bucket = tkc.category
left join
ticket_keys_done tkd
on s.day_ between tkd.done_date and now()
and s.bucket = tkd.category
group by s.day_
order by s.day_
),
iteration_end_dates_ as (
select
day_,
max(day_) over (partition by iteration_n) as sprint_end_date,
case
when (select v.calculate_by from variables v) = 't'
then scope_tickets
else scope_story_points
end as scope,
case
when (select v.calculate_by from variables v) = 't'
then done_tickets
else done_story_points
end as done,
n
from intermediate_result
where n >= - (select v.iterations_to_analyze from variables v)
),
added_tickets_velocity_tickets as (
select
day_,
scope,
done,
case
when n<=0
then done - (lag(done) over (order by n))
end as velocity,
case
when
(select v.scope_change from variables v) = 'no'
then 0
else
case
when n<=0
then scope - (lag(scope) over (order by n))
end
end as added_in,
n
from iteration_end_dates_
where day_ = case n when 0 then current_date else sprint_end_date end
),
avg_in_avg_out as (
select
day_,
scope,
done,
added_in,
velocity,
avg(added_in) over () as AverageIn,
avg(velocity) over () as AverageOut,
n
from
added_tickets_velocity_tickets
),
disp_in_disp_out as (
select
day_,
scope,
done,
added_in,
velocity,
AverageIn,
coalesce(
(sum( (sum(case
when
n <= 0
then
(added_in - AverageIn)*(added_in - AverageIn)
end
) over ()
)/nullif((select -n-1 from iteration_end_dates_ order by n limit 1), 0),
0) as disp_in, --sigma(2)
AverageOut,
coalesce(
(sum( (sum(case
when
n <= 0
then
(velocity - AverageOut)*(velocity - AverageOut)
end
) over ()
)/nullif((select -n-1 from iteration_end_dates_ order by n limit 1), 0),
0) as disp_out,
n
from avg_in_avg_out
),
meanX_standdevX as (
select
day_,
scope,
done,
case
when n = 0
then scope - done
when n > 0
then lag(scope - done, n) over (order by n)
end as work_left,
n,
(case
when n>= 0
then n
end)*(AverageOut - AverageIn) as me,
sqrt(
(case
when n>= 0
then n
end)*(disp_in + disp_out)
) as sd
from
disp_in_disp_out
),
points as (
select
day_,
scope,
done,
n,
work_left,
me,
sd,
generate_series((work_left)::numeric,
(me+4*sd)::numeric,
(me+4*sd - work_left)::numeric
/
(select v.division_step from variables v)
) as points,
(me+4*sd - work_left)::numeric/(select v.division_step from variables v) as h
from
meanX_standdevX
where n > 0
),
normal_dist_values as (
select
day_,
scope,
done,
n,
work_left,
me,
sd,
points,
h,
dense_rank() over (partition by n order by points) as index,
case
when h > 0
then
1::numeric/(sd*sqrt(2*pi())) * round(cast(exp(-power((points - me), 2)/(2*power(sd, 2))) as numeric), 4)
else 0
end as normal_dist_value
from points
),
simpson_rule as (
select
day_,
scope,
done,
n,
work_left,
me,
sd,
points,
h,
index,
case
when index = 1 or index = (select v.division_step from variables v)+1
then normal_dist_value
when mod(index, 2) = 0
then normal_dist_value*2
when mod(index, 2) = 1
then normal_dist_value*4
end as simpson_values
from
normal_dist_values
)
select
to_char(day_, 'yyyy')||' - '||date_part('week', day_) as week_number,
round((max(h)::numeric/3)*sum(simpson_values)::numeric*100, 2) as probability
from simpson_rule
group by day_
order by day_ |