Q&A for people looking to improve their interpersonal communication skills
WITH cr AS (SELECT CAST(CreationDate AS DATE) AS cdate, 0.0+COUNT(*) ncreat FROM Users -- WHERE CreationDate > '2017-08-01' GROUP BY CAST(CreationDate AS DATE) ), lv AS (SELECT CAST(LastAccessDate AS DATE) AS cdate, 0.0+COUNT(*) nlast FROM Users -- WHERE CreationDate > '2017-08-01' GROUP BY CAST(LastAccessDate AS DATE) ) SELECT COALESCE(cr.cdate,lv.cdate) d , avg(ncreat) over(ORDER BY cr.cdate ROWS BETWEEN 14 PRECEDING AND CURRENT ROW) as [# Users created this date, 30 day rolling avg] , avg(nlast) over(ORDER BY lv.cdate ROWS BETWEEN 14 PRECEDING AND CURRENT ROW) as [# Users having this Last Access Date, 30 day rolling avg] FROM cr FULL OUTER JOIN lv ON (cr.cdate=lv.cdate) ORDER BY d