8
\$\begingroup\$

In light of the recent "SQL frenzy" of sorts in The 2nd Monitor, I decided to take a stab at writing my own SEDE query. Essentially, what it does is find questions that could be answered based on the following parameters.

-- @MinQuestionVotes - The minimum amount of votes on a question.
-- @MaxQuestionAnswers - The maximum amount of answers to a question.
-- @QuestionTags - The tags that should be on the questions.

At the moment, I feel that those parameters are necessary for finding possible questions to answer, but if you feel that one isn't needed, just mention it. 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.

  -- @MinQuestionVotes - The minimum amount of votes on a question.
  -- @MaxQuestionAnswers - The maximum amount of answers to a question.
  -- @QuestionTags - The tags that should be on the questions.
DECLARE @MinQuestionVotes INT = ##MinQuestionVotes##;
DECLARE @MaxQuestionAnswers INT = ##MaxQuestionAnswers##;
DECLARE @QuestionTags NVARCHAR(150) = ##QuestionTag1##;


-- SELECT the final results. Data is filtered based
-- on the following conditions.

  -- ClosedDate  IS EQUAL TO              null
  -- PostTypeId  IS EQUAL TO              question
  -- Score       GREATER THAN OR EQUAL TO @MinQuestionVotes
  -- AnswerCount LESS THAN OR EQUAL TO    @MaxQuestionAnswers
  -- Tags        CONTAIN                  @QuestionTags
SELECT
    Posts.Id AS [Post Link]
  , OwnerUserId AS [User Link]
  , Posts.Score
  , Posts.Tags
  , Posts.ViewCount
  , Posts.AnswerCount
  FROM Posts 
    INNER JOIN PostTags ON Posts.Id = PostTags.PostId
    INNER JOIN Tags ON PostTags.TagId = Tags.Id
  WHERE
    Posts.PostTypeId = 1 AND
    Posts.ClosedDate IS NULL AND
    Posts.Score >= @MinQuestionVotes AND
    Posts.AnswerCount <= @MaxQuestionAnswers AND
    Tags.TagName LIKE CONCAT('%', @QuestionTags, '%');

Finally, here's an example of possible inputs. When entering into the QuestionTags field, you need to surround your tags with single quotes, like this: 'python'.

@MinQuestionVotes:   1
@MaxQuestionAnswers: 0
@QuestionTags:       'python'
\$\endgroup\$
6
\$\begingroup\$

First, your comments:

They're structured well, but, the content could be improved:

-- SELECT the final results. Data is filtered based
   ^^^^^^^^^^^^^^^^^^^^^^^^^
-- on the following conditions.

You're not really SELECTing the final results, you SELECT them based on the conditions, you don't SELECT them and then filter them.


Your declaration DECLARE @QuestionTags NVARCHAR(150) = ##QuestionTag1## is a little confusing:
Tags is plural, but then the variable you ask for input is Tag1 (singular)?


You ask for input like ##MinQuestionVotes##, but there's no reason to abbreviate, it could really just be:
##MinimumQuestionVotes##. Same things applies to the other two.


Finally, CONCAT('%', @QuestionTags, '%') is good, but you could really just:
'%' + @QuestionTags + '%' instead.

You could even build the tag string with the ' attached so the user doesn't need to input.


Other than that, your code looks really clean and nice. Good Work!

\$\endgroup\$
4
\$\begingroup\$

Due to the way you used the ##QuestionTag1## parameter, you have to enter 'java' in the input field (with the single quotes) rather than java. I'm pretty sure that that is an indicator of an SQL injection vulnerability.

But is not . I don't see why you mangle the tag name with wildcards.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.