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