Tags with the most views per question

1

Please login or register to vote for this query.

(click on this box to dismiss)

For each tag, calculate the views per question and the views per day per question.

Science Fiction and Fantasy

Q&A for science fiction and fantasy enthusiasts

declare @numtags int = ##NumTags:int?1000##
declare @mincount int = ##MinCount:int?20##
declare @excludetag nvarchar(35) = ##ExcludeTag:string?-##
declare @latest datetime = (select max(CreationDate) from PostHistory)

select top (@numtags)
    TagName,
    count(Posts.Id) as Count, -- nearly the same as Tags.Count
    sum(ViewCount) as Views,
    1.0*sum(ViewCount)/count(Posts.Id) as ViewsPerQ,
    1.0*sum(ViewCount/datediff(day, Posts.CreationDate, @latest))/count(Posts.Id)
        as ViewsPerDayPerQ
into #table
from Tags
left join PostTags on PostTags.TagId = Tags.Id
left join Posts on Posts.Id = PostTags.PostId
where Posts.PostTypeId = 1 and Tags.Count >= @mincount
    and (@excludetag = '-' or Tags not like '%<' + @excludetag + '>%')
    and datediff(day, Posts.CreationDate, @latest) > 0
group by TagName
order by count(Posts.Id) desc, Tags.TagName

select TagName, Count, Views,
    round(ViewsPerQ, 0) as [Views per question],
    round(ViewsPerDayPerQ, 2) as [Views per day per question]
from #table
order by ViewsPerQ desc

declare @scale float = (select max(ViewsPerQ) / max(ViewsPerDayPerQ) from #table)

select row_number() over (order by ViewsPerQ desc) as Rank,
    ViewsPerQ as [Views per question],
    ViewsPerDayPerQ * @scale as [Views per day per question, scaled (ignore numbers)]
from #table

Enter Parameters

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