Tag count by date

0

Please login or register to vote for this query.

(click on this box to dismiss)

Motor Vehicle Maintenance and Repair

Q&A for mechanics and DIY enthusiast owners of cars, trucks, and motorcycles

select t.TagName, 
  count(case when p.CreationDate between '2015-1-1' and '2015-7-1' then 1 else null end) as Count2015a,
  count(case when p.CreationDate between '2015-7-1' and '2016-1-1' then 1 else null end) as Count2015b,
  count(case when p.CreationDate between '2016-1-1' and '2016-7-1' then 1 else null end) as Count2016a,
  count(case when p.CreationDate between '2016-7-1' and '2017-1-1' then 1 else null end) as Count2016b,
  count(case when p.CreationDate between '2017-1-1' and '2017-7-1' then 1 else null end) as Count2017a,
  count(case when p.CreationDate between '2017-7-1' and '2018-1-1' then 1 else null end) as Count2017b,
  count(case when p.CreationDate between '2018-1-1' and '2018-7-1' then 1 else null end) as Count2018a,
  count(case when p.CreationDate between '2018-7-1' and '2019-1-1' then 1 else null end) as Count2018b,
  count(case when p.CreationDate between '2019-1-1' and '2019-7-1' then 1 else null end) as Count2019a,
  count(case when p.CreationDate between '2019-7-1' and '2020-1-1' then 1 else null end) as Count2019b,
  count(case when p.CreationDate between '2020-1-1' and '2020-7-1' then 1 else null end) as Count2020a,
  count(case when p.CreationDate between '2020-7-1' and '2021-1-1' then 1 else null end) as Count2020b,
  count(case when p.CreationDate between '2021-1-1' and '2021-7-1' then 1 else null end) as Count2021a,
  count(case when p.CreationDate between '2021-7-1' and '2022-1-1' then 1 else null end) as Count2021b,
  count(case when p.CreationDate between '2022-1-1' and '2022-7-1' then 1 else null end) as Count2022a,
  count(case when p.CreationDate between '2022-7-1' and '2023-1-1' then 1 else null end) as Count2022b,
  count(case when p.CreationDate between '2023-1-1' and '2023-7-1' then 1 else null end) as Count2023a,
  count(case when p.CreationDate between '2023-7-1' and '2024-1-1' then 1 else null end) as Count2023b
from Posts p
join PostTags pt on pt.PostId = p.Id
join Tags t on t.Id = pt.TagId
and t.TagName in ('javascript','typescript','python','java','ruby','php','css','go','c#','c++','shell','c','rust','kotlin','scala','swift','powershell','dart','objective-c','r')
group by t.TagName

Enter Parameters

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