I wanted to get some stats on who has done the most edits on Code Review, so I wrote the following query on SE Data Explorer. All improvement suggestions are welcome.
Note that I used a cursor to partition top users by year because at first there was a fundamental flaw in my logic trying to get the top users overall and then grouping them by years, which gave incorrect results.
The execution plan doesn't look too bad, considering the cursor only iterates a total of 6 times (2011-2016), but if it can be improved without degrading readability too much, I'd like to know.
/*
Aggregate the top 10 users with the most post edits
for each year, along with relevant statistics.
*/
/* Filter for Edit events: */
declare @EditPostHistoryTypes table (Id int);
insert into @EditPostHistoryTypes
select Id
from PostHistoryTypes
where Name like 'Edit%';
/* All edits on record: */
declare @CountAllEdits int = (
select count(Id)
from PostHistory
where
PostHistoryTypeId in (select Id from @EditPostHistoryTypes)
and PostId in (
select Id from Posts
where DeletionDate is null
)
);
/* Hold top editors per year for main query: */
declare @TopEditorsPerYear table (
[Year] int,
UserId int,
CountByYear int,
TotalCount int
);
/* Get all years with edit activity: */
declare _years cursor for
select distinct datepart(year, CreationDate)
from PostHistory
where PostHistoryTypeId in (
select Id
from PostHistoryTypes
where Name like 'Edit%'
);
declare @currentYear int;
/* Cursor to split top editors into yearly partitions: */
open _years;
fetch next from _years into @currentYear;
while @@fetch_status = 0
begin;
insert into @TopEditorsPerYear ([Year], UserId, CountByYear, TotalCount)
select top 10
datepart(year, ph.CreationDate) as [Year],
ph.UserId,
count(ph.Id),
(select count(Id) from PostHistory as phInline where phInline.UserId = ph.UserId)
from PostHistory as ph
where PostHistoryTypeId in (
select Id
from PostHistoryTypes
where Name like 'Edit%'
)
and datepart(year, CreationDate) = @currentYear
and UserId is not null
group by
datepart(year, CreationDate),
UserId
order by count(Id) desc;
fetch next from _years into @currentYear;
end;
/* Cleanup cursor: */
close _years;
deallocate _years;
/* Main query: */
select
[Year Rank] = row_number() over (
partition by tops.[Year]
order by tops.CountByYear desc
),
[Year] = tops.[Year],
[User Link] = hist.UserId,
[Annual Total] = tops.CountByYear,
[% Year/User] = cast((cast(tops.CountByYear as decimal(10,2)) / tops.TotalCount) * 100 as decimal(5,2)),
[% Year/Site] = cast((cast(tops.CountByYear as decimal(10,2)) / @CountAllEdits) * 100 as decimal(5,2)),
[% Total/Site] = cast((cast(tops.TotalCount as decimal(10,2)) / @CountAllEdits) * 100 as decimal(5,2)),
[Grand Total] = tops.TotalCount
from
PostHistory as hist
join @TopEditorsPerYear as tops on hist.UserId = tops.UserId
where
PostHistoryTypeId in (select Id from @EditPostHistoryTypes)
and PostId in (select Id from Posts where DeletionDate is null)
group by
hist.UserId,
tops.[Year],
tops.CountByYear,
tops.TotalCount
order by
[Year Rank] asc,
[Year] asc;