0

Please login or register to vote for this query.

(click on this box to dismiss)

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

-- Enter Query Title
-- Enter Query Description

WITH Engaged AS (
  SELECT OwnerUserId, Year, Week, COUNT(*) Count FROM (
    SELECT OwnerUserId, YEAR(CreationDate) Year, DATEPART(wk, CreationDate) / 10 [Week], DAY(CreationDate) Day
    FROM Posts
    WHERE CreationDate > '2009-01-01' AND PostTypeId = 2
    GROUP BY OwnerUserId, YEAR(CreationDate), DATEPART(wk, CreationDate) / 10, DAY(CreationDate)
  ) t
  GROUP BY OwnerUserId, Year, Week
HAVING COUNT(*) >= 10
)
SELECT lastWeek.Year, lastWeek.Week, COUNT(*) FROM Engaged lastWeek 
LEFT JOIN Engaged thisWeek ON thisWeek.OwnerUserId = lastWeek.OwnerUserId AND thisWeek.Year = lastWeek.Year AND thisWeek.Week = lastWeek.Week + 1
WHERE thisWeek.Year IS NULL
GROUP BY lastWeek.Year, lastWeek.Week, thisWeek.Year, thisWeek.Week
ORDER BY lastWeek.Year DESC, lastWeek.Week DESC

fork query

created jul 20 10 dfullerton07