-- Generate scope
with scope as (
select key, created from ticket
    --, release r
    -- WHERE r.id = ANY(ticket.fix_releases) and r.name = 'version 1.15.2'
),
 
-- Generate ticket snaphsots
snapshots as (
    select * from ticket_snapshots(ARRAY(select key from scope), 'week')
),
 
-- Generate days
days as(
    select generate_series( 
            (select date_trunc('day', min(created)) from scope),
            date_trunc('day', now()),                                                          
            '1 week') as start
                                 
),
 
-- Get snapshot actual by each date
snapshot_on_day as(
    select snapshots.key as key, max(snapshots.snapshot_created) as created, days.start as day from snapshots, days
    where snapshots.snapshot_created < days.start
    group by snapshots.key, days.start
),
 
-- Generate done count by date
done as(
    select count(distinct snapshot_on_day.key) as done_count, snapshot_on_day.day as start from snapshots sn
    inner join snapshot_on_day
    on sn.key = snapshot_on_day.key
    and sn.snapshot_created = snapshot_on_day.created
    where is_done(sn)
    group by snapshot_on_day.day
),
 
-- Generate created count by date
created as(
    select count(distinct key) as created_count, days.start from scope, days
    where created < days.start
    group by days.start )
 
select created.created_count, done.done_count, created.start as startdate from created left outer join done on done.start = created.start order by startdate