4

On my home site of RPG.SE, I'm trying to investigate whether a particular tag might be causing trouble. Specifically, I'm trying to find all the questions that had that tag in the first revision but don't currently have it, and vice versa, to get a metric on how often it needs to be added/removed.

However, SEDE doesn't seem to store revision history about tags. As far as I can tell it stores tags only for the current version of a post, and for revisions it only stores the text, revision comment, post ID, and some metadata around the revision.

Does the data explorer actually store post tag revision data somewhere I'm not seeing it? If so, where does it store that information, and how do I query for it?

2
  • @Shokhet That was it!! That stores tags like this in text: <support><tags><data-explorer><revisions-list>. Would you like to post that as an answer so that I can accept it? Jun 6 '17 at 22:13
  • Sure thing. Glad I could help :)
    – MTL
    Jun 6 '17 at 22:14
4

Sure you can. PostHistoryTypeId includes

  • 3: Initial Tags - The first tags a question is asked with.
  • 6: Edit Tags - A question's tags have been changed.
  • 9: Rollback Tags - A question's tags have been rolled back.

If you run a query like this:

select PostId as [Post Link], Text
from PostHistory
where PostHistoryTypeId = 3

You'll get the tags listed out in the Text field. For this question, you'd see:

<support><tags><data-explorer><revisions-list>

If you run a query like this (for a given post, here's an example):

select PostId as [Post Link], Text
from PostHistory
where PostHistoryTypeId in (3, 6, 9)
order by CreationDate

You'll get the edit history in order.

5
  • Thanks for the edit, @doppelgreener :)
    – MTL
    Jun 6 '17 at 22:26
  • Thanks to you for putting me onto this! :) Jun 6 '17 at 22:26
  • @doppelgreener You should also add 9 (rollback tags) to your list.
    – Jason C
    Jun 6 '17 at 23:32
  • Also there's fairly complete schema documentation here.
    – Jason C
    Jun 6 '17 at 23:34
  • Thank you, for the edit and the link, @JasonC.
    – MTL
    Jun 6 '17 at 23:47
3

Here are a few queries which look into the PostHistory table - and might occasionally be useful for people who try to keep an eye on the tags.

This is not a query which looks into the PostHistory table. But it still might be useful in situations, when you want to know whether some tag existed in the past: Deleted questions with the given tag

You must log in to answer this question.

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