1

Please login or register to vote for this query.

(click on this box to dismiss)

Put banks in for Tag Name for all. Tag names should be in lower case Put 0 for user ID for all users

Q&A about the site for practitioners, researchers, and students in cognitive science, psychology, neuroscience, and psychiatry

-- Tag Rankings Fun
-- Put banks in for Tag Name for all. Tag names should be in lower case
-- Put 0 for user ID for all users

DECLARE @tagName VARCHAR(255) 
DECLARE @tagInput BIT
SET @tagname = RTRIM(##TagName:string##)
DECLARE @UserID INT = ##UserId##
DECLARE @MinScore INT = ##MinScore##

IF RTRIM(@tagname) = ''
    SET @tagInput = 0 
ELSE
    SET @tagInput = 1
 
IF @tagInput  = 0 and @UserID = 0 
    SELECT 'Don' + '''' + 't do that'
ELSE 
 
WITH UserTagScores AS
(
SELECT  
    a.OwnerUserId,
    t.TagName,
    SUM
    (
        CASE v.VoteTypeID
        WHEN 2 THEN  1
        WHEN 3 THEN -1
        END
    )
    Score
FROM 
    votes v
    INNER JOIN Posts a
    ON v.PostID = a.ID
    INNER JOIN Posts q
    ON a.parentId = q.id
    INNER JOIN PostTags pt
    ON q.id = pt.PostId
    INNER JOIN Tags T
    ON pt.TagId = t.id
WHERE
    v.voteTypeId in (2,3)  -- Up and down votes
    AND a.PostTypeID = 2  -- Answers only
    AND
    (
        a.CommunityOwnedDate IS NULL OR
        a.CommunityOwnedDate > v.CreationDate
    )
    AND 
    (
        @tagInput = 0 OR
        t.TagName = @TagName
    )
    AND a.OwnerUserId IS NOT NULL
GROUP BY
    a.OwnerUserId,
    t.TagName
)
,
UserTagRank AS
(
SELECT
    TagName,
    s.OwnerUserId,
    u.DisplayName,
    RANK() OVER (PARTITION BY TagName ORDER BY Score DESC) Rank,
    Score
FROM
    UserTagScores s
INNER JOIN
    Users u
ON
    s.OwnerUserId = u.Id
)
,
UserTagRankPct AS
(
SELECT
    *,
    CAST(CAST(ROUND(100 - CAST((Rank - 1) * 100 AS FLOAT) / (SELECT COUNT(*) FROM UserTagScores WHERE Score >= @MinScore), 3) AS NUMERIC(6, 3)) AS NVARCHAR) Percentile
FROM
    UserTagRank
WHERE
    Score >= @MinScore
)

--,
--userTagRank2 AS
--(
--SELECT
--    COUNT(*) + 1 Rank
--FROM
--    UserTagScores
--WHERE
--    Score >
--    (
--    SELECT
--        Score
--    FROM
--        UserTagScores
--    WHERE
--        OwnerUserId = @UserID
--    )
--)

SELECT
    *
FROM
    UserTagRankPct
--WHERE
--    OwnerUserId = @UserID


--WHERE
--    OwnerUserId = @UserID

--SELECT 
--    TagName,
--    Rank,
--    OwnerUserId ,
--    OwnerUserId [User Link],
--    Score
--FROM
--    userTagRank 
--WHERE 
--    (OwnerUserId = @UserID OR @UserID = 0)
--    AND Score >= 1000
--ORDER BY
--    Score DESC,
--    TagName,
--    rank

fork query

edited sep 3 18 anonymous

created nov 15 11 conradfrix