4

If I want to find the longest (by character count) answers by a specific user, across entire SE network, is there a good Data Explorer query to do so?

1
  • Here is a simple one that does it for one site at once.
    – Glorfindel Mod
    Jun 13 '16 at 19:01
5

Based on this answer, which helped me write a cross-site query, I constructed the following SEDE query. It needs some work, though.

You need the account ID of the user, which can be found in the URL of the network profile - mine is 6085540. The magic post link does not work, you need to create the URL manually, e.g. my longest answer on Stack Overflow has ID 30833733, and can be found on https://stackoverflow.com/a/30833733.

(Thanks @rene for fixing the parameter issue!)

declare @AccountId int = ##AccountId##
-- AccountId: The account ID of your network profile, e.g. https://stackexchange.com/users/6085540/glorfindel:

declare db_c cursor for select [name] 
                         from sys.databases 
                         where database_id > 5 -- skip master, temp, model, msdb, Data.SE

declare @db_c_name sysname -- holds name of db after fetch
declare @sql nvarchar(max)

create table #results(site nvarchar(250), postid nvarchar(max), length int)

open db_c
fetch next from db_c into @db_c_name
while(@@fetch_status = 0)
begin
    set @sql = N'use '+ quotename(@db_c_name) +';
    insert into #results
    select '''+ quotename(@db_c_name) +''', p.id, len(p.body)
      from posts as p
      inner join users as u
        on u.id = p.owneruserid
        where u.accountid = ' + cast(@accountid as nvarchar) + '
        and p.posttypeid = 2';
    exec (@sql)
    fetch next from db_c into @db_c_name
end;
close db_c;
deallocate db_c;

select * from #results order by length desc
3
  • 1
    I was on the wrong track, here is my version that fixes your issues
    – rene
    Jun 13 '16 at 19:59
  • Thanks for sharing - I see your query takes questions into account as well, and it returns only the top post per site. At the moment, I'm too tired to add another ±50 lines of SQL just to add the post link.
    – Glorfindel Mod
    Jun 13 '16 at 20:17
  • According to this query I have an answer that exceeds 30,000 characters (screenshot). Interestingly, it is the same answer that prompted this question about character limits. I wonder if there's a connection.
    – Alex
    Feb 8 at 1:31
1

I was looking for an opportunity to use How can I get URLs and site names for a cross-site SEDE query? and this answer is going to do exactly that.

The following SEDE query takes the accountid of Shog9 (that is the number in the url of your networkprofile) and then uses the table in the Wiki answer of Glorfindels post to drive the select statement to construct a multi-database union query in one variable (called sql) which is then executed.

Here is is the whole query. The first CTE is the query to parse the table in the Wiki answer, the rest is the actual per site union.

declare @sql nvarchar(max)

;with alldbs as
(
select [Url]
     , SiteName
     , DatabaseName
from
(
  select (pid - 8) pid
     , value [Url]
     , Lead(value, 1) over (order by pid) [SiteName]
     , Lead(value, 2) over (order by pid) [DatabaseName]
  from (
    SELECT row_number() over(order by @@identity ) pid
         , LEFT(TRIM(VALUE), 100) Value FROM STRING_SPLIT(
    -- fetch Markdown for post containing table with site details
    (SELECT TOP 1  Text
     FROM [StackExchange.Meta].dbo.PostHistory
     WHERE PostId = 359796 AND PostHistoryTypeId = 5
     ORDER BY CreationDate DESC)
    , '|')
    WHERE LEN(TRIM(REPLACE(REPLACE(VALUE, CHAR(10), ''), CHAR(13), ''))) > 0
  ) src
  where pid >= 8
) data
where pid % 3 = 0
and url like 'https://%' -- ouch 
)

select @sql = concat(string_agg(
concat(
N'select * 
from (
select top 1 
         posts.id
       , ''', sitename, N''' sitename
       , concat(''', url, N'/q/'',posts.id,''|'', q.title) [Link]
       , len(posts.body) [Post Length]
from ', convert(nvarchar(max), quotename(databasename)), N'.dbo.posts
inner join ', convert(nvarchar(max), quotename(databasename)), N'.dbo.posts q on posts.parentid = q.id
inner join ', convert(nvarchar(max), quotename(databasename)), N'.dbo.users on users.id = posts.owneruserid
where accountid = ##userid?811##
and posts.posttypeid = 2 -- Answer, you asked for it
order by len(posts.body) desc
) data
'
) 
, N' union all ')
, N' order by [Post Length] desc')
from alldbs

print @sql

exec (@sql)

That user is a meta addict:

posts with length

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

You must log in to answer this question.

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