5

I am trying to replicate the top user page with user sorted by number of post for a specific tag.

Suppose there are two users for tag.

User 'A' with 5608 post for tag. And User 'B' with 2627 post.

List 
User A
User B

So far I have written query to list top 100 users of a tag with most post( answers and questions)

The query is below

DECLARE @tagName nvarchar(35) = '##tagName##'

Select Top 100 OwnerUserId from Posts order by post desc 
WHERE 
    Tags.TagName = @tagName;

But I could not write correct query for it?

Error message

Incorrect syntax near the keyword 'WHERE'.

I have refered to these post

  • How can I improve this post? – Pie Apr 25 at 16:13
  • 1
    @smileycreations15 No, SEDE is on-topic here. This is a question about that, not a generic SQL question. That said, the SQL query is misformatted. That's not how SQL works. – fbueckert Apr 25 at 16:28
4

You can't reliably recreate that as not all reputation gained and lost is kept in SEDE.

A close approximation would be this query.

It joins the questions and answers with the posttags table (notice the use of coalesce because answers don't have tags, so we want the question postid for those) and then with the tags tabel. By taking the sum of the score of a post and then grouping on the owneruserid we should get a close enough result that gives you plenty of insight.

DECLARE @tagName nvarchar(35) = '##tagName##'

Select Top 100 
         OwnerUserId as [User Link] 
       , sum(score) score
       , count(*) [# posts]
       , sum(case posttypeid when 1 then score else 0 end) [question score]
       , sum(case posttypeid when 2 then score else 0 end) [answer score]
       , sum(case posttypeid when 1 then 1 else 0 end) [# questions]
       , sum(case posttypeid when 2 then 1 else 0 end) [# answers]
from Posts p
inner join posttags pt on pt.postid = coalesce(p.parentid, p.id)
inner join tags t on t.id = pt.tagid
WHERE 
    Tagname = @tagName
and owneruserid is not null
and posttypeid in (1,2) -- only questions and answers
and communityowneddate is null -- only include posts that give reputation
group by owneruserid    
order by sum(score) desc 

if you want to run this on the datadump, you'll find that you'll be missing the posttags table. You can then remove those inner joins and instead do:

WHERE 
    Tags like '%<' + @tagName + '>%'

That will cause a full table scan on the Posts table so grab some coffee if you're hosting it on an under-powered boxx.

For the PHP tag, this will be the result today: SEDE query results for the PHP tag

and here is the screenshot from the PHP top users page:

PHP top users on stack overflow

Kee in mind SEDE is updated once a week, on Sunday.

  • this query gives Gordon with 2627 higher than deceze with 5608 post. – Pie Apr 25 at 16:57
  • 1
    @Pie now it doesn't – rene Apr 25 at 17:03
  • what was wrong? – Pie Apr 25 at 17:04
  • 1
    community owned posts were included – rene Apr 25 at 17:05

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .