0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for builders and users of networked sensors and control devices in the contexts of smart homes, industry automation, or environmental sensors

DECLARE @cnt bigint 
SET @cnt = (
  SELECT COUNT(*) 
  FROM Users
  WHERE (1=1) 
  -- AND Reputation > 1000 
  AND DATEDIFF(year, CreationDate, CURRENT_TIMESTAMP)  = 10
); 


SELECT TOP 15 
   Id
  ,DisplayName
  ,Location 
  ,Reputation
  ,CreationDate
  ,UpVotes 
  ,DownVotes 
  ,RANK() OVER(ORDER BY Reputation) AS Rank 
  ,DENSE_RANK() OVER(ORDER BY Reputation) AS DenseRank  
  ,PERCENTILE_CONT(0.5) 
   WITHIN GROUP (ORDER BY Reputation)
   OVER (PARTITION BY 1) 
   AS MedianReputation
   ,@cnt AS UserCount
FROM Users
WHERE (1=1) 
-- AND Reputation > 1000 
AND DATEDIFF(year, CreationDate, CURRENT_TIMESTAMP)  = 10
ORDER BY 
     CASE WHEN id = '##UserId##' THEN 0 ELSE 1 END 
    ,Reputation DESC

fork query

edited dec 5 19

created dec 5 19 anonymous