Tag Rankings Fun

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

Internet of Things Meta

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

-- 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

Enter Parameters

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