Monthly stats on HNQ hits with proposed autofilters

0

Please login or register to vote for this query.

(click on this box to dismiss)

Mathematics

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],
  COUNT(*) as [Total (no filter)],
  SUM(Filtered) as [Total (with filter)],
  SUM(Removed) as [Removed or closed (no filter)],
  SUM(Removed * Filtered) as [Removed or closed (with filter)]
FROM HNQHits
GROUP BY [Month]
ORDER BY [Month]

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results