How many extra comments will be shown on long threads?

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

declare @weight_threshold int = 10
declare @score_cap int = 10
declare @min_len int = 15
declare @len_step int = 15
declare @len_cap int = 9
declare @age_cap int = 10;

with comment_weights as (
     select case 
               when score is null then 0
               when score < @score_cap then score
               else @score_cap
             end
           +
             case
               when (len(Text) - @min_len)/@len_step < @len_cap 
               then (len(Text) - @min_len)/@len_step
               else @len_cap
             end
           +
             case
             when cast(GetUTCDate() - CreationDate as int) < @age_cap 
                 then @age_cap - cast(GetUTCDate() - CreationDate as int)
               else 0
           end as weight,
          PostId 
    from comments
)

select top 100 count(*) - 5, PostId as [Post Link]
from comment_weights
--     join Posts p on (p.Id = PostId)
where weight >= @weight_threshold
--      and Title <> 'unknown'
group by PostId
having count(*) > 5
order by count(*) desc;

Enter Parameters

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