Unanswered questions with answers in comments (per tag)

1

Please login or register to vote for this query.

(click on this box to dismiss)

Returns a list of questions with no answers (not closed and not locked), in which OP uses 'keywords' in comments like 'solved', 'worked' and 'ended up'. Some false positives are discarded as well. If query runs out of memory, go back to revision 1235856 (from March 5th, 2019) and it should work as a simplified version (query with less keywords, not case insensitive, without removal of false positives).

GIS

Q&A for cartographers, geographers and GIS professionals

-- major revision in 5th, March, 2019 (now remove some false positives) 
-- (revision 1236141)

DECLARE @tag nvarchar(35) = ##tag:string##;

WITH post_candidates AS (
 SELECT p.Id,
        c.Text,
        t.TagName,
        p.CreationDate
 FROM Posts p
 INNER JOIN Comments c ON c.PostId = p.Id
 INNER JOIN PostTags pt ON pt.PostId = p.Id
 INNER JOIN Tags t ON t.Id = pt.TagId
 WHERE p.PostTypeId = 1 -- questions
       AND p.AnswerCount = 0 -- unanswered questions.
       AND p.ClosedDate IS NULL -- not closed.
       AND NOT EXISTS 
        (
         SELECT *
         FROM PostHistory ph
         WHERE ph.PostId = p.Id
         AND ph.PostHistoryTypeId = 14 -- not locked.
        ) 
       AND p.OwnerUserId = c.UserId -- comments from OP.
       AND (LOWER(c.Text) LIKE '%solved%' -- extreme true positive signal (solution)
            OR LOWER(c.Text) LIKE '%worked%' -- extreme true positive signal (solution)
            OR LOWER(c.Text) LIKE '%that was it%' -- so far 100% true positives (1/1)
            OR LOWER(c.Text) LIKE '%ended up%' -- high true positive signal (work around)
            --OR LOWER(c.Text) LIKE '%thank you%' --low true positive signal (use it only after reviwing other Qs)
            OR LOWER(c.Text) LIKE '%thanks a lot%') -- medium true positive signal (solution)
)
-- remove false positives.
SELECT pc.Id AS [Post Link]
FROM post_candidates pc
WHERE pc.TagName IN (@tag) -- questions with specific tags.
      -- tag:Tag name:
      AND (pc.Text NOT LIKE '%neither worked%'
           AND pc.Text NOT LIKE '%none of which worked%'
           AND pc.Text NOT LIKE '%nothing has worked%'
           AND pc.Text NOT LIKE '%unresolved%'
           AND pc.Text NOT LIKE '%did not worked%' --grammar
           AND pc.Text NOT LIKE '%didn''t worked%' --grammar
           AND pc.Text NOT LIKE '%haven''t worked%' --grammar
           AND pc.Text NOT LIKE '%dissolved%') -- this is specific to GIS context.
ORDER BY pc.CreationDate DESC; -- recent questions first (greater chances the answer in comments is not obsolete)

-- hypothesis for true positives:

-- collect false positives patterns (for improving query in future):
-- 'work anymore' (context:  '... it worked. After ... it does not seem to work anymore')
-- 'will not be solved' (context: I'm beginning to believe that this will not be solved with python alone but with..')
-- 'but'

-- Linked posts:
-- - GIS: https://gis.meta.stackexchange.com/questions/5030/finding-questions-which-are-answered-in-comments/5031#5031
-- - CV: https://stats.meta.stackexchange.com/questions/2819/converting-comment-for-simple-question-to-answer-good-practice/2820#2820

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results