Open Issues ans Avg Open Issues Lifetime

gistfile1.txt

SELECT 
    date,
    avgIf(diff, created and not resolved) as avg_open_days,
    countIf(created and not resolved) as cnt_open
FROM
(
    SELECT 
        toDate( toString(y) || '-' || toString(m)  || '-' || '01') as date,
        dateDiff('day', created_at,  date) as diff,
        date >= created_at as created,
        state,
        closed_at,
        state != 'open' and closed_at <= date as resolved
    FROM
    default.issues
    CROSS JOIN 
    (
    SELECT 
        arrayJoin(range(1,13,1)) as m, 
        arrayJoin(range(2016,2021,1)) as y 
    ) t2
)
WHERE date < toDate('2020-02-01')
GROUP BY date
ORDER BY date ASC