7

I was recently participating on one of the meta sites and several of us wanted to see how that site compared to other sites, specifically with regards to moderator-deleted and moderator-closed posts and comments vs user actions. Then the "2018: a year in moderation" post came like a New Year's miracle. Well, nearly. It's some very good information. However, it cannot be quickly compared to any other SE sites. Specifically, it seems to be missing some vital totals, which would be necessary to make inter-site comparisons.

How can we compare our communities to other communities with the data provided in the posts?

2
  • 3
    Not quite the intent of those posts - they're not normalized, nor is it possible to normalize them accurately for this purpose. If you're looking for something like this: 2018: a year in closing I may be able to provide it though.
    – Shog9
    Jan 4, 2019 at 23:43
  • @Shog9 Yes, I did just find that one. The site where this came up was Skeptics. Here's the post that motivated the discussion. Stats is not really my subject, but LangLangC (in the comment thread there) seems to have a trained grasp on it. Perhaps asking him what he thinks would be useful would be better than asking me.
    – user212646
    Jan 4, 2019 at 23:46

1 Answer 1

6

I was interested in comparing how much users were destroyed across the network in 2022 per site. I'm sure there is tough competition in that category.

It turns out you need a bit of text parsing and cross-site magic to get this query. The query takes a year parameter (but I didn't test for other years then 2022) and a category. The result is then ordered descending by moderator_value. That puts the most prolific site first, right?

declare @jnat int = 1399708
declare @category nvarchar(50) = '##category?Users destroyed##'
declare @categoryLike nvarchar(50) = concat('%', @category, '%')

declare @mod varchar(50) = '<td style="text-align: right;">'
declare @modLike nvarchar(50) = concat('%', @mod, '%')

declare @modEnd varchar(50) = '</td>'
declare @modEndLike nvarchar(50) = concat('%', @modEnd, '%')

declare @sql nvarchar(max)

create table #sitestats (site nvarchar(50), body nvarchar(max) ) 

select @sql = 'insert into #sitestats ' + string_agg(concat(
N'select ''', convert(nvarchar(max),name), N''' site
, body from ', convert(nvarchar(max),quotename(name)), N'.dbo.posts p
inner join ', convert(nvarchar(max),quotename(name)), N'.dbo.users u on u.id = p.owneruserid
where u.accountid = ',@jnat, N'
and p.posttypeid=1 
and p.title like ''##year:int?2022##: %''')
, N' union all ')
from sys.databases
where name like '%.meta' or name = 'stackapps'

exec(@sql)


;with category as (
select case 
       when site = 'StackExchange.Meta' 
       then site 
       else replace(site, '.meta', '') 
       end site
     , patindex(@categoryLike, body) [start]
     , substring(body, patindex(@categoryLike, body) + len(@categoryLike), len(body)) rest
from #sitestats
where patindex(@categoryLike, body) > 0
)
,
moderator as (
select site
     , patindex(@modLike, rest) [start]
     , substring(
           rest
         , patindex(@modLike, rest) + len(@mod)
         , patindex(@modEndLike, rest) - (patindex(@modLike, rest) + len(@mod))
       ) value
     , substring(
         rest
       , patindex(@modLike, rest) 
         + len(@mod) 
         + ( patindex(@modEndLike, rest) 
         - ( patindex(@modLike, rest) + len(@mod)))
         + len(@modEndLike) - 1
       , len(rest)) rest
from category
), 
community as (
select site
     , convert(decimal, replace(moderator.value,',','')) moderator_value --  modvalue
     , patindex(@modLike, rest) [start]
     , substring(
           rest
         , patindex(@modLike, rest) + len(@mod)
         , patindex(@modEndLike, rest) - (patindex(@modLike, rest) + len(@mod))
       ) value
     , substring(
         rest
       , patindex(@modLike, rest) 
         + len(@mod) 
         + ( patindex(@modEndLike, rest) 
         - ( patindex(@modLike, rest) + len(@mod)))
         + len(@modEndLike) - 1
       , len(rest)) rest
from moderator
),
category_stats as (
select site 
    , moderator_value
    ,  convert(decimal, replace(community.value,',','')) community_value
from community
)


select *
from category_stats
order by moderator_value desc

This is what the result looks like:

StackOveflow: 10537, StackApps: 7417

Let me point out for no particular reason that Stack Apps has a mod team of 3 mods, Stack Overflow of 27 mods. Just imagine what they could have achieved if they got their priorities right.

Not relevant for this particular query but SEDE is updated once a week on Sunday.
Use the educational SEDE Tutorial written by the awesome Monica Cellio.
Say "Hi" in SEDE chat.

3
  • Considering you're a moderator of Stack Apps, would you please shed some light on why that site has the largest number of destroyed accounts among sites other than SO? Feb 26, 2023 at 22:51
  • Given the limited scope and traffic on Stack Apps, I am imagining there is a lot that the SO mods have to do which you don't need to spend time on on Stack Apps. Just sayin'.
    – tripleee
    Feb 27, 2023 at 13:19
  • 1
    @SonictheAnonymousHedgehog see meta.stackoverflow.com/questions/421453/… and on top of that the mod team has tooling to report new profile spammers on a daily basis. Those get judged and acted on and then there are reports from across the network by other mods which get handled as well. That adds up if you moderate a light site and there is no other significant flag load. Destroying profile spammers is the first thing that gets dropped when more urgent matters arise.
    – rene
    Feb 27, 2023 at 18:25

You must log in to answer this question.