0

Please login or register to vote for this query.

(click on this box to dismiss)

Looks for posts with 9, 24, 99 total score Or favorited by 24 people Ordered by most remarquable then by total views

Q&A for professional and enthusiast programmers

-- Questions close to be nice, good, great or favorite
-- Looks for posts with 9, 24, 99 total score
-- Or favorited by 24 people
-- Ordered by most remarquable then by total views

 
set nocount on 

declare @VoteStats table (PostId int, up int, down int, fav int) 

insert @VoteStats
select
    PostId, 
    up = sum(case when VoteTypeId = 2 then 1 else 0 end), 
    down = sum(case when VoteTypeId = 3 then 1 else 0 end),
    fav =sum(case when VoteTypeId = 5 then 1 else 0 end)
    
from Votes
where VoteTypeId in (2,3,5)
group by PostId

set nocount off

select top 100 p.id as [Post Link] , up-down as score, fav, ViewCount, up, down from @VoteStats 
join Posts p on PostId = p.Id
where ( ((ViewCount >990 and ViewCount < 1000) or (up - down = 9) or (up - down = 24) or (up - down > 95 and up-down < 100) or (fav=24))) and p.CommunityOwnedDate is null and p.ClosedDate is null
order by fav desc,up-down desc,ViewCount desc

fork query

created may 12 11 ogerard