Questions with/without a specific tag at starting revision versus now, tabulated as qty by month

0

Please login or register to vote for this query.

(click on this box to dismiss)

Role-playing Games

Q&A for gamemasters and players of tabletop, paper-and-pencil role-playing games

/*
This query is a variant of the following:
https://data.stackexchange.com/rpg/query/776724/
Except with results tabulated per month.

CONFIGURATION:

HasNow (boolean, 1 or 0):
  Whether questions should have the tag now or not.
  If 1, find questions that have the tag at current revision.
  If 0, find questions that don't have the tag.

HadThen (boolean, 1 or 0):
  Whether questions should have had the tag at first revision, or not.
  If 1, find questions that had the tag at first revision.
  If 0, find questions that didn't.

Tag:
  The tag to track.

CreationDateMaxYear/Month:
  The maximum creation date for questions to include in the query.

CreationDateMinYear/Month:
  The minimum creation date for the same.
*/

select
DateRange.[Year-Month],
Count(Posts.Id) as [Count]
from Posts
inner join (
  select PostId, Text
  from PostHistory
  where PostHistoryTypeId = 3
  -- ORIGINAL TAGS
  and (
    (Text like '%##Tag##%' and ##HadThen:int## = 1)
    or
    (Text not like '%##Tag##%' and ##HadThen:int## = 0)
  )
) as PostInits on Posts.Id = PostInits.PostId RIGHT JOIN
(select
Concat(
  YEAR(Posts.CreationDate),
  '-',
  IIF(MONTH(Posts.CreationDate) < 10, '0', ''),
  MONTH(Posts.CreationDate)) as [Year-Month]
from Posts
WHERE (
  YEAR(Posts.CreationDate) <= ##CreationDateMaxYear:int##
  and MONTH(Posts.CreationDate) <= ##CreationDateMaxMonth:int##
)
and (
  YEAR(Posts.CreationDate) >= ##CreationDateMinYear:int##
  and MONTH(Posts.CreationDate) >= ##CreationDateMinMonth:int##
)) DateRange ON DateRange.[Year-Month] = Concat(
  YEAR(Posts.CreationDate),
  '-',
  IIF(MONTH(Posts.CreationDate) < 10, '0', ''),
  MONTH(Posts.CreationDate))
-- CURRENT TAGS
where ((
  (Posts.Tags like '%##Tag##%' and ##HasNow:int## = 1)
  or
  (Posts.Tags not like '%##Tag##%' and ##HasNow:int## = 0)
)
and (
  YEAR(Posts.CreationDate) <= ##CreationDateMaxYear:int##
  and MONTH(Posts.CreationDate) <= ##CreationDateMaxMonth:int##
)
and (
  YEAR(Posts.CreationDate) >= ##CreationDateMinYear:int##
  and MONTH(Posts.CreationDate) >= ##CreationDateMinMonth:int##
  ))
OR Posts.Id IS NULL
group by DateRange.[Year-Month]
order by DateRange.[Year-Month] desc

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results