18
\$\begingroup\$

To preface this post a little bit, I'll explain a little bit of the "backstory" here. The Stack Exchange site, Gaming.SE, also known as Arqade, often has a large influx of bad questions about the popular video game, Minecraft. Many of these questions are either easy to find on a search engine, horribly unclear, have been asked before, are treating Arqade like a forum, or are asking for things like account support, which only the company that produces the game can provide. A few examples might include:

  1. what is the best biome to spawn in minecraft
  2. Can't Log In To Minecraft Account After 1.9
  3. No .Minecraft folder at school
  4. What I need to make TNT?
  5. How to Spawn Herobrine without Mods?

The query essentially searches in a manner like this:

  • It looks for questions with tags that are like %minecraft% or %minecraft-%%.
  • It checks to see if it has a score below -1.
  • It checks to make sure that the post hasn't already been deleted.
  • It checks to see if it's either been closed, or not closed.

I'd like to know the following things:

  • Is there a way to make this shorter and less repetitive?
  • Are there any redundant checks that aren't needed?
  • Are there any checks that could cause problems?
  • Anything else?

Here's the code, and the query link:

SELECT
    Id AS [Post Link]
  , OwnerUserId AS [User Link]
  , Score
FROM Posts WHERE
  DeletionDate IS NULL
  AND Score <= -1
  AND (
    Tags LIKE '%minecraft-%%'
    OR Tags LIKE '%minecraft%'
  )
  AND (
    ClosedDate IS NULL 
    OR ClosedDate IS NOT NULL
  );


SELECT
  COUNT(*)
FROM Posts WHERE
  DeletionDate IS NULL
  AND Score <= -1
  AND (
    Tags LIKE '%minecraft-%%'
    OR Tags LIKE '%minecraft%'
  )
  AND (
    ClosedDate IS NULL 
    OR ClosedDate IS NOT NULL
  );
\$\endgroup\$
  • \$\begingroup\$ Aside: [[minecraft] score:..-1 is:question](gaming.stackexchange.com/…) in search does much the same thing. You might also want to display the size (as a small size often correlates with a poor question). \$\endgroup\$ – user22048 Oct 13 '15 at 0:58
14
\$\begingroup\$

The use of the Tags field on the Post is a poor choice for the query. It relies on a table scan, which is slow, because it has to check each question. Note, you can use the < and > characters to identify tag start and end values in the Tags column, but, as I say, don't use that column.

Instead, you should do a join with the PostTags and Tags tables. Using a CTE to simplify that query makes sense.

I really dislike the all-caps style of SQL, but realize that there is no standard. If you can change standards, please use lower-case for keywords... it makes the column and table names much easier to see (and those are the important parts). Still, you get +1 for being consistent in your capitalization. Consistency is really important, and you nailed it.

Note that the Posts table has no deleted posts, none. There's no need to check the DeletedDate at all.

So, joining Tags to PostTags gets you minecraft-tagged posts. Joining that to Posts gets you the ones you can test scores and closures on.

Here are unclosed, low-scoring, minecraft-related posts....:

WITH mctags AS (
  SELECT Id AS tid
  FROM Tags
  WHERE TagName LIKE 'minecraft%'
), mcqs AS (
  SELECT DISTINCT PostId
  FROM PostTags INNER JOIN mctags ON TagId = tid
)
SELECT
    Id AS [Post Link],
    OwnerUserId AS [User Link],
    Tags,
    Score
FROM Posts
INNER JOIN mcqs ON Id = PostId
WHERE Score <= -1
  AND ClosedDate IS NULL
  AND PostTypeId = 1

Note, I have tried to continue using your all-caps keywords.... despite myself ;-)

Hmmm, some aditional notes:

  • your text says you check for questions "below -1", but your code checks for questions "below 0" ... there is a difference between < and <=.
  • it does not check the closed-status of the post. If you want to see if a currently-open post was closed in the past, you need to check the PostHistory table.
\$\endgroup\$
10
\$\begingroup\$

This doesn't do anything at all.

ClosedDate IS NULL 
OR ClosedDate IS NOT NULL

Saying "where is or not" results in the same thing as not including the statement at all. Either you're not getting the right results, or this could be removed.

\$\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.