Top percentile values for a given user in all of his (eligible) tags


Please login or register to vote for this query.

(click on this box to dismiss)

Operations Research Meta

Q&A about the site for operations research and analytics professionals, educators, and students

-- set showplan_all on
-- go 
-- UserId: User ID     "Enter a user's ID."
;WITH qforuser AS (
  from posts q
  inner join posts p on = coalesce(p.parentid,
  where p.owneruserid = ##UserId##

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  qforuser 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
                )  AS [Score]
                , Count (a.Id)   AS [Elgbl Ans]
                , Sum (
                    CASE    WHEN a.CommunityOwnedDate IS NULL
                            THEN 0  ELSE 1
                )  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
                ) 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')
            ) 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

Enter Parameters

Switch to main site
loading Hold tight while we fetch your results