0

Please login or register to vote for this query.

(click on this box to dismiss)

Lists all the tags where you have a score over 0 and how many users are doing better than you in that tag.

Q&A for professional and enthusiast programmers

-- Tag Rankings
-- Lists all the tags where you have a score over 0 and how many users are doing better than you in that tag.

select
    TagName,
    count(*) as Rank
from
(
    select
        OwnTagScores.TagId,
        OwnTagScores.Score as MyScore,
        sum(Posts.Score) as TheirScore
    from
    (
        select
            PostTags.TagId as TagId,
            sum(Answers.Score) as Score
        from Posts as Answers
        join PostTags on PostTags.PostId = Answers.ParentId
        where
            OwnerUserId = '##userid##'
            and
            Answers.Score > 0
            and
            Answers.CommunityOwnedDate is null
        group by
            Answers.OwnerUserId,
            PostTags.TagId
    ) OwnTagScores
    join PostTags on OwnTagScores.TagId = PostTags.TagId
    join Posts on Posts.ParentId = PostTags.PostId
    where
        CommunityOwnedDate is null
        and
        Posts.Score > 0
    group by
        Posts.OwnerUserId,
        OwnTagScores.TagId,
        OwnTagScores.Score
) SuperiorUsers
join Tags on Tags.Id = TagId
where
    MyScore < TheirScore
group by
    TagId,
    TagName
order by
    count(*),
    TagName

fork query

created jul 27 11 David Freitas