0

Please login or register to vote for this query.

(click on this box to dismiss)

Total number of users who posted a question or answer on three separate days in a single week

Вопросы и ответы для программистов

-- Engaged Users Per Week
-- Total number of users who posted a questin or answer on three separate days in a single week

SELECT Year, Week, COUNT(*) FROM (
  SELECT OwnerUserId, Year, Week, COUNT(*) Count FROM (
    SELECT OwnerUserId, YEAR(CreationDate) Year, DATEPART(wk, CreationDate) [Week], DAY(CreationDate) Day
    FROM Posts
    WHERE CreationDate > '2009-01-01'
    GROUP BY OwnerUserId, YEAR(CreationDate), 
    DATEPART(wk, CreationDate), 
    DAY(CreationDate)
  ) t
  GROUP BY OwnerUserId, Year, Week
  HAVING COUNT(*) >= 3
) t
GROUP BY Year, Week
ORDER BY Year DESC, Week DESC

fork query

edited feb 24 13 draks

created jul 20 10 dfullerton07