0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professionals and students of engineering

WITH downvotes AS
(
SELECT
  post.ownerUserID as victim,
  post.id as post,
  vote.creationDate as date,
  count(vote.id) as number
--  vote.id as id
FROM 
    posts as post
  inner join 
    votes as vote
   on
    vote.postID = post.id
    and datediff(day,post.creationdate,vote.creationdate) > ##threshold##
WHERE
  vote.voteTypeID = 3 -- downvote
GROUP BY
  post.ownerUserID,
  post.id,
  vote.creationDate
)

select 
  victim as [User Link], 
  date,
  count(post) as 'distinct posts', 
  sum(number) as 'total downvotes'
from downvotes
group by victim, date
order by 'distinct posts' desc
/*
SELECT top 25
  reference.victim as [User Link],
  max(reference.post) as [Post Link],
  reference.date,
  count(*) as number
FROM
    downvotes as reference
  inner join 
    downvotes as nearby
   on 
    reference.victim = nearby.victim
   and
    reference.post  != nearby.post
   and
    reference.date = nearby.date
--    abs(datediff(day, reference.date, nearby.date)) <= ##dateThreshold##
GROUP BY
  reference.victim,
  reference.date
HAVING
  count(*) >= ##countThreshold##
ORDER BY 
  count(*) desc
    */

fork query

edited sep 9 12 anonymous

created sep 9 12 KevinN