It was suggested on Meta for July 2015 Community ChallengeJuly 2015 Community Challenge to make a bot that can find questions with bad titles. I wrote this query to help others find examples to help them make write their code.
I made it a point to document as I wrote it so that someone who is a beginner with SQL could still make changes to it easily. Is there room for improvements?
DECLARE @Question INT = (SELECT Id FROM PostTypes WHERE Name = 'Question');
DECLARE @Answer INT = (SELECT Id FROM PostTypes WHERE Name = 'Answer');
SELECT
/* Uncomment below if you want to limit the results */
/* TOP 100 */
Posts.Id AS [Post Link]
/* only for sanity check in case the filter is removed from WHERE clause */
, CASE
WHEN Posts.PostTypeId = @Question THEN 'Question'
WHEN Posts.PostTypeId = @Answer THEN 'Answer'
ELSE 'Other' END
AS [Post Type]
, Posts.Score
, Posts.CreationDate
, Users.Id AS [User Link]
, Users.Reputation
FROM Posts
INNER JOIN Users
ON Posts.OwnerUserId = Users.Id
WHERE
/* Filter out non-question posts */
Posts.PostTypeId = @Question
/* Generic title */
AND (
Posts.Title LIKE '%please%' OR
Posts.Title LIKE '%review%' OR
Posts.Title LIKE '%improve%' OR
Posts.Title LIKE '%better%' OR
Posts.Title LIKE '%how to%'OR
Posts.Title LIKE '%how can i%' OR
Posts.Title LIKE '%how do i%'
)
/* Post not closed yet */
AND Posts.ClosedDate IS NULL
/* Question doesn't have positive score */
/* AND Posts.Score <= 0 */
/* User has low reputation */
AND Users.Reputation <= 500
ORDER BY
/* Lowest scores first - Change to DESC if you want Highest first */
Posts.Score ASC
/* Newest first - Change to ASC if you want oldest first */
, Posts.CreationDate DESC