I'm querying the SO posting data to see popularity of different words in post titles over time. As part of this I'm trying to take account of overall traffic rather than looking at raw view/comment/answer counts.
To get the total posts and total post views, answers and comments per month I ran:
select Year(CreationDate), Month(CreationDate), count(*), Sum(ViewCount), Sum(AnswerCount), Sum(CommentCount) from Posts group by Year(CreationDate), Month(CreationDate)
Charting the data returned in Excel it shows a steady climb in total ViewCount until early 2011 (peak is ~207m/month), then a steady decline until now (~11m in Dec 13).
What makes me question the data is that counts for Posts, Comments and Answers grow steadily over the total period. So has the meaning or mechanism for recording ViewCount changed at some point?
Is it related to the introduction of new caching as described in the answer here?
Or is my SQL too rusty and my query is not doing what I think it is?