sqlstackexchange-apistackexchange

How to query all the new tags created last week from Stack Exchange data Explorer?

I want to query SO new tags created during last week. We have tables Posts,Tags, PostTags (leave out TagSynonyms for now).

Tags doesn't have CreationDate so we probably need to use join with min(Post.CreationDate)

last week = getdate() - 7
Solution

  • This query will produce a list of posts that the tags are created the past week. The JOIN between the tables is only to bring information. The WHERE clause would exclude tags which have been used prior to the 7 days.

    The subquery will produce a list of unique tag IDs that where used in the past. Following your last week example in question, the formula is created by using the DATEDIFF function.

    SELECT PostTags.PostId, PostTags.TagId, Tags.TagName, Posts.CreationDate
FROM PostTags
JOIN Posts
ON PostTags.PostId = Posts.Id
JOIN Tags
ON PostTags.TagId = Tags.Id
WHERE PostTags.TagId NOT IN (
  SELECT DISTINCT PostTags.TagId AS OlderTags
  FROM PostTags
  JOIN Posts
  ON PostTags.PostId = Posts.Id
  WHERE DATEDIFF(DAY, CreationDate, GETDATE()) > 7)