keywords in aboutme

2

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

print '##cachebreaker##'

select word
into   #triggers
from( select 'Address' word
union select 'Business Address'
union select 'BUSINESS CATEGORY'
union select 'Business Contact Email ID'
union select 'Business Description'
union select 'Business Email'
union select 'BUSINESS HOURS'
union select 'BUSINESS KEYWORDS'
union select 'Business name'
union select 'BUSINESS PHONE'
union select 'Contact us'
union select 'Description'
union select 'Email'
union select 'Employee'
union select 'Facebook'
union select 'Google'
union select 'Instagram'
union select 'Keywords'
union select 'LinkedIn'
union select 'Linked-in'
union select 'Opening Hours'
union select 'Payment'
union select 'Phone'
union select 'Pinterest'
union select 'Social links'
union select 'Social Media Links'
union select 'Twitter'
union select 'Website'
union select 'Year Est'
union select 'Year Found'
union select 'Youtube'
) data


create table #validaccounts(accountid int primary key)

select accountid 
into #users
from users
where reputation = 1
and not exists(select 1 from posts where owneruserid = users.id)
and not exists(select 1 from badges where userid = users.id and name <> 'Autobiographer')
and not exists(select 1 from votes where userid = users.id)
and users.accountid not in (7585603)
and users.id not in (107617)


declare @sql nvarchar(max)
select @sql = concat(N'insert into #validaccounts 
select distinct accountid 
from (',
STRING_AGG(concat(N'
select #users.accountid 
from [', convert(nvarchar(max),name), N'].dbo.users uo
inner join #users on #users.accountid = uo.accountid
where uo.reputation > 1
or uo.upvotes > 0
or uo.downvotes > 0 
' ) ,N' union all ')
,N' ) data')
from sys.databases
where database_id > 5
and ((name not like '%.Meta' 
  and name not like '%_Temp'  
  and name not like 'DBA' ) or name = 'StackExchange.Meta')

exec(@sql)

delete from #users
where accountid in (select accountid from #validaccounts)

select id as [User Link]
     , count(*) [# trigger words] 
     , datediff(d, min(creationdate), getdate()) [member for days]
     , datediff(d, min(creationdate), min(lastaccessdate)) [#days active]
     , min(views) [views]
     , concat('site://users/##urlpart##/', id,'|dashboard') [Dashboard]
from users
inner join #triggers t 
   on (aboutme like concat('%<p>', t.word, '%') collate SQL_Latin1_General_CP1_CI_AS
   or aboutme like concat('%<br>', t.word, '%') collate SQL_Latin1_General_CP1_CI_AS
   or aboutme like concat('%<br> ', t.word, '%') collate SQL_Latin1_General_CP1_CI_AS
   or aboutme like concat('%<p><strong>', t.word, '%') collate SQL_Latin1_General_CP1_CI_AS
   or aboutme like concat('%<br><strong>', t.word, '%') collate SQL_Latin1_General_CP1_CI_AS
   )
inner join #users va on va.accountid = users.accountid
group by id
having count(*) > 1 
order by count(*) desc

Enter Parameters

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