11
\$\begingroup\$

Sometimes, it's not enough to know what the easy prey is. You also need to know whether you will be able to hunt it down. What good is an almost turned zombie if you cannot read the functional incantations of the language? How will you as a guru handle a behemoth?

That's why I present to you the tag-based zombie sniffer:

SELECT 
  q.Id AS [Post Link],
  q.Tags
FROM 
  Posts q
    INNER JOIN Posts a   ON q.Id           = a.ParentId
    INNER JOIN PostTags  ON q.Id           = PostTags.PostId
    INNER JOIN Tags      ON PostTags.TagId = Tags.id
WHERE 
      q.AnswerCount > 0
  AND q.AcceptedAnswerId IS NULL
  AND Tags.TagName   = '##TagName##'
GROUP BY 
  q.Id, q.Tags
HAVING 
      MAX(a.Score) >= ##MinScore:int?0##
  AND MAX(a.Score) <= ##MaxScore:int?0##

-- TagName: Tag name "as you would use in your post"
-- MinScore: Highest answer has at least votes:
-- MaxScore: Highest answer has at most votes:

You can find it on SEDE.

As it's predecessor, it looks for easy prey by default:

  • questions that have at least one answer
  • no answer has a score of more than 0
  • the highest scored answer has a score of at least 0
  • has no accepted answer

However, this one also takes a tag name so that you can search for C++ zombies or similar. This is the first time I've ever written an SQL query with three JOINs, so I have no idea whether this is considered good style. Also, I group by the q.Tags to show them in the result, but I think that's a hack and not really good style.

\$\endgroup\$
5
\$\begingroup\$

When using "simple" Aliases for table names in a query you should alias all the tables, not just some of them. This makes the query appear more consistent. In your query, you have a and q aliases for the two facets of the Posts table (questions and answers, it makes sense), but you should also alias the PostTags and the Tags tables to keep things consistent.

Stylistically, though, your query is otherwise neat, and reads quite well.

When querying tables, you should always offer the optimizer as much information as possible. You're cheating in your queries by ignoring the PostTypeID column (1 for questions, 2 for answers). Don't rely on inferred values like null, or non-null ParentID values to fix a query.

Further, note that you can put constant conditionals in the JOIN syntax for a table. You don't need to force these constants in to the regular where-block. I prefer putting related conditions together even if they are not strictly part of the join between tables. This is a coder-preference thing... you may disagree.

The most significant issue I have with the query is the use of the having-clause, which in my mind has always been a tool-of-last-resort in a SQL query.

Common Table Expressions (CTEs) have made this sort of complexity much simpler by allowing the aggregation in a virtual table before the actual query runs. CTE's are easier to show, than describe, so consider this query with a CTE instead:

WITH BestAnswer as (
  select ParentId, Max(Score) as Score
  from Posts
  where PostTypeId = 2
  group by ParentId
)

SELECT 
  q.Id AS [Post Link],
  q.Tags
FROM 
  Posts q
    INNER JOIN PostTags pt
    ON    q.Id = pt.PostId
    INNER JOIN Tags t
    ON    pt.TagId = t.id
      AND t.TagName = '##TagName##'
    INNER JOIN BestAnswer a
    ON    q.Id = a.ParentId
      AND a.Score >= ##MinScore:int?0##
      AND a.Score <= ##MaxScore:int?0##
WHERE 
      q.PostTypeId = 1
  AND q.AnswerCount > 0
  AND q.AcceptedAnswerId IS NULL

-- TagName: Tag name as you would use in your post
-- MinScore: Highest answer has at least votes:
-- MaxScore: Highest answer has at most votes:

See it in SEDE here.

\$\endgroup\$
  • \$\begingroup\$ Thanks for the CTEs, I didn't knew them yet. Maybe you're also interested in reviewing the unspecific wounded Zombie finder? \$\endgroup\$ – Zeta Mar 20 '18 at 15:27

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.