Finding questions to answer - Part 2

Question

I've refactored my previous question finder, and added multi-tag support, and support for searching for keywords in the question titles. Essentially, what it does is find questions that could be answered based on the following parameters.

-- @MinimumQuestionVotes - The minimum amount of votes on a question.
-- @MaximumQuestionAnswers - The maximum amount of answers to a question.
-- @QuestionTags - The question's tags. Blank string is a wildcard.
-- @TitleContains - Words in the title. Blank string is a wildcard.

If you feel that any parameters aren't needed, or can be improved, just mention it. I'm open to any suggestions. Anyways, here's the code, and here's the SEDE query link.

-- User parameters for finding questions. Here is
-- a brief description of what each parameter does.
 -- @MinimumQuestionVotes - The minimum amount of votes on a question.
 -- @MaximumQuestionAnswers - The maximum amount of answers to a question.
 -- @QuestionTags - The question's tags. Blank string is a wildcard.
 -- @TitleContains - Words in the title. Blank string is a wildcard.
DECLARE @MinimumQuestionVotes INT = ##MinimumQuestionVotes:int?0##;
DECLARE @MaximumQuestionAnswers INT = ##MaximumQuestionAnswers:int?0##;
DECLARE @QuestionTags NVARCHAR(150) = ##QuestionTags##;
DECLARE @TitleContains NVARCHAR(250) = ##TitleContains##;
-- Final results are SELECTed based on the following
-- conditions.
 -- ClosedDate IS EQUAL TO null
 -- PostTypeId IS EQUAL TO question
 -- Score GREATER THAN OR EQUAL TO @MinimimQuestionVotes
 -- AnswerCount LESS THAN OR EQUAL TO @MaximumQuestionAnswers
 -- Tags CONTAIN @QuestionTags
SELECT
 [Posts].Id AS [Post Link]
 , [Posts].OwnerUserId AS [User Link]
 , [Posts].Score
 , [Posts].Tags
 , [Posts].ViewCount
 , [Posts].AnswerCount
 FROM Posts WHERE
 [Posts].ClosedDate IS NULL
 AND [Posts].PostTypeId = 1
 AND [Posts].Score >= @MinimumQuestionVotes
 AND [Posts].AnswerCount <= @MaximumQuestionAnswers
 AND [Posts].Tags LIKE '%' + REPLACE(@QuestionTags, ' ', '%%') + '%'
 AND [Posts].Title LIKE '%' + REPLACE(@TitleContains, ' ', '%%') + '%'
 ORDER BY Score DESC;

Finally, here's some example input for the parameters. Do note, in the @QuestionTags and @TitleContains fields, you need to put single quotes, '', and space-separate each tag/word.

@MinimumQuestionVotes: 1
@MaximumQuestionAnswers: 0
@QuestionTags: 'python beginner'
@TitleContains: 'web'

• Malachi: your query doesn't work with empty @QuestionTags or @TitleContains unless you put single quotes in.
• Ethan Bierlein: @Lyle'sMug That's one of the quirks of the SEDE. You have to put in raw variable values.

Answer 1

There are some usability issues with @QuestionTags:

• The ordering of tags matters. I think in the typical use case that's not the desired behavior
• The input format is hackish (the implementation too) and unintuitive

I have an alternative suggestion that has different downsides:

• Use multiple tag input fields, for example @Tag1, @Tag2, and so on
• The advantage is that you could enter tags in any order, and without funky input rules
• The disadvantage is that the number of tags you can enter will be limited to the number of fields you provide
• Note that this approach will also open the possibility to choose the condition for for matching tags: they can be AND-ed or OR-ed. Your choice, or give another input to users to let them decide

The numbered fields are not exactly pretty, but I think the query will be easier to use this way. (Can be subjective.)

You could do similarly for matching keywords in the title.

Note that I dropped "Question" from the parameter names, to simplify to @TagN. On the network only questions can have tags, so this is obvious.

Other notes:

• The semicolons at the end of variable declarations are unnecessary
• It would be nice to make title optional
• The repeated [Posts]. prefix of fields are not necessary, and a bit of a noise

Example implementation with some of the improvements explained above:

DECLARE @MinimumQuestionVotes INT = ##MinimumQuestionVotes:int?0##
DECLARE @MaximumQuestionAnswers INT = ##MaximumQuestionAnswers:int?0##
DECLARE @Tag1 NVARCHAR(50) = RTRIM(LTRIM(##Tag1:string? ##))
DECLARE @Tag2 NVARCHAR(50) = RTRIM(LTRIM(##Tag2:string? ##))
DECLARE @Tag3 NVARCHAR(50) = RTRIM(LTRIM(##Tag3:string? ##))
DECLARE @TitleContains NVARCHAR(250) = RTRIM(LTRIM(##TitleContains:string? ##))
SELECT
 Id AS [Post Link]
 , OwnerUserId AS [User Link]
 , Score
 , Tags
 , ViewCount
 , AnswerCount
 FROM Posts WHERE
 ClosedDate IS NULL
 AND PostTypeId = 1
 AND Score >= @MinimumQuestionVotes
 AND AnswerCount <= @MaximumQuestionAnswers
 AND (@Tag1 = '' OR Tags LIKE '%' + @Tag1 + '%')
 AND (@Tag2 = '' OR Tags LIKE '%' + @Tag2 + '%')
 AND (@Tag3 = '' OR Tags LIKE '%' + @Tag3 + '%')
 AND (@TitleContains = '' OR Title LIKE '%' + REPLACE(@TitleContains, ' ', '%%') + '%')
 ORDER BY Score DESC;

Answer 2

Your code's looking really good!

I can only really see a few things that could be improved.

---

SELECT 
 [Posts].Id AS [Post Link]
, [Posts].OwnerUserId AS [User Link]

into:

SELECT
 [Posts].Id [Post Link]
, [Posts].OwnerUserId [User Link]

AS is optional, see this answer for some more elaboration on why it's unnecessary and cluttery.

---

ORDER BY Score DESC;

I would add another ORDER to that, and make it ORDER BY Score DESC, ViewCount DESC;

---

When working from/with a single table (Posts), you can either prefix [Posts]. to the beginning of your column requests, or just omit it entirely.

However, in the ORDER, you change the continous use of this and opt for vanilla Score, rather than [Posts].Score.

It's best if you choose one method (I'd suggest not attaching [Posts].), and stick with it.

---

Other than that, your code looks really neat and nice. Good work!