Users with the most quick votes by day With Detail Link

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

WITH sev as(


SELECT 
  row_number() over (partition by userID order by id) rn,
  UserId,
  CreationDate
FROM 
  SuggestedEditVotes sev
WHERE 
   UserID <> -1
  )
  , aggs as   (
SELECT 
    
    sev.UserID,
    cast(sev.CreationDate as date) day,
    Count(sev.rn) k
   
    
FROM 
  sev 
  INNER JOIN sev s_prev
    ON sev.UserId = s_prev.UserID
       AND sev.rn = s_prev.rn + 1
WHERE
DATEDIFF(ss, s_prev.CreationDate, sev.CreationDate) < 5
GROUP BY 
  sev.UserID,
  cast(sev.CreationDate as date)
  )
   
SELECT 
    UserId [User Link],
    UserID,
    day,
     k,
     sum(k) over (partition by userID) totalK,
     'http://data.stackexchange.com/stackoverflow/query/76464/suggested-edit-votes-for-a-user-on-a-day?TheUserid=' + cast(UserID as varchar) + '&editday='
     + Cast (day as  varchar) as detail_link
   
FROM aggs
ORDER BY 
totalK  desc, k desc

Enter Parameters

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