0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for peer programmer code reviews

declare @topbycount as int = ##TopByZombies:int?0##;
declare @epoch as date = DateAdd(week, -1 - ##Weeks:int?52##, CURRENT_TIMESTAMP)
declare @pivot as nvarchar(max)
declare @query as nvarchar(max)
;

create table #toptags (
    Rank int,
    TagId int,
    TagName NVarchar(40),
    TagCount int,
    ZombieCount int,
    ZombieRatio float
)

if (@topbycount = 0)
    with TagStats as (
        select pt.TagId as TagId,
               t.TagName as TagName,
               count(*) as TagCount,
               sum (case when p.AnswerCount is null or p.AnswerCount = 0 then 1 else 0 end) as ZombieCount
        from PostTags pt, Posts p, Tags t
        where p.Id = pt.PostId
          and t.id = pt.TagId
          and p.PostTypeId = 1
          and p.CommunityOwnedDate is null
          and p.ClosedDate is null
        group by pt.TagId, t.TagName
    )
    insert into #toptags
    select TOP ##TopTags:int?15##
           ROW_NUMBER() over (order by (100.0 * ZombieCount) / (1.0 * TagCount) desc) as Rank,
           TagId,
           TagName,
           TagCount,
           ZombieCount,
           (100.0 * ZombieCount) / (1.0 * TagCount) as ZombieRatio
    from TagStats
    where TagCount >= ##TagMin:int?50##
    order by (100.0 * ZombieCount) / (1.0 * TagCount) desc

else

    with TagStats as (
        select pt.TagId as TagId,
               t.TagName as TagName,
               count(*) as TagCount,
               sum (case when p.AnswerCount is null or p.AnswerCount = 0 then 1 else 0 end) as ZombieCount
        from PostTags pt, Posts p, Tags t
        where p.Id = pt.PostId
          and t.id = pt.TagId
          and p.PostTypeId = 1
          and p.CommunityOwnedDate is null
          and p.ClosedDate is null
        group by pt.TagId, t.TagName
    )
    insert into #toptags
    select TOP ##TopTags:int?15##
           ROW_NUMBER() over (order by ZombieCount desc) as Rank,
           TagId,
           TagName,
           TagCount,
           ZombieCount,
           (100.0 * ZombieCount) / (1.0 * TagCount) as ZombieRatio
    from TagStats
    where TagCount >= ##TagMin:int?50##
    order by ZombieCount desc
    
    
    
insert into #toptags
select 0,
       -1,
       'All',
       count(*),
       sum(case when IsNull(p.AnswerCount, 0) = 0 then 1 else 0 end),
       (100.0 * sum(case when IsNull(p.AnswerCount, 0) = 0 then 1 else 0 end)) /
           (1.0 * count(*))
from Posts p
where p.PostTypeId = 1
  and p.CommunityOwnedDate is null
  and p.ClosedDate is null

--select * from #toptags



select distinct DateAdd(day, 7 - DatePart(weekday, CreationDate), Convert(Date, CreationDate)) as WeekEnd
into #weekends
from Users
where CreationDate > @epoch

;

with TagData as (
    select WeekEnd,
           TagName,
           case when exists (
                          select 1
                          from Posts a
                          where a.PostTypeId = 2
                            and a.ParentId = p.Id
                            and a.CreationDate < WeekEnd)
                then 0
                else 1
           end as ZombieCount,
           1 as QuestionCount
    from Posts p, PostTags pt, #weekends w, #toptags t
    where p.CreationDate < WeekEnd
      and p.PostTypeId = 1
      and p.Id = pt.PostId
      and pt.TagId = t.TagId
      and p.CommunityOwnedDate is null
      and p.ClosedDate is null
      and WeekEnd < CURRENT_TIMESTAMP
      


)
select WeekEnd,
       TagName,
       Convert(decimal(10,2), 100.0 * sum(ZombieCount) / sum(QuestionCount)) as ZombieRatio
into #results
from TagData
group by WeekEnd, TagName
;

with AllData as (
    select WeekEnd,
           TagName,
           case when exists (
                          select 1
                          from Posts a
                          where a.PostTypeId = 2
                            and a.ParentId = p.Id
                            and a.CreationDate < WeekEnd)
                then 0
                else 1
           end as ZombieCount,
           1 as QuestionCount
    from Posts p, #weekends w, #toptags t
    where p.CreationDate < WeekEnd
      and p.PostTypeId = 1
      and p.CommunityOwnedDate is null
      and p.ClosedDate is null
      and t.rank = 0
      and WeekEnd < CURRENT_TIMESTAMP
)
insert into #results
select WeekEnd,
       TagName,
       Convert(decimal(10,2), 100.0 * sum(ZombieCount) / sum(QuestionCount)) as ZombieRatio
from AllData
group by WeekEnd, TagName
;

SET @pivot = STUFF((SELECT ',' + QUOTENAME(TagName) 
            FROM #toptags
            Order by Rank
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
;

print @pivot
;

;
set @query = 'select * from #results pivot (sum(ZombieRatio) for TagName in (' + @pivot + ')) as Pivoted  order by WeekEnd ASC'
;
execute(@query)

fork query

edited may 28 14

created may 28 14 rolfl