0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for Drupal developers and administrators

SELECT TOP ##NumberOfPosts:int?50## Posts.Score, Posts.Id AS [Post Link], Posts.CreationDate, Posts.LastActivityDate, Users.Reputation AS [Author's current reputation]
FROM   Posts INNER JOIN Users on Posts.OwnerUserId = Users.Id
WHERE  Posts.CreationDate >= DATEADD(DAY, -30, GETDATE())
       AND Users.Reputation < 2000    --Search for posts by users with relatively low reputation, high reputation users usually know what they're doing
       AND NOT Posts.Body LIKE '%<img src%'    --If the link is already an image we don't have to do anything
       AND NOT Posts.Body LIKE '%<a %<a %<a %<a %<a %'    --If the OP has enough reputation to post lots of links, he has enough reputation to post images
       AND (
           Posts.Body LIKE '%<a href=%imgur%.png%>%</a>%'
           OR Posts.Body LIKE '%<a href=%imgur%.gif%>%</a>%'
           OR Posts.Body LIKE '%<a href=%imgur%.jpg%>%</a>%'
           OR Posts.Body LIKE '%<a href=%imgur%.jpe%>%</a>%'
           OR Posts.Body LIKE '%<a href=%imgur%.bmp%>%</a>%'
       )
ORDER BY Score DESC

view current version

edited may 27

created may 27 JonathanDavidArndt34589