18

This is probably very easy for anyone who knows how Data.SE and whatever programming language it uses, but beyond my non-existent programming knowledge.

Is there a way to get a list of who has asked the most questions network-wide? In other words, summing a user's question counts across all sites, and sorting users from the top?

I've found these two queries, but they're only for specific sites and I don't know how to make network-wide queries (but I know it's possible as I've seen it done with other queries).

1
15

Here is the query you're looking for. It wasn't straightforward because of the massive size of Stack Overflow. It includes questions on Meta sites as well, so the number might not match what you'd expect from a user's network profile. (And hidden communities may play a role too.)

enter image description here

The query is dynamic, so you can search for the most prolific answerers too.

1
  • 3
    FWIW, I'm pretty sure @rene will have a method to make a query that is easier on the database. Mine will timeout if you put the MinimumNumberOfPosts too low.
    – Glorfindel Mod
    Dec 24 '21 at 14:58
13

I'm pretty sure @rene will have a method to make a query that is easier on the database

Challenge accepted.

I've created a batch of insert statements per database instead of an union. More importantly I've added an inner query for the per database select so I could join and group on the users.id as that column does have an index. That inner query gives me one row per user that I then join again with the users table to get the displayname and accountid.

That gives me a #results temp table with all post counts for each user over all databases. Adding the group by and order over that #result is trivial.

Here is the whole query:

CREATE TABLE #results (site NVARCHAR(250),
                       accountid INT,
                       displayname NVARCHAR(40),
                       postscount int);
CREATE INDEX #ix_account on #results(accountid);

DECLARE @sql NVARCHAR(MAX) = ''

SELECT @sql= string_agg(concat(N'INSERT INTO #results
SELECT ''', convert(nvarchar(max),name) , N'''
     , uall.AccountId
     , uall.DisplayName
     , cnt
from (
  select u.id
       , count(p.id) [cnt]
  FROM ', convert(nvarchar(max), QUOTENAME(name)), N'.dbo.Posts AS p  
  INNER JOIN ', convert(nvarchar(max), QUOTENAME(name)), N'.dbo.Users AS u
    ON p.OwnerUserId = u.Id
  WHERE p.PostTypeId = ', ##posttypeid:int?1##, N'group by u.id) data
inner join ', convert(nvarchar(max), QUOTENAME(name)), N'.dbo.users uall 
  on uall.id = data.id
'), N';')
FROM sys.databases WHERE database_id > 5
 

EXEC (@sql);

select top 50000 
       concat(
           'https://stackexchange.com/users/'
         , accountid
         , '?tab=accounts'
         , '|'
         , min(displayname)
         ) [Network profile]
     , sum(postscount) [total number of posts]
from #results
group by accountid
order by sum(postscount) desc

and when run today this is the result:

top posters network wide

Now we only have to find the reason our results are slightly off ...

I have also looked at the execution plan:

totally screwed up, mangled and non-sensical execution plan

Pretty easy on the database ...

Keep in mind SEDE is updated once a week on Sunday.
Use the fine SEDE Tutorial written by the wonderful Monica Cellio.
Say "Hi" in SEDE chat.

1
  • 5
    Pretty easy on the database but not on my eyes :P Still, I'm happy you don't fail to disappoint me. Merry Christmas! P.S. results are slightly different but not meaningful: I simply take the MAX of the display names across the network.
    – Glorfindel Mod
    Dec 24 '21 at 19:59

You must log in to answer this question.

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