Q&A for amateur radio enthusiasts
with new_users_by_week as ( select cast (dateadd(week, datediff(week, getdate(), CreationDate), getdate()) as DATE) Week, count(*) new_users from Users group by datediff(week, getdate(), CreationDate) ) select cast (dateadd(week, datediff(week, getdate(), q.ClosedDate), getdate()) as DATE) Week, avg(datediff(hour, q.CreationDate, q.ClosedDate)) close_delay, new_users, count(q.Id) closes_per_week from Posts q join new_users_by_week u on u.Week = cast (dateadd(week, datediff(week, getdate(), q.ClosedDate), getdate()) as DATE) where q.PostTypeId = 1 and q.ClosedDate is not null group by DATEDIFF(week, getdate(), q.ClosedDate), new_users order by DATEDIFF(week, getdate(), q.ClosedDate)