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 RawHits 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],
  (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
),
HNQHits as (SELECT *,
  -- The following list is from https://math.meta.stackexchange.com/questions/31425/autofilters-for-hot-network-questions
  (CASE 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 1 ELSE 0 END) as [Filtered]
FROM RawHits
)
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