Evolution of top tags (start and end dates)

1

Please login or register to vote for this query.

(click on this box to dismiss)

Russian Language

Q&A for students, teachers, and linguists wanting to discuss the finer points of the Russian language

-- Show how today's top tags have evolved. Check the graph!
-- What to set:
-- * topn: how many tags to consider (top n as of today)
-- * dateFromYYYY: start year 
-- * dateFromMM: start month
-- * dateToYYYY: end year 
-- * dateToMM: end month
-- * Tag: like wildcard for selecting a special group of tags

-- Try topn=5, dateFromYYYY=2010, dateFromMM=1, Tag:%
-- Around 2017 is when Python and Java cross, Java & Javascript follow 
-- similar pattern, it looks like in 2020 React is surpassing HTML

DECLARE @dateFromYYYY int
DECLARE @dateFromMM int
DECLARE @dateToYYYY int
DECLARE @dateToMM int

SET @dateFromYYYY = ##dateFromYYYY:int?2010##
SET @dateFromMM = ##dateFromMM:int?1##
SET @dateToYYYY = ##dateToYYYY:int?2014##
SET @dateToMM = ##dateToMM:int?1##

create table #tags (Date date, TagName nvarchar (35), Count int);

insert into #tags 

select 
DATEFROMPARTS(YEAR(p.CreationDate),MONTH(p.CreationDate),1) as [Date], t.TagName,
count(*) as Count
from Posts p
join PostTags pt on pt.PostId = p.Id
join Tags t on t.Id = pt.TagId 
where 
DATEFROMPARTS(YEAR(p.CreationDate),MONTH(p.CreationDate),1)<
DATEFROMPARTS(YEAR(getdate()),MONTH(getdate()),1)
AND
(t.TagName like ##Tag:string?%##)
group by t.TagName,
         DATEFROMPARTS(YEAR(p.CreationDate),MONTH(p.CreationDate),1)
order by [Date] desc;

select * from #tags 
where 
Date >= DATEFROMPARTS(@dateFromYYYY,@dateFromMM,1)
AND Date < DATEFROMPARTS(@dateToYYYY,@dateToMM,1)
AND TagName in
(
select TOP ##topn:int?5## TagName from #tags 
where 
DATEDIFF(month,Date,getdate()) =1
order by Count desc
)
order by [Date] asc;

Enter Parameters

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