Network accounts that edited their own posts since a date

0

Please login or register to vote for this query.

(click on this box to dismiss)

Computational Science

Q&A for scientists using computers to solve scientific problems

declare @cutoffdate nvarchar(10) = ##date:string?2024-04-06##
declare @score int = ##score##
declare @sql nvarchar(4000);


create table #results(accountid integer, editcount integer, site sysname);

set @sql = concat('
insert into #results
select *, [$site name$] site
from (
select -- top 1000
           u.accountid
         , count(distinct ph.postid) [count]
from posthistory ph
inner join posts p on p.id = ph.postid
inner join users u on u.id = ph.userid
where ph.posthistorytypeid = 5 -- edits 
and ph.creationdate > ''', @cutoffdate,''' -- after date
and p.score > ',  @score  , '
and p.creationdate < ''', @cutoffdate , '''
-- edit on own post
and p.owneruserid = ph.userid
group by u.accountid 
) data
');

exec sede_ineachdb @sql, 1 /*main*/, 1 /*meta*/, 1 /*mse*/ , 0
  
select 
       rank() over(order by sum(editcount) desc) [Rank]
     , concat('https://stackexchange.com/users/', accountid, '|', accountid) [Network Link]
     , sum(editcount) [total posts]
     , count(*) [total sites]
     , string_agg(site, ', ') [sites]
from #results
where accountid is not null
group by accountid
order by sum(editcount) desc

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results