The Think! hat (2017 Winter Bash) is for 6 posts to be edited after leaving a comment. I figured I'd look up to see what posts these were, see if any of my comments were eligible for deletion (as no longer being relevant) to try and get the This is Fine hat.

I'm no good with SEDE (as soon as a database query hits a JOIN I'm lost) and I tried editing a SEDE query that was very similar (one for the Silencium hat).

I got this far:

SELECT a.Id AS [Post Link]
  FROM Posts AS q
  INNER JOIN Posts AS a ON a.ParentId = q.Id
  LEFT OUTER JOIN Comments AS c ON c.PostId = a.Id
                               AND c.CreationDate < a.LastEditDate
  WHERE a.Score >= 1
    AND a.CommentCount > 0
    AND a.CreationDate >= '2017-12-13'
    AND c.UserId = 1663383

I know the following:

  • this will only find answers, not questions (or I can change it to find questions and not answers)
  • hard coded to my SO user ID (I don't know how to either a. take an input value or b. use the active user's id).
  • Only finds 3 of the comment -> edit posts I have on Game Dev (where I have gotten the Think! hat, which is 6 such edits).
  • Setting the date to Jan 1 results in several duplicate results (e.g. two or more comments on the same Q/A). This is probably an acceptable flaw.
  • Just for starters get rid of the Inner join on posts and rename the alias a to q. That should give you all posts, the autopopulate for userid's is broken long ago. Use ##userid##
    – rene
    Dec 27, 2017 at 8:31
  • SEDE doesn't show deleted comments, which will probably count towards the Think! hat. Can you find those comments 'by hand'? Also, the posts need to get edited by their owner, which will require an extra join to the PostHistory table.
    – Glorfindel Mod
    Dec 27, 2017 at 8:31
  • @Glorfindel given that I haven't deleted any comments since the start of the winter bash, I'm not worried about those. (Well, ok, I deleted some just now, but from my existing query!) Dec 27, 2017 at 8:32
  • Other people might have flagged your comments as 'no longer needed'.
    – Glorfindel Mod
    Dec 27, 2017 at 8:33
  • @Glorfindel That's...a fair point! That said, if I'm looking for comments to delete, then I don't think deleted-by-others are going to be valuable anyway ;) Dec 27, 2017 at 8:34
  • @rene That...had interesting results. The first post that comes up (running this against SO) was an answer on a question I've never interacted with and it listed over 100 copies of it in the results. Every other result seems to be listed dozens of times as well. Dec 27, 2017 at 8:42

1 Answer 1


I think this query does the job; I've tried to explain in the comments what's going on. It actually finds 4 qualifying comments on Game Dev for your account:

enter image description here

  FROM Posts AS p
  INNER JOIN Comments AS c ON c.PostId = p.Id
  INNER JOIN PostHistory AS h ON h.PostId = p.Id
    AND h.CreationDate > c.CreationDate -- edit AFTER comment
    AND h.UserId = p.OwnerUserId -- edit by post owner
    AND h.PostHistoryTypeId IN (4, 5) -- type: Edit Title, Edit Body
  WHERE c.CreationDate >= '2017-12-13' -- start of Winter Bash
    AND c.UserId = ##userid:int##
    AND p.OwnerUserId != ##userid:int## -- own posts don't count
  • Ah, brilliant! That indeed turned up a post I'd failed to find with my own bashing at SEDE. The other two (for the hat) are likely from comments that were deleted (either by me in the last half hour, or flagged as "no longer needed") Dec 27, 2017 at 8:46
  • You're welcome. Also note that SEDE is updated once a week, on Sunday morning, so it fails to see comments you posted in the last three days.
    – Glorfindel Mod
    Dec 27, 2017 at 8:47
  • That too. ;) I have heard the Sunday thing before, but don't always remember. Dec 27, 2017 at 8:48
  • And...hat'd. Which also got me the It’s-a me! hat (who knew deleting your own comments could be so rewarding?) Dec 27, 2017 at 9:00

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .