0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for administrators and users of the CiviCRM Constituent Relationship Management software

-- Free Mentor: Find answers based on top ranked users for a given tag that resonate with you.

DECLARE @UserID Int = ##UserID:Int?943827##;
DECLARE @TagName Nvarchar(Max) = '##TagName?javascript##';

WITH UserData AS (
  SELECT
    a.OwnerUserId AS UserId,
    SUM(a.Score) AS TotalScore,
    COUNT(a.Id) AS Answers
  FROM Posts a
  JOIN PostTags pt ON a.ParentId = pt.PostId
  JOIN Tags t ON pt.TagId = t.Id
  WHERE t.TagName = @TagName
    AND a.CommunityOwnedDate IS NULL
    AND a.OwnerUserId IS NOT NULL
  GROUP BY a.OwnerUserId
),
RankedUsers AS (
  SELECT
    RANK() OVER(ORDER BY TotalScore DESC) AS Ranking,
    UserId,
    TotalScore,
    Answers
  FROM UserData
),
MaxRank AS (
  SELECT MAX(Ranking) AS MaxRanking FROM RankedUsers
)

-- Show top 100
SELECT TOP 100
  r.Ranking,
  CAST(ROUND(r.Ranking * 100.0 / m.MaxRanking, 2) AS NUMERIC(36, 2)) AS [Top],
  r.UserId AS [User Link],
  r.TotalScore AS [Total Score],
  r.Answers
FROM RankedUsers r, MaxRank m
WHERE r.UserId IS NOT NULL
ORDER BY
  IIF(r.UserId = @UserID, 0, 1),
  r.TotalScore DESC;

fork query

edited mar 24 Ryan.Dennler

created mar 23 anonymous