0

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 researchers and developers who explore the principles of a system through analysis of its structure, function, and operation

-- 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
        WHEN a.PostTypeId = 2 AND v.VoteTypeID = 2 THEN  0
        WHEN q.PostTypeId = 1 AND v.VoteTypeID = 2 THEN   5
        WHEN v.VoteTypeID = 3 THEN -2
        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 OR   -- Questions and Answers
        q.PostTypeID = 1
    )
    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,
    u.Id UserId,
    u.DisplayName UserDisplayName,
    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), 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 UserId = @UserId
--SELECT * FROM VoteTypes
select PostTypeId, COUNT(PostTypeId) from Posts GROUP BY PostTypeId

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