Possible underaged users

1

Please login or register to vote for this query.

(click on this box to dismiss)

Query to find new and active accounts that are underage

Stack Apps

Q&A for apps, scripts, and development with the Stack Exchange API

-- MinReputation: Users with at least this amount of rep
-- ActiveWithinPastDays: Users last seen within the past X number of days

(
  SELECT 
    u.Id as [User Link],
    u.CreationDate,
    u.AboutMe,
    SUBSTRING(u.AboutMe, PATINDEX('%m 1[0-5] year%',LOWER(u.AboutMe))+2, 2) AS Age,
    ( 'site://admin/cm-message/create/' + CONVERT(nvarchar(20),u.Id) + '?action=underage-user&info=Underaged+user.+Profile+mentions+underage+age;+Created+within+the+past+year+and+is+recently+active' ) AS [Contact CM]
  FROM Users AS u
  WHERE
        LOWER(u.AboutMe) LIKE '%m 1[0-5] year%'
    AND u.Reputation >= ##MinReputation:int?1##
    AND u.LastAccessDate > DATEADD(DAY, -##ActiveWithinPastDays:int?90##, GETDATE())
    AND u.CreationDate >= DATEADD(dd, -365, GETDATE())
  
  UNION
  
  SELECT 
    u.Id as [User Link],
    u.CreationDate,
    u.AboutMe,
    SUBSTRING(u.AboutMe, PATINDEX('%m a 1[0-5] year%',LOWER(u.AboutMe))+4, 2) AS Age,
    ( 'site://admin/cm-message/create/' + CONVERT(nvarchar(20),u.Id) + '?action=underage-user&info=Underaged+user.+Profile+mentions+underage+age;+Created+within+the+past+year+and+is+recently+active' ) AS [Contact CM]
  FROM Users AS u
  WHERE
        LOWER(u.AboutMe) LIKE '%m a 1[0-5] year%'
    AND u.Reputation >= ##MinReputation:int?1##
    AND u.LastAccessDate > DATEADD(DAY, -##ActiveWithinPastDays:int?90##, GETDATE())
    AND u.CreationDate >= DATEADD(dd, -365, GETDATE())
)

ORDER BY u.Id DESC

Enter Parameters

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