« first day (1363 days earlier)      last day (117 days later) » 

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
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
 
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
      ) AS Answers
      WHERE [Rank] = 2
  ) a
--
WHERE AnswerScore > 10 AND AnswerUserId = @UserId
 
I think you can't use aliases inside the WHERE clause
 
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
--
 
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
      ) AS Answers
      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 ...
 

« first day (1363 days earlier)      last day (117 days later) »