7

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

Suppose there are two users for a tag.

User 'A' with 5608 posts for a tag. And User 'B' with 2627 posts.

List
User A
User B

So far I have written a query to list the top 100 users of a tag with the most posts (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. How can I fix it?

Error message

Incorrect syntax near the keyword 'WHERE'.

I have referred to these posts:

  • How can I improve this post? – Pie Apr 25 '19 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 '19 at 16:28
8

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

Keep in mind SEDE is updated once a week, on Sunday. Give a big shout-out to Monica Cellio for the awesome SEDE Tutorial she wrote.
Say "Hi" in SEDE chat.

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

You must log in to answer this question.

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