0

Please login or register to vote for this query.

(click on this box to dismiss)

without valid accounts on other sites

Q&A about the site for anime and manga 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

fork query

edited nov 8 anonymous

created dec 31 22 BMitch