8:34 AM
Basically just a reminder that there is also a room on the other chatserver:
in SEDE - Stack Exchange Data Explorer on The Stack Exchange Network Chat, Apr 6 at 23:22, by Thomas Markov
“Questions I have answered where a different answer was awarded the enlightened badge”
in SEDE - Stack Exchange Data Explorer on The Stack Exchange Network Chat, 2 mins ago, by Martin Sleziak
@ThomasMarkov Would this maybe work at least as an approximation of such query? https://data.stackexchange.com/rpg/query/1582106/answered-by-me-but-somebody-else-got-the-enlightened-badge?userid=62294

5 hours later…
1:50 PM
@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

oh, sure, but as seconds, like Objective C? milliseconds, like Unix timestamp? days, like Excel (it is a M\$ product after all)

@Glorfindel oh no clue
> 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

2:05 PM
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
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
WHERE [Rank] = 2
) a
--

you're now selecting questions where you have two answers scoring > 10
(also, the criteria for enlightenend is >= 10, but that doesn't matter)

2:18 PM
@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
WHERE [Rank] >= 2 AND Score >= 10 AND OwnerUserId = @UserId
) a

2:34 PM
@Glorfindel too many nested selects 😵
but I got it

I'm sure rene can do better.

2:57 PM
I got something working

3:44 PM
Great!

4:06 PM
in TRPG General Chat on The Stack Exchange Network Chat, 1 hour ago, by AncientSwordRage
@ThomasMarkov ta da! (thanks to Glorindel for the help) https://data.stackexchange.com/rpg/query/1582291/answers-that-missed-out-on-being-enlightened?MinutesLate=30&UserId=62294

4 hours later…
7:38 PM
@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

8:12 PM
@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 ...