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

Q&A about the site for people looking to bypass life's everyday problems with simple tricks

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

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

fork query

created jul 20 10 dfullerton07