0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for people studying math at any level and professionals in related fields

WITH HNQHits as (SELECT
  p.Id AS [PostId],
  MIN(ph.CreationDate) as [Date],
  MIN(ph2.CreationDate) as [Removed],
  MIN(p.ClosedDate) as [Closed],
  (SELECT TOP 1 ph3.[Text]
   FROM PostHistory ph3
   WHERE ph3.PostId = p.Id
     AND ph3.PostHistoryTypeId IN (1, 4, 7) -- New/Edit/Rollback Title
     AND ph3.CreationDate <= MIN(ph.CreationDate)
   ORDER BY ph3.CreationDate DESC) as [Title]
FROM PostHistory ph
JOIN Posts p ON p.Id = ph.PostId
LEFT JOIN PostHistory ph2
  ON p.Id = ph2.PostId
  AND ph2.PostHistoryTypeId = 53 -- HNQ removal event
WHERE
  p.PostTypeId = 1 -- Question
  AND ph.PostHistoryTypeId = 52 -- HNQ hit event
GROUP BY p.Id
)
SELECT
  [Date], [PostId] as [Post Link], [Title] as [Original Title],
  -- The following list is from https://math.meta.stackexchange.com/questions/31425/autofilters-for-hot-network-questions
  (CASE
  WHEN Title LIKE '%$%$%' THEN 'MathJax' -- MathJax titles are already excluded from HNQ
  WHEN Title NOT LIKE '%this%'
    AND Title NOT LIKE '%problem%'
    AND Title NOT LIKE '%question%'
    AND Title NOT LIKE '%puzzle%'
    AND Title NOT LIKE '%contest%'
    AND Title NOT LIKE '$advice%'
    AND Title NOT LIKE '%help%'
    AND Title NOT LIKE '%please%'
    AND Title NOT LIKE '%the following%'
    AND Title NOT LIKE '%exercise%'
    AND Title NOT LIKE '%easy%'
    AND Title NOT LIKE '%opinion%'
    AND Title NOT LIKE '%solve%'
    AND Title NOT LIKE '%solving%'  
    AND Title NOT LIKE '%difficult%'
    AND Title NOT LIKE '%urgent%'
    AND Title NOT LIKE '%tricky%'
    AND Title NOT LIKE '%simplify%'
  THEN NULL ELSE 'Yes' END) as [Filter],
  [Removed], [Closed]
FROM HNQHits
ORDER BY [Date] DESC

fork query

edited apr 20 Ilmari Karonen

created nov 16 19 Josh29812