SPAMMIES: 1000 User Profiles that are OBVIOUSLY SPAM

2

Please login or register to vote for this query.

(click on this box to dismiss)

A simple queried list of obvious spammers: List of user profiles with lengthy descriptions, inactive since shortly after creation (which was not recently), with very-low-rep (usually 1), Sorted by "NUMBER OF HTTP LINKS FOUND IN BIO". Disclaimer: I haven't put a ton of time into this. Like anything it can only be improved, and it isn't exactly "machine learning"... I haven't looked through all these links manually but a random sampling make me very confident these could all be safely deleted, nobody would miss them (likely not even the OP would notice)

Super User

Q&A for computer enthusiasts and power users

--List of extracted URLs: https://data.stackexchange.com/stackoverflow/query/874148

select top ##MaxRecords:int?1000##
  id as [User Link],
  datediff(MM,creationdate,getdate()) as [Profile Age month],
  datediff(MM,lastaccessdate,getdate()) as[Last Access month],
  Reputation,
  (len(aboutme)-len(replace(aboutme,'http','')))/4 as [#Links in Bio],
  len(aboutme) as ['AboutMe' Length],
  --DisplayName,'http://stackoverflow.com/u/'+cast(id as nvarchar) as UserLink, --uncomment this line if exporting to CSV
  views as [#ProfileViews]
into #spammers from users
where 
      reputation <= ##MaxRep:int?10##                                              --under 20 reputation
  and len(aboutme)>= ##MinLength:int?250##                                         --length of Bio over 500 characters
  and (len(aboutme)-len(replace(aboutme,'http','')))/4 > ##LinksInProfile:int?5##  --count of links
  and ( ##NoActivitySinceCreation:int?1## <>1                                      --1=True, 2=False (True = last access within 1 month of creation
        or datediff(MM,creationdate,getdate())<=1+datediff(MM,lastaccessdate,getdate()) ) --inactive since month profile was created
  and lastaccessdate<dateadd(MM,0-##LastAccessMonths:int?4##,getdate())            --inactive >=4 months 

select 
        rank() over (order by [#Links in Bio] desc) as Rank, *,
  row_number() over (order by [#Links in Bio] desc) as Row
from #spammers 
order by [#Links in Bio] desc, ['AboutMe' Length] desc

-- MaxRecords:Maximum number of records to display?.................................
-- MaxRep:Maximum reputation of user?.................................................
-- MinLength:Minimum Length of 'About Me' section?..................................
-- LinksInProfile:Mininum # of HTTP links in profile?.........................................
-- NoActivitySinceCreation: Only users with no activity since profile creation? (1=Ya, 2=No)
-- LastAccessMonths:Minimum # of months since last activity?................................

Enter Parameters

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