Number of first posts and new users per week

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

DECLARE @FirstDate DATE = '2011-01-01';  --There was very little activity prior to this date. Including it skews the graph.

DECLARE @FirstWeek DATE = DATEADD(week, DATEDIFF(week, 0, @FirstDate), 0);
DECLARE @LastWeek DATE = (
    SELECT MAX(CreationDate)
    FROM Posts
    WHERE PostTypeId IN (1,2)
);

WITH AllWeeks AS (
    SELECT @FirstWeek AS WeekOf
    
    UNION ALL

    SELECT DATEADD(week, 1, WeekOf) AS WeekOf
    FROM AllWeeks
    WHERE WeekOf < @LastWeek
)
SELECT AllWeeks.WeekOf,
  COALESCE(p.Questions,0) AS [First Questions],
  COALESCE(p.Answers,0) AS [First Answers],
  COALESCE(NewUsers.AccountsCreated,0) AS [New Accounts]
FROM AllWeeks
LEFT JOIN (
    SELECT WeekOf,
        CASE PostTypeId
            WHEN 1 THEN 'Questions'
            WHEN 2 THEN 'Answers'
        END AS PostType,
        COUNT(*) AS PostCount
    FROM (
        SELECT PostTypeId,
            DATEADD(week, DATEDIFF(week, 0, MIN(CreationDate)),0) AS WeekOf
        FROM Posts
        WHERE PostTypeId IN (1,2)
            AND CreationDate >= @FirstWeek
        GROUP BY OwnerUserId, PostTypeId
    ) AS FirstPosts
    GROUP BY WeekOf, PostTypeId
) AS WeeklyCounts
PIVOT (
    MAX(PostCount)
    FOR PostType IN ([Questions],[Answers])
) AS p ON p.WeekOf=AllWeeks.WeekOf
LEFT JOIN (
  SELECT DATEADD(week, DATEDIFF(week, 0, CreationDate), 0) AS WeekOf,
    COUNT(*) AS AccountsCreated
  FROM Users
  GROUP BY DATEADD(week, DATEDIFF(week, 0, CreationDate), 0)
) AS NewUsers ON NewUsers.WeekOf=AllWeeks.WeekOf
ORDER BY WeekOf
OPTION (MAXRECURSION 300);

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results