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 
  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]

view current version

edited apr 20 20

created apr 20 20 Ilmari Karonen