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'

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!