Basically just a reminder that there is also a room on the other chatserver:

@Glorfindel here was my attempt: data.stackexchange.com/scifi/query/1582281/…

something like that would work to, but if I understand correctly, the only date comparison you need is a.CreationDate > fp.CreationDate?

and I have no idea what happens when you subtract dates ... but that's my lack of knowledge

@Glorfindel they're stored as numbers AFAIK

I'm trying to find where you miss out on the enlightened badge by some small number of minutes

@Glorfindel oh no clue

any idea about this:

> Invalid column name 'AnswerScore'. Invalid column name 'AnswerUserId'. Invalid column name 'Score'. Invalid column name 'CreationDate'. Invalid column name 'OwnerUserId'.

screenshot?

I need the full code to see what's wrong

DECLARE @UserId int = ##UserId##

SELECT a.Id AS [Post Link], a.Score as AnswerScore, a.CreationDate as AnswerDate, a.OwnerUserId as AnswerUserId
  FROM Posts AS q
--
  CROSS APPLY (
    SELECT Id
      FROM (
        SELECT Id, RANK() OVER(ORDER BY Id) AS [Rank]
          FROM Posts
          WHERE ParentId = q.Id
      ) AS Answers
      WHERE [Rank] = 2
  ) a
--
WHERE AnswerScore > 10 AND AnswerUserId = @UserId

I think you can't use aliases inside the WHERE clause

ah ok

and you can only use a.Score if you select it inside the CROSS APPLY

RIGHT!

that makes sense

now not working - either I get the where clause applying to the question or I get no results (could be working I guess?)

DECLARE @UserId int = ##UserId##

SELECT a.Id AS [Post Link], a.Score as AnswerScore, a.CreationDate as AnswerDate
  FROM Posts AS q
--
  CROSS APPLY (
    SELECT Id, Score, CreationDate, OwnerUserId
      FROM (
        SELECT Id, RANK() OVER(ORDER BY Id) AS [Rank]
          FROM Posts
          WHERE ParentId = q.Id AND Score > 10 AND OwnerUserId = @UserId
      ) AS Answers
      WHERE [Rank] = 2
  ) a
--

data.stackexchange.com/scifi/query/1582291/…

you're now selecting questions where you have two answers scoring > 10

(also, the criteria for enlightenend is >= 10, but that doesn't matter)

@Glorfindel I think I only need to do that on the second answer

indeed

but it could be the third answer as well, right?

@Glorfindel true

CROSS APPLY (
    SELECT Id, Score, CreationDate, OwnerUserId
      FROM (
        SELECT Id, Score, CreationDate, OwnerUserId,RANK() OVER(ORDER BY Id) AS [Rank]
          FROM Posts
          WHERE ParentId = q.Id
      ) AS Answers
      WHERE [Rank] >= 2 AND Score >= 10 AND OwnerUserId = @UserId
  ) a

@Glorfindel too many nested selects 😵

but I got it

I'm sure rene can do better.

I got something working

Great!

@rene @Glorfindel Is there an easy way to find posts affected by this bug?

@SonictheSaveUkraine-hog I guess that would need a LIKE or a regex on the post body, which isn't feasible for Stack Overflow.

and while we can probably search for <...> inside <pre><code> and </code></pre>, that's bound to give a lot of false positives

@Glorfindel That's using regex to parse HTML, which as we all know, is a big no-no

indeed

I'm basing this off of what I know about the Markdown to HTML parser. What happens afterwards (syntax highlighting, MathJax) I don't really know anything about except from observance ...