7

I am currently making a SEDE query to determine which questions from a specific user have made it onto the Hot Network Questions list and when. While I can get the specific questions, I am unable to figure out how to get the date when those questions became/were no longer a Hot Network Question.

From this answer, I have knowledge of how to get questions that were on the HNQ list for a specific user (this can be done by searching for questions with PostTypeHistoryId=52 that were asked by a certain user), but am unable to find anything on how to find when said questions hit/disappeared from the HNQ list, despite searching through the and tags. (along with doing a few Google searches)

This is what I currently have:

DECLARE @UserId INT = ##UserId##
SELECT DISTINCT p.Id AS [Post Link], p.PostTypeId, u.DisplayName AS [User Link], p.CreationDate
FROM Posts p
JOIN Users u ON p.OwnerUserId = u.Id
JOIN PostHistory ph ON p.Id = ph.PostId
WHERE (OwnerUserId = @UserId) AND (ph.PostHistoryTypeId = 52)
ORDER BY p.Id ASC

My question is: How should I go about extending my code as to include the times for which a question became/was no longer hot with SEDE, or is there a resource that I could use on how to do this?

Improve this question
2
  • 1
    52 is already the time it became HNQ, you just need to include that CreationDate. And 53 is when it became not HNQ, so you need ph.PoshHistoryTypeId IN (52, 53). Do you have an example user you already know has at least one such question?
    – testing-for-ya
    Commented May 15 at 16:54
  • @testing-ma-lady Yes, me. (testing on Puzzling.SE - I have 20 HNQs there)
    – CrSb0001
    Commented May 15 at 16:57

2 Answers 2

Reset to default
7

SEDE doesn't record the moment the question is no longer on the Hot Network Questions list, unless that happens because a moderator removed it (in that case, you will find a Post History with type 53). In the vast majority of the cases, the question is no longer on the list because it isn't in the top 100 anymore; other common reasons are the question being closed, or too long in the list (there's a 72 hours maximum).

I have a script which downloads the HNQ every 3 minutes since 2018, and from the results you could determine when a particular question no longer appeared on the list. I used to publish the results of it once in a while, but that paused due to other pressing matters. I might do it again in 6-8 weeks ...

Improve this answer
4

In Stack Overflow, you could use this query:

DECLARE @UserId INT = ##UserId##;

SELECT hnq.PostId AS [Post Link],
  p.PostTypeId,
  u.DisplayName AS [User Link],
  EnteredHNQ = hnq.CreationDate,
  RemovedHNQ = nhnq.CreationDate
FROM PostHistory AS hnq
INNER JOIN Posts AS p
ON hnq.PostId = p.Id
INNER JOIN Users AS u
ON p.OwnerUserId = u.Id
LEFT OUTER JOIN PostHistory AS nhnq
ON hnq.PostId = nhnq.PostId
AND nhnq.PostHistoryTypeId = 53
WHERE u.Id = @UserId
AND hnq.PostHistoryTypeId = 52;

However, it looks like 53 is, like Glorfindel identified, and as mentioned in this post, only recorded when the post was manually removed from HNQ. For a question dropping off the list naturally (72 hours, no longer in the top 100, closure):

Right now we don't have an event for a post dropping off the list.
...
This won't create a history event for now but it's something that we'd like to add in the future.

For questions manually removed:

an event will be logged in the post timeline and edit history that indicates when it was removed and by whom.

So, the answer to your question in general is, no, you won't find this information in SEDE, unless the post was intentionally removed.

Improve this answer

You must log in to answer this question.

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