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?
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
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: