How many comments will be hidden?

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 @number_threshold int = 5
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
),
chatty_posts as (
  select postid
  from comments
  group by postid
  having count(id) > @number_threshold
)

select count(*) as hidden_count 
from comment_weights cw
inner join chatty_posts cp
on cp.postid = cw.postid
where weight < @weight_threshold

select num_comments, count(p.id) as post_count
from posts p
inner join (
select p2.id as postid,
count(c.id) as num_comments
from posts p2
left join comments c on p2.id=c.postid
group by p2.id
) counts on p.id = counts.postid
group by num_comments

Enter Parameters

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