...

0

Please login or register to vote for this query.

(click on this box to dismiss)

sandbox

The Great Outdoors

Q&A for people who love outdoor activities, excursions, and outdoorsmanship

print 'sandbox!'



/*

--Original Query: https://data.stackexchange.com/stackoverflow/query/874148
--get text from table
create procedure #parseProfile( @UserID int ) as
begin
  declare @txt nvarchar(max) = ( select aboutme from users where id=@UserID )
  set @txt = replace(@txt,char(39),'') --remove single quotes
  declare @pBegin int 
  declare @pEnd int = 1
  declare @uLen int = -1

  while charindex('http',@txt,@pEnd)>0 and @uLen<>0
    begin
      set @pBegin = charindex('http',@txt,@pEnd)  --URL start position
      set @pEnd = ( select min(oEnd) from (       --URL end: First occurrence of:
                       select len(@txt)                          as oEnd -- EOL
                 union select charindex(' ', @txt, @pBegin + 5)  as oEnd -- Space
                 union select charindex('"', @txt, @pBegin + 5)  as oEnd -- "
                 union select charindex('<', @txt, @pBegin + 5)  as oEnd -- <
                 union select charindex(',', @txt, @pBegin + 5)  as oEnd -- ,
                 union select charindex(char(13),@txt,@pBegin+5) as oEnd -- CR
                 union select charindex(char(10),@txt,@pBegin+5) as oEnd -- LF
                  ) a where oEnd > 0 )
      set @uLen = @pEnd - @pBegin 
      insert into #urls select @UserID as UserID, substring(@txt,@pBegin,@uLen) as URL --save url 
    end
  end
go

----------------------------------------------
print '000'
select top ##MaxRecords:int?1000##
  id,
  (len(aboutme)-len(replace(aboutme,'http','')))/4 as LinkCount
into #temp 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 


print '111'
select 
  rank() over (order by LinkCount desc) as Rank, 
  Id, 
  LinkCount,
  row_number() over (order by LinkCount desc) as Row
into #spammers from #temp 
order by LinkCount desc
print '222'
----------------------------------------------


declare @id int = 1
create table #urls (UserID int, URL nvarchar(2048)) --temp output table
declare @numRecords int=( select count(*) as cnt from #spammers )

print '333'
declare @rw int = 0
while @rw < @numRecords
begin
  set @rw = @rw + 1
  set @id = ( select id from #spammers where Row=@rw )
  print cast(@rw as varchar) +' postid='+cast( @id as varchar)

  execute #parseProfile @id --run this on each profile
  --go
end;

----------------------------------------------
----------------------------------------------
select URL, count(*) as [CountURLs], count(distinct UserID) as [UserCount]
from #urls group by URL order by count(*) desc

select * from #urls
print 'done'

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results