without valid accounts on other sites
Q&A about the site for music historians, critics, and fans
select distinct TOP(10) accountid into #users from users inner join sede_users su on su.account_id = users.accountid where su.reputation =1 and su.question_count = 0 and su.answer_count = 0 and users.reputation = 1 and creationdate >= '##year##-01-01' AND datediff(hour, Creationdate, Lastaccessdate) < 504 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) -- false positives and users.accountid not in (7585603) SELECT U.Id AS [User Link] , U.Displayname AS 'Display' , U.Location AS 'Location' , U.WebsiteUrl AS 'URL' , U.Creationdate AS 'Created' , U.Lastaccessdate AS 'Accessed' , datediff(hour, U.Creationdate, U.Lastaccessdate) AS 'Duration' , U.Aboutme AS 'About Me' FROM users U inner join #users au on au.accountid = u.accountid WHERE U.Aboutme LIKE '%[^i]phone:%' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '%number:%' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '%hotline:%' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '%[^0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]%' OR U.Aboutme LIKE '%[^0-9][0-9][0-9] [0-9][0-9] [0-9][0-9]%' OR U.Aboutme LIKE '%keyword%' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '%searches:%' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '%tags:%' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '%category:%' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '%description:%' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '%address:%' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '%location:%' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '%email:%' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '%web:%' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '%website:%' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '% our website %' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '%hours:%' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '%HOUR%' OR U.Aboutme LIKE '%24[x-]7%' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '%[> ]escort%' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '%[> ]sexy%' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '%call girl' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '%[> ]massag%' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '% masaje%' OR U.Aboutme LIKE '%[> ]e[xr]otic%' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '%[> ]orgasm%' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '%sex toy%' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '%sextoy%' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '%sensual%' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '%[> ]prostitut%' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '%companion%' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '%[> ]drug%' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '%[> ]dispensar%' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '%[> ]pharmacy%' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '%[> ]pill%' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '%[> ]penis%' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '%[> ]cannabis%' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '%silkroad%' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '%cocaine%' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '%heroine%' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '%[> ]edible%' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '%[> ]thuốc%' OR U.Aboutme LIKE '%[> ]slot[s ]%' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '%[> ]poker%' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '%jackpot%' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '%[> ]lottery%' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '%[> ]lotto%' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '%[> ]prize%' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '%[> ]gambl%' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '%[> ]taruhan%' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '%[> ]judi%' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '%[> ]pejudi%' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '%[> ]togel%' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '%[> ]taruhan%' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '%permainan%' OR U.Aboutme LIKE '%memainkan%' OR U.Aboutme LIKE '%memenangkan%' OR U.Aboutme LIKE '%penjualan%' OR U.Aboutme LIKE '%[> ]obat %' OR U.Aboutme LIKE '%casino%' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '%[Kk]emenangan%' OR U.Aboutme LIKE '%airline%' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '%[Gg]ratis%' OR U.Aboutme LIKE '%[> ][Hh]arga%' OR U.Aboutme LIKE '%[> ][Nn]ikah%' OR U.Aboutme LIKE '%[> ]we %' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '%[> ]our %' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '%[> ]us %' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '%[> ]they %' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '%[> ]their %' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '%[> ][Kk]ami %' OR U.Aboutme LIKE '%[> ][Kk]alian%' OR U.Aboutme LIKE '%[> ][Bb]ạn%' OR U.Aboutme LIKE '%[> ][Nn]osotros%' OR U.Aboutme LIKE '%[> ][Tt]hao%' OR U.Aboutme LIKE '%[> ][Cc]húng [Tt]ôi%' OR U.Aboutme LIKE '%[> ][Cc]húng [Tt]a%' OR U.Aboutme LIKE '%<a href="%' COLLATE Latin1_General_100_CI_AI OR U.Location LIKE '[0-9]%,%[0-9][0-9][0-9][0-9][0-9]%' OR U.WebsiteUrl LIKE '%[0-9]%.[0-9]%.[0-9]%.[0-9]%' OR U.WebsiteUrl LIKE '%business.site%' COLLATE Latin1_General_100_CI_AI OR U.WebsiteUrl LIKE '%goo.go/maps/%' COLLATE Latin1_General_100_CI_AI OR U.WebsiteUrl LIKE '%yelp.com%' COLLATE Latin1_General_100_CI_AI OR U.Displayname LIKE '%spam%' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '%[> ]spam %' COLLATE Latin1_General_100_CI_AI OR U.Aboutme LIKE '%#spam%' COLLATE Latin1_General_100_CI_AI OR U.WebsiteUrl LIKE '%#spam%' COLLATE Latin1_General_100_CI_AI ORDER BY Creationdate desc