Users with the most quick votes by day and vote type

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, 
                votetypeid 
         FROM   suggestededitvotes sev 
         WHERE  userid <> -1), 
     aggs 
     AS (SELECT sev.userid, 
                Cast(sev.creationdate AS DATE) day, 
                sev.votetypeid, 
                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), 
                   sev.votetypeid) 
SELECT userid                   [User Link], 
       userid, 
       day, 
       vt.name                  VoteType, 
       k, 
       Sum(k) 
         OVER ( 
           partition BY userid) totalK 
FROM   aggs 
       INNER JOIN votetypes vt 
               ON aggs.votetypeid = vt.id 
ORDER  BY totalk DESC, k DESC

Enter Parameters

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