# Excuse me, how many of you are new here? Mind if I JOIN you?

I had perceived an uptick in new reviewers around here, and I wanted to see if the data backed that observation up. It's been a while since I've written any SQL, so this was a nice little exercise in using the Stack Exchange Data Explorer.

The query finds the first question and first answer from every user, and then groups those first posts by week. It turns out that I was right, but the growth of first questions is outpacing the growth of first answers.

The query feels repetitive and I'm not a huge fan of the Full Outer Join. Is there a better way to write this?

WITH FirstAnswers
AS (
SELECT
Users.Id UserId
,dateadd(week, datediff(week, 0, Convert(Date,Min(Posts.CreationDate))), 0) WeekOf
FROM Posts
ON Posts.OwnerUserId = Users.Id
AND Posts.CreationDate > '2011-Jan-01' --There was very little activity prior to this date. Including it skews the graph.
GROUP BY Users.Id
),

FirstQuestions
AS (
SELECT
Users.Id UserId
,dateadd(week, datediff(week, 0, Convert(Date,Min(Posts.CreationDate))), 0) WeekOf
FROM Posts
ON Posts.OwnerUserId = Users.Id
WHERE PostTypeId = 1 --question
AND Posts.CreationDate > '2011-Jan-01' --There was very little activity prior to this date. Including it skews the graph.
GROUP BY Users.Id
)

SELECT ISNULL(a.WeekOf,b.WeekOf) As WeekOf
FROM (
GROUP BY WeekOf
) a
FULL OUTER JOIN (
SELECT WeekOf, Count(UserId) QuestionCount
FROM FirstQuestions
GROUP BY WeekOf
)b
ON a.WeekOf = b.WeekOf
ORDER BY WeekOf


• Plotting the number of new users per week against this data would have been nice. Jul 24, 2015 at 0:49
• I'm not real sure how to make that happen @Mat'sMug. Open to suggestions. Jul 24, 2015 at 0:55

Technically, the full outer join is insufficient. If during a certain week there was not a single user who posted their first question or answer, the plot for that week needs to be 0. It would be misleading to plot a direct line from the previous week to the following week, skipping the quiet week. So, strictly speaking, you need to generate a series of all the weeks, to which you LEFT OUTER JOIN the FirstQuestions and FirstAnswers CTEs. However, generating a date series in SQL Server is a hassle compared to some developer-friendlier databases. I'm willing to pretend that there aren't weeks with no new user activity (actually demonstrably false for the week of 2011-01-10).

## First rewrite

All of the information you need is contained in the Posts table. There is no need to join the Users table.

The subselects in the query suggest that your CTEs are underdeveloped. In my first rewrite below, I've reorganized FirstAnswers and FirstQuestions to perform the role of your subselects.

a and q would make more meaningful table aliases than a and b.

Your function names are inconsistently capitalized: dateadd, datediff, Convert, Min, ISNULL. I believe that Convert(Date, …) isn't needed at all.

WITH FirstPosts AS (
SELECT OwnerUserId As UserId
, PostTypeId
, DATEADD(week, DATEDIFF(week, 0, MIN(CreationDate)), 0) AS WeekOf
FROM Posts
WHERE CreationDate > '2011-01-01' -- There was very little activity prior to this date. Including it skews the graph.
GROUP BY OwnerUserId, PostTypeId
SELECT WeekOf
, COUNT(UserId) AS UserCount
FROM FirstPosts
WHERE PostTypeId = 2 -- answer
GROUP BY WeekOf
), FirstQuestions AS (
SELECT WeekOf
, COUNT(UserId) AS UserCount
FROM FirstPosts
WHERE PostTypeId = 1 -- question
GROUP BY WeekOf
)
SELECT ISNULL(a.WeekOf, q.WeekOf) AS WeekOf
, ISNULL(q.UserCount, 0) AS QuestionCount
FULL OUTER JOIN FirstQuestions AS q
ON a.WeekOf = q.WeekOf
ORDER BY 1;


## Better solution

As it turns out, there is a much slicker way to avoid the join altogether by using a conditional COUNT():

WITH FirstPosts AS (
SELECT OwnerUserId As UserId
, PostTypeId
, DATEADD(week, DATEDIFF(week, 0, MIN(CreationDate)), 0) AS WeekOf
FROM Posts
WHERE CreationDate > '2011-01-01' -- There was very little activity prior to this date. Including it skews the graph.
GROUP BY OwnerUserId, PostTypeId
)
SELECT WeekOf
, COUNT(CASE WHEN PostTypeId = 2 THEN UserId END) AS AnswerCount
, COUNT(CASE WHEN PostTypeId = 1 THEN UserId END) AS QuestionCount
FROM FirstPosts
GROUP BY WeekOf
ORDER BY WeekOf;

• What does Order By 1 do? In my mind, it shouldn't do anything at all. Fantastic answer btw! Jul 24, 2015 at 9:27
• Nevermind, stackoverflow had an answer for me. =D Jul 24, 2015 at 10:18

'2011-Jan-01' --There was very little activity prior to this date. Including it skews the graph.

And then

'2011-Jan-01' --There was very little activity prior to this date. Including it skews the graph.

If that wasn't Copy+Paste'd, you've given your fingers a lot of work here. And if it was... well then, you've given yourself more work than you should need, to change the "start date".

declare @startDate datetime
set @startDate = '2011-01-01' --There was very little activity prior to this date. Including it skews the graph.


Why not stick to the ISO format, YYYY-MM-DD? Not that 2011-Jan-01 isn't readable, it's just... a little surprising, to me at least.

That said, @startDate is nice, but even nicer would be a SEDE parameter - and a notch nicer still, would be a SEDE parameter that lets you specify a number of weeks, and to have the script compute the @statDate; the query results works in weeks anyway, and then you could calculate the start date by ensuring you're only capturing full StackLand weeks. this way you avoid returning a less-meaningful first week figure, if today is Saturday for example (like Jan 1st, 2011 was).

SELECT ISNULL(a.WeekOf,b.WeekOf) As WeekOf


This is the only instance of the AS keyword I'm seeing that's used to specify a column alias. Be consistent, ditch it! :)

You can do this more succinctly by first grouping by the User and the Post Type to get a set of every first question and answer. Then group that set by the type of post and the week it occurred in. Finally use the PIVOT operator to pivot the rows into two columns so you can display it on the chart.

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

FROM (
SELECT WeekOf,
CASE PostTypeId
WHEN 1 THEN 'Questions'
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 >= @FirstDate
GROUP BY OwnerUserId, PostTypeId
) AS FirstPosts
GROUP BY WeekOf, PostTypeId
) AS WeeklyCounts
PIVOT (
MAX(PostCount)
) AS p
ORDER BY WeekOf;


There isn't any need to join the Posts table to the Users table because we don't need any columns from there. Grouping by the OwnerUserId column is sufficient, it doesn't even need to be in the SELECT clause.

A variable to hold the starting week so it can be easily updated in the future can be useful.

I noticed that you've split all of your sub-queries out into CTEs. If I'm not using it in multiple places or doing recursion I don't use a CTE. Personally, I find it easier to read the sub-queries in-line rather than have to scroll up to see what they are doing.

One problem this doesn't solve is that if no new questions and answers are made in a week then that week won't appear in the result set. A recursive CTE can be used to generate a set of all of the dates in the period.

My date ranges aren't normally so large so I don't usually need to change the MAXRECURSION option but in this case there are more than 100 weeks in the set and 100 is the default maximum recursion limit. 300 will be sufficient.

A second variable is used to store the maximum post date for limiting the recursive CTE. If a variable wasn't used the query to determine the maximum post creation date would be run every time the CTE executed.

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

DECLARE @FinalWeek DATE = (
SELECT MAX(CreationDate)
FROM Posts
WHERE PostTypeId IN (1,2)
);

WITH AllWeeks AS (
SELECT DATEADD(week, DATEDIFF(week, 0, @FirstWeek), 0) AS WeekOf

UNION ALL

SELECT DATEADD(week, 1, WeekOf) AS WeekOf
FROM AllWeeks
WHERE WeekOf < @FinalWeek
)
FROM AllWeeks
LEFT JOIN (
SELECT WeekOf,
CASE PostTypeId
WHEN 1 THEN 'Questions'
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)
) AS p ON p.WeekOf=AllWeeks.WeekOf
ORDER BY WeekOf
OPTION (MAXRECURSION 300);


Doing this will slow down your query of course.

Number of first posts and new users per week