12

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for finance professionals and academics

-- UserId: User ID     "Enter a user's ID."
WITH eligableTags AS (
    --  There is some cutoff; this is best SWAG at moment
    SELECT      t.Id
    FROM        Tags t
    INNER JOIN  PostTags pt     ON t.Id         = pt.TagId
    INNER JOIN  Posts q         ON q.Id         = pt.PostId
    GROUP BY    t.Id
    HAVING      Count (q.ID) >= 200
),
answerScoreByUserAndTag AS (
    SELECT      et.Id        AS TagId
                , a.OwnerUserId
                , Sum (
                    CASE    WHEN a.CommunityOwnedDate IS NULL
                            THEN a.Score  ELSE 0
                    END
                )  AS [Score]
                , Count (a.Id)   AS [Elgbl Ans]
                , Sum (
                    CASE    WHEN a.CommunityOwnedDate IS NULL
                            THEN 0  ELSE 1
                    END
                )  AS [CW ans]
    FROM        eligableTags et
    INNER JOIN  PostTags pt     ON et.Id        = pt.TagId
    INNER JOIN  Posts q         ON q.Id         = pt.PostId
    INNER JOIN  Posts a         ON a.ParentId   = q.Id
    WHERE       a.OwnerUserId   IS NOT NULL
    AND         a.Score > 0
    GROUP BY    et.Id, a.OwnerUserId
    -- There might also be user rep cutoffs?
),
targUsersEligableTags AS (
    SELECT      asbut.TagId
                , (SELECT t.TagName  FROM Tags t  WHERE t.Id = asbut.TagId)  AS TagName
                , asbut.OwnerUserId     AS TargUser
                , asbut.Score           AS tuScore
                , asbut.[CW ans]
                , asbut.[Elgbl Ans]
    FROM        answerScoreByUserAndTag asbut
    WHERE       asbut.OwnerUserId = ##UserId:int##
    AND         asbut.Score > 0
),
tagStats AS (
    SELECT      tut.TagId
                , Count (asbut.OwnerUserId)  AS ttlUsers
                , Sum ( CASE WHEN asbut.Score > tut.tuScore
                    THEN 1  ELSE 0
                    END
                ) AS bttrUsers
    FROM        targUsersEligableTags tut
    INNER JOIN  answerScoreByUserAndTag asbut  ON (
        asbut.TagId = tut.TagId  AND  asbut.OwnerUserId <> tut.TargUser
    )
    GROUP BY    tut.TagId
)
SELECT -- TOP 50  -- Top limit here really doesn't save anything
            tut.TagName  -- Do not rename.  `TagName` is magic in SEDE
            , ( CASE    WHEN ts.bttrUsers = 0
                        THEN 'Top User!'
                        ELSE Format (100.0 * ts.bttrUsers / ts.ttlUsers, 'N1')
                END
            ) AS [Percentile]
            , tut.tuScore   AS [Ans Score]
            , tut.[Elgbl Ans]
            , tut.[CW Ans]
FROM        targUsersEligableTags tut
INNER JOIN  tagStats ts     ON ts.TagId = tut.TagId
ORDER BY    (100.0 * ts.bttrUsers / ts.ttlUsers) ASC   -- Subdecimal sorting borked, hence the * 100
            , tut.TagName

fork query

created apr 26 18 Brock Adams