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