Where does USER rank in TAG for the past DAYS

0

Please login or register to vote for this query.

(click on this box to dismiss)

Music Fans Meta

Q&A about the site for music historians, critics, and fans

DECLARE @since DATETIME
SET @since=DateAdd(d, ##DAYS:int?30## * -1, GETDATE())

DECLARE @Rankings TABLE
(
     RANK INT,
     SCORE INT,
     ID INT
);

INSERT INTO @Rankings
SELECT TOP 10000
   ROW_NUMBER() OVER(ORDER BY COUNT(Answers.Score) DESC),
   COUNT(Answers.Score) * 10 AS Rep,
   Users.Id

FROM Posts AS Answers

   INNER JOIN Posts AS Questions
   ON Questions.Id = Answers.ParentId

   INNER JOIN Users as Users
   ON Answers.OwnerUserId = Users.Id
   
   INNER JOIN PostTags
   ON PostTags.PostId = Answers.ParentId
   
   INNER JOIN Tags
   ON Tags.Id = PostTags.TagId
   
WHERE
     Answers.PostTypeId = 2
       AND Answers.CreationDate >= @since
       AND Answers.OwnerUserId IS NOT NULL
       AND Answers.Score != 0
       AND Tags.TagName = '##TAG##'
           
GROUP BY
     
     Users.Id
     
ORDER BY

     Rep DESC
     
;
  
SELECT
    RANK, SCORE
FROM
     @Rankings
WHERE
     ID=##USERID##

Enter Parameters

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