## Percentage stats for HNQ hits with proposed autofilters

0

(click on this box to dismiss)

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

WITH HNQHits as (SELECT
DATEFROMPARTS(YEAR(MIN(ph.CreationDate)), MONTH(MIN(ph.CreationDate)), 1) as [Month],
(CASE WHEN
MIN(ph2.CreationDate) IS NOT NULL
OR MIN(p.ClosedDate) IS NOT NULL
THEN 1 ELSE 0 END) as [Removed],
-- The following list is from https://math.meta.stackexchange.com/questions/31425/autofilters-for-hot-network-questions
(CASE WHEN
p.Title NOT LIKE '%this%'
AND p.Title NOT LIKE '%problem%'
AND p.Title NOT LIKE '%question%'
AND p.Title NOT LIKE '%puzzle%'
AND p.Title NOT LIKE '%contest%'
AND p.Title NOT LIKE '$advice%' AND p.Title NOT LIKE '%help%' AND p.Title NOT LIKE '%please%' AND p.Title NOT LIKE '%the following%' AND p.Title NOT LIKE '%exercise%' AND p.Title NOT LIKE '%easy%' AND p.Title NOT LIKE '%opinion%' AND p.Title NOT LIKE '%solve%' AND p.Title NOT LIKE '%solving%' AND p.Title NOT LIKE '%difficult%' AND p.Title NOT LIKE '%urgent%' AND p.Title NOT LIKE '%tricky%' AND p.Title NOT LIKE '%simplify%' THEN 1 ELSE 0 END) as [Filtered] 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 AND p.Title NOT LIKE '%$%\$%' -- MathJax titles are already exluded from HNQ
GROUP BY p.Id, p.Title
)
SELECT
[Month],
100 * SUM(Removed) / COUNT(*) as [% removed or closed (total)],
(CASE WHEN SUM(Filtered) > 0 THEN 100 * SUM(Removed * Filtered) / SUM(Filtered) ELSE NULL END) as [% removed or closed (filter pass)],
(CASE WHEN SUM(1-Filtered) > 0 THEN 100 * SUM(Removed * (1-Filtered)) / SUM(1-Filtered) ELSE NULL END)  as [% removed or closed (filter match)]
FROM HNQHits
GROUP BY [Month]
ORDER BY [Month]

### Enter Parameters

Options:
-
Switch to meta site
Hold tight while we fetch your results
:records returned in :time ms:cached