0

Please login or register to vote for this query.

(click on this box to dismiss)

Exploration of the distribution of the various VoteTypes

Q&A for professional and enthusiast programmers

-- Tests for VoteTypeId
-- Exploration of the meaning of the various VoteTypes

 
set nocount on 

declare @VoteStats table (PostId int, up int, down int,
type1 int, type4 int, type5 int, type6 int, type7 int, type8 int, type9 int, type10 int,
type11 int, type12 int, type13 int, type14 int, type15 int, type16 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),
    type5 = sum(case when VoteTypeId = 5 then 1 else 0 end),
    type1 =sum(case when VoteTypeId = 1 then 1 else 0 end),
    type4 = sum(case when VoteTypeId = 4 then 1 else 0 end),
    type6 = sum(case when VoteTypeId = 6 then 1 else 0 end),
    type7 = sum(case when VoteTypeId = 7 then 1 else 0 end),
    type8 = sum(case when VoteTypeId = 8 then 1 else 0 end),
    type9 = sum(case when VoteTypeId = 9 then 1 else 0 end),
    type10 = sum(case when VoteTypeId = 10 then 1 else 0 end),
    type11 = sum(case when VoteTypeId = 11 then 1 else 0 end),
    type12 = sum(case when VoteTypeId = 12 then 1 else 0 end),
    type13 = sum(case when VoteTypeId = 13 then 1 else 0 end),
    type14 = sum(case when VoteTypeId = 14 then 1 else 0 end),
    type15 = sum(case when VoteTypeId = 15 then 1 else 0 end),
    type16 = sum(case when VoteTypeId = 16 then 1 else 0 end)
    
from Votes
-- where VoteTypeId in (2,3)
group by PostId

set nocount off

select top 100 p.id as [Post Link] , up, down, type1+type4+type6+type7+type8+type9+type10+type11+type12+type13+type14+type15+type16 as [others] , ViewCount, type1, type4, type5, type6, type7, type8, type9, type10, type11, type12, type13, type14, type15, type16 from @VoteStats 
join Posts p on PostId = p.Id
where (up > 100 or down > 6 or type5 > 50 or type1 > 1 or type4 >1 or type6 >1 or type7>1 or type8>1 or type9>1 or type10>1 or type11>1 or type12>1 or type13>1 or type14>1 or type15>1) and p.CommunityOwnedDate is null and p.ClosedDate is null
order by up desc,ViewCount desc

fork query

created may 13 11 ogerard