5

I'm looking for a total tag count up until a specific date for a given tag - or ideally, tags plural - on Stack Overflow. Any suggestions appreciated.

0
2

Here's a simple query to count the number of posts with a given tag on a certain date:

Select
  count(*) as [Count]
From
  PostHistory as TagChange
Where
  PostHistoryTypeId in (3,6,9)  -- initial/edit/rollback tags
  and Text like '%<' + ##TagName:string## + '>%'
  and CreationDate < ##DateBefore:string?9999-12-31##
  and not exists(
    Select 1 From PostHistory as NewChange
    Where NewChange.PostId = TagChange.PostId
      and PostHistoryTypeId in (3,6,9)
      and NewChange.CreationDate > TagChange.CreationDate
      and NewChange.CreationDate < ##DateBefore:string?9999-12-31##
  )

Unlike rene's query, this one should only count the latest set of tags on the question before the limit date. With my query, I count 25,494 questions tagged haskell on SO on 2016-01-01 (and 36,229 as of the latest SEDE update, which matches pretty well with the current live count of 36,303).

However, note that the results of this query are still not 100% historically accurate, since it fails to count questions that have been deleted after the cutoff date. This is simply because SEDE doesn't provide access to post history information for deleted posts. While it's possible to find out which tags a deleted question had when it was deleted by examining the PostsWithDeleted table, there's no way to reliably tell when the tags were added to the question.

I also wrote a query that can graph the number of posts with a given tag in monthly increments. Here's what the growth of the [haskell] tag on SO looks like:

Growth of the [haskell] tag on Stack Overflow

And for comparison, here's the same graph for the [problem] tag, which was apparently first burninated in October 2009, bounced back a bit and was nuked again a couple of times, and was finally blacklisted in 2011:

Growth and fall of the [problem] tag on Stack Overflow

Of course, it should be noted that these results also don't include deleted questions, which probably makes the [problem] seem a lot smaller now than it actually was back in 2008–2011.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .