0

Please login or register to vote for this query.

(click on this box to dismiss)

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

fork query

edited jan 3 19

created jan 3 19 anonymous