Unanswered questions (with answers) which OP 'okayed' answer

0

Please login or register to vote for this query.

(click on this box to dismiss)

Unanswered questions with zero scored answers where OPs expressed in comments the answer was helpful (comment is beneath the supposed helpful answer). This query was adapted from https://data.stackexchange.com/gis/query/983514/unanswered-questions-with-answers-in-comments-per-tag

Stack Overflow

Q&A for professional and enthusiast programmers

WITH 
unanswered_questions AS
(
  SELECT q.Id,
         q.OwnerUserId
  FROM Posts q
  -- credits to sam.saffron in https://data.stackexchange.com/stackoverflow/query/4038/find-interesting-unanswered-questions
  WHERE (SELECT COUNT(*) FROM Posts a WHERE a.ParentId = q.Id and a.Score > 0) = 0
  AND q.CommunityOwnedDate IS NULL 
  AND q.ClosedDate IS NULL 
  AND q.ParentId is null 
  AND q.AcceptedAnswerId IS NULL
  AND NOT EXISTS 
        (
         SELECT *
         FROM PostHistory ph
         WHERE ph.PostId = q.Id
         AND ph.PostHistoryTypeId = 14 -- not locked.
         )
)
, post_candidates AS 
(
 SELECT p.Id,
        p.Score,
        p.CreationDate,
        c.Text
 FROM unanswered_questions u
 INNER JOIN Posts p ON p.ParentId = u.Id
 INNER JOIN Comments c ON c.PostId = p.Id
 WHERE u.OwnerUserId = c.UserId -- comments from OP.
       AND (LOWER(c.Text) LIKE '%that was it%'
            OR LOWER(c.Text) LIKE '%worked%'
            OR LOWER(c.Text) LIKE '%works%' -- some false positives
            OR LOWER(c.Text) LIKE '%solved%'
            OR LOWER(c.Text) LIKE '%thanks a lot%')
)
-- remove false positives (with some cost of true positives as well).
SELECT pc.Id AS [Post Link],
       pc.Score
FROM post_candidates pc
WHERE (LOWER(pc.Text) NOT LIKE '%but%'
       AND LOWER(pc.Text) NOT LIKE '%still%'
       AND LOWER(pc.Text) NOT LIKE '%however%'
       AND LOWER(pc.Text) NOT LIKE '%?%'
       AND LOWER(pc.Text) NOT LIKE '%not%'
       AND LOWER(pc.Text) NOT LIKE '%problem is%'
       AND LOWER(pc.Text) NOT LIKE '%has not worked%'
       AND LOWER(pc.Text) NOT LIKE '%hasn''t worked%')
ORDER BY pc.Score DESC,
         pc.CreationDate DESC;

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results