Top user (+ info) by tags per location

1

Please login or register to vote for this query.

(click on this box to dismiss)

Physics

Q&A for active researchers, academics and students of physics

-- Top users per tag

declare @tag_id1 int = (select id
    from tags
    where tagname = LOWER('##Skill1##')
);

declare @tag_id2 int = (select id
    from tags
    where tagname = LOWER('##Skill2##')
);

declare @tag_id3 int = (select id
    from tags
    where tagname = LOWER('##Skill3##')
);

select top (##Limit##)
    rank() over (order by sum(answers.score) desc) "Rank",
    u1.id "User Link",
    u1.AccountId,
    u1.DisplayName,
    u1.Location,
    u1.Reputation,
    u1.EmailHash,
    u1.ProfileImageUrl,
    u1.WebsiteUrl,
    sum(answers.score) as "Score"
from
    users u1
    join posts answers on (u1.id = answers.owneruserid)
    join posts parent on (answers.parentid = parent.id)
    join posttags on (parent.id = posttags.postid)
where(
    LOWER(posttags.tagid) = LOWER(@tag_id1)
    or LOWER(posttags.tagid) = LOWER(@tag_id2)
    or LOWER(posttags.tagid) = LOWER(@tag_id3)
        --or posttags.tagid = @tag_id3
    ) and
    LOWER(u1.Location) LIKE LOWER('%##Location##%')
    and LEN(u1.WebsiteUrl) > 0
    --and answers.posttypeid = 2  -- answers only

group by
    u1.id,
    u1.AccountId,
    u1.DisplayName,
    u1.Location,
    u1.Reputation,
    u1.EmailHash,
    u1.ProfileImageUrl,
    u1.WebsiteUrl
order by
    sum(answers.score) desc;

Enter Parameters

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