Users with the most quick votes by day With Detail Link and Since input

0

Please login or register to vote for this query.

(click on this box to dismiss)

Craft CMS Meta

Q&A about the site for administrators, end users, developers and designers for Craft CMS

WITH sev as(


SELECT 
  row_number() over (partition by userID order by id) rn,
  UserId,
  CreationDate
FROM 
  SuggestedEditVotes sev
  
WHERE 
  CreationDate > '##Since##'
  and
  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) < ##Speed:Int##
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