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)

Stack Overflow

Q&A for professional and enthusiast programmers

/*
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
Concat(
  YEAR(Posts.CreationDate),
  '-',
  IIF(MONTH(Posts.CreationDate) < 10, '0', ''),
  MONTH(Posts.CreationDate)) as [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
-- 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##
)
group by YEAR(Posts.CreationDate), MONTH(Posts.CreationDate)
order by YEAR(Posts.CreationDate) desc, MONTH(Posts.CreationDate) desc

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results