14
\$\begingroup\$

The Zombies are on their way to eat our statistics. Fortunately, we have a way to get rid of them: our votes. However, we need to find them first. The /unanswered section is too large and contains many strong enemies where we have to write a review first. But the StackExchange Data Explorer can sniff almost dead Zombies from a mile.

For this, we need a fitting query. This is what I've tried:

SELECT 
  q.Id AS [Post Link],
  MAX(a.Score) AS [Best answer score]
FROM 
  Posts q
JOIN 
  Posts a ON a.ParentId = q.Id
WHERE 
      q.PostTypeId = 1  
  AND q.AnswerCount > 0
  AND q.AcceptedAnswerId IS NULL
  AND a.PostTypeId = 2
GROUP BY 
  q.Id
HAVING 
 MAX(a.Score) = 0

You can run and find the query on SEDE.

It searches for questions (q.PostTypeId = 1) that have at least one answer, no accepted answer, and the maximum score of all its answers is 0. The resulting list can then be checked for fast Zombie removal.

Is there something that is utterly wrong? Or can this weapon help us in our fight against the undead?

\$\endgroup\$
  • \$\begingroup\$ @πάνταῥεῖ yes they are. I'll try to find a reference but I have seen queries using keywords only available in T-SQL. (sort-of reference: meta.stackexchange.com/q/54450/295232) \$\endgroup\$ – Glorfindel Mar 19 '18 at 18:28
  • 2
    \$\begingroup\$ @Glorfindel It's in the help: "Click the Compose Query button up there and start typing your T-SQL query" (if you're looking for it) \$\endgroup\$ – Zeta Mar 19 '18 at 18:30
  • \$\begingroup\$ @Zeta thanks. I recently learned there is a SEDE tutorial you might be interested in. Depending on your SQL knowledge, you can skip some of the pages. \$\endgroup\$ – Glorfindel Mar 19 '18 at 18:37
  • \$\begingroup\$ @Glorfindel If I hadn't read that tutorial, I wouldn't have known the as [Post Link] trick ^^. Unfortunately, the tutorial falls short on the GROUP BY and JOIN site. \$\endgroup\$ – Zeta Mar 19 '18 at 18:46
  • \$\begingroup\$ Could you please help with finding wounded C++ zombies? I tried to stick in ` AND q.tags like 'c++' ` but it didn't work. \$\endgroup\$ – Incomputable Mar 19 '18 at 19:28
13
\$\begingroup\$

Not utterly wrong, but

q.PostTypeId = 1 

and

AND a.PostTypeId = 2

are superfluous. The only post types with a non-null value in ParentId are answers (see the Database schema documentation), and their values are the ids of questions, so the join already takes care of this.

I'd slightly misunderstood the objective of the query at first, because questions with answers with negative scores, like this one also appear on /unanswered. But you want to review the answers to see if they're worthy of an upvote. Then, you might as well drop the second column (MAX(a.Score) AS [Best answer score]) because you know it's going to be zero.

\$\endgroup\$
  • \$\begingroup\$ Yeah, I should drop the [Best answer score]. My first variant was similar to /unanswered, but then I thought it would be better just to look for those fast zombie removal questions. I've opted to use q.Tags instead in a newer variant, although I'm pretty sure that I'm abusing GROUP BY. But that's for a future review. Thanks already for your remarks. \$\endgroup\$ – Zeta Mar 19 '18 at 18:49

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.