0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for people who build and repair bicycles, people who train cycling, or commute on bicycles

DECLARE @StartDate DATE = '##StartDate##'; -- Should be a Sunday
DECLARE @EndDate DATE = '##EndDate##'; -- Should also be a Sunday (upper bound non-inclusive)

WITH WeekNormalisedAnswers (StartOfWeek, UserId, PostId) AS (
    SELECT
        CAST(DATEADD(DAY, 1 - DATEPART(WEEKDAY, p.CreationDate), p.CreationDate) AS DATE),
        u.id,
        p.id
    FROM
        Users u
            LEFT JOIN Posts p ON p.OwnerUserId = u.id
    WHERE p.PostTypeId = 2 AND
          (@StartDate <= p.CreationDate AND p.CreationDate < @EndDate)
),
     AnswersPerUserPerWeek (StartOfWeek, UserId, NumberOfPosts) AS (
         SELECT
             StartOfWeek,
             UserId,
             COUNT(DISTINCT PostId)
         FROM
             WeekNormalisedAnswers
         GROUP BY StartOfWeek, UserId
     )
SELECT
    DENSE_RANK() OVER (ORDER BY StartOfWeek) AS WeekNumber,
    COUNT(DISTINCT UserId) AS NumberOfUsers,
    NumberOfPosts
FROM
    AnswersPerUserPerWeek
GROUP BY StartOfWeek,
         NumberOfPosts
ORDER BY StartOfWeek,
         NumberOfPosts;

fork query

edited jun 9

created jun 9 Henry.Ecker