SELECT ph.NextActivity, COUNT(*) FROM ( SELECT *, NextAct...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

SELECT 
  ph.NextActivity, COUNT(*)
FROM
(
    SELECT *, NextActivity = LEAD(ph.Name, 1, 'None') OVER (PARTITION BY ph.PostId ORDER BY ph.CreationDate)
    FROM (
        SELECT
          ph.PostId,
          pht.Name,
          ph.CreationDate
        FROM PostHistory ph
        JOIN PostHistoryTypes pht ON ph.PostHistoryTypeId = pht.Id
        WHERE ph.CreationDate >= '2021-01-01'
        
        UNION ALL
        
        SELECT
          p.ParentId,
          vt.Name,
          v.CreationDate
        FROM Votes v
        JOIN VoteTypes vt ON vt.Id = v.VoteTypeId
        JOIN Posts p ON p.Id = v.PostId
        WHERE p.PostTypeId = 2 AND v.VoteTypeId NOT IN (3, 5, 8, 9)
          AND v.CreationDate >= '2021-01-01'
    ) ph
) ph
WHERE ph.Name = 'CommunityBump'
GROUP BY ph.NextActivity
ORDER BY COUNT(*) DESC

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results