4

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

declare @thismonth datetime = dateadd(month, datediff(month, 0, getdate()), 0);

with TopTags as
(
  select top 1 TagId
  from PostTags pt
  join Posts p on p.Id=pt.PostId
  join Tags t on t.Id=pt.TagId
  where t.TagName in ('##tagname##')
  --where CreationDate > getdate()-2000 -- most popular tags, in terms of post count, of last 2000 days
  --group by TagId
  --order by count(*) desc
)
select 
  mo = coalesce(a.mo, v.mo),
  TagName = coalesce(a.TagName, v.TagName),
  --a.[Anon Count],
  v.[Total Votes]
from
(
  select mo, TagName, count(*) as [Anon Count]
  from 
  (
    select pf.Id, t.TagName, -- added pf.Id to Shog's query to count every single vote attempt
      dateadd(month, datediff(month, 0, min(pf.CreationDate)), 0) mo
    from PostFeedback pf
    join PostTags pt 
      on pt.PostId=pf.PostId
    join TopTags tt 
      on tt.TagId=pt.TagId
    join Tags t 
      on t.Id=pt.TagId
    where pf.CreationDate between dateadd(month, -96, @thismonth) and @thismonth-1 -- 6 years
    group by pf.Id, pf.PostId, t.TagName
  ) ot
  group by mo, TagName
) a
full outer join
(
  select mo, TagName, count(*) as [Total Votes]
  from
  (
    select pv.Id, t.TagName, -- added pf.Id to Shog's query to count every single vote attempt
      dateadd(month, datediff(month, 0, min(pv.CreationDate)), 0) mo
    from Votes pv
    join PostTags pt 
      on pt.PostId=pv.PostId
    join TopTags tt 
      on tt.TagId=pt.TagId
    join Tags t 
      on t.Id=pt.TagId
    where pv.CreationDate between dateadd(month, -96, @thismonth) and @thismonth-1 -- 6 years
      and pv.VoteTypeId in (2, 3)
    group by pv.Id, pv.PostId, t.TagName

  ) ot
  group by mo, TagName
) v
 on a.mo = v.mo
 and a.TagName = v.TagName
where a.[Anon Count] is not null
  and v.[Total Votes] is not null
order by mo

view current version

edited feb 24 16

created feb 24 16 SamtheBrand