Add an automated query/cleanup for potential spam accounts

[ArtOfCode-]

Current query for these:

select
    u.id,
    u.email, 
    u.username,
    concat('https://', c.host, '/users/', u.id) as profile_link,
    u.profile_markdown
from users u
inner join community_users cu on u.id = cu.user_id and cu.id = (select id from community_users where user_id = u.id order by reputation desc limit 1)
inner join communities c on cu.community_id = c.id
left join posts p on p.user_id = u.id
left join comments cm on cm.user_id = u.id
left join votes v on v.user_id = u.id
where u.profile_markdown is not null
  and u.profile like '%href="%'
  and u.created_at >= date_sub(current_timestamp, interval 12 month)
  and u.deleted = false
  and u.email not like '%localhost'
  and cu.reputation = 1
group by u.id
having count(distinct p.id) = 0
   and count(distinct cm.id) = 0
   and count(distinct v.id) = 0
order by u.id

We should add this as an automated query/cleanup job in some form.

Discussion:

• How can we avoid this catching legitimate users? Do we time-limit to only the last 24 hours if this is running daily? Hopefully any established user would have been around longer than that, although we still run the risk of catching the odd user who's customised their profile but not yet done anything.
• There is sufficient volume of these that manually reviewing each one is a heavy job. How can we lighten that? Can we automate any part of this?

[Oaphi]

How can we avoid this catching legitimate users?

Just wondering for now - would we get significantly less false positives if we only included accounts with a user website filled in? Seems to be a common pattern nowadays.

[cellio]

Checking my understanding: this targets users who haven't posted, commented, or voted, who have 1 reputation, and who have links in their profiles? In other words, they could be spam profiles or they could be legit users who created accounts, added blog/github/whatever links, and then never came back to do anything?

Since we can't tell which and we'd rather not alienate the false positives, I suggest that for the resulting accounts we revoke Participate Everywhere. Without this ability the links aren't shown to anonymous users, but an actual legit user can still log in and do stuff.

We might want to add "count(raised flags) = 0" to the checks at the end.

[ArtOfCode-]

Just wondering for now - would we get significantly less false positives if we only included accounts with a user website filled in?

@Oaphi Actual number of false positives with this seems quite low anyway - I've found less than 5 across hundreds of results. This might improve accuracy, but I think would also cut out a lot of spam profiles without websites.

Checking my understanding

@cellio Your understanding is correct.

I could make this a scheduled job that just removes PE for these users easily enough.

[cellio]

Is there more to do here, or can this be closed? (PR was merged.)

[ArtOfCode-]

I'd like to create some sort of moderator review system that would list the users de-privileged by this and provide options to re-privilege or delete.