1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

DECLARE @UserId AS INT = ##UserId:int##

SELECT
  MAX(DATEPART(HOUR, H.CreationDate)) AS UTCHour,
  CASE
    WHEN MAX(H.PostHistoryTypeId) IN (1) THEN 'post_create'
    WHEN MAX(H.PostHistoryTypeId) IN (4) THEN 'post_edit_title'
    WHEN MAX(H.PostHistoryTypeId) IN (5) THEN 'post_edit_body'
    WHEN MAX(H.PostHistoryTypeId) IN (6) THEN 'post_edit_tags'
    ELSE 'error'
  END AS Type,
  COUNT(*) AS EventCount
FROM PostHistory H
WHERE H.UserId = @UserId AND H.PostHistoryTypeId IN (1,4,5,6)
GROUP BY DATEPART(HOUR, H.CreationDate), H.PostHistoryTypeId

UNION
SELECT
  MAX(DATEPART(HOUR, C.CreationDate)) AS UTCHour,
  'comment' AS Type,
  COUNT(*) AS EventCount
FROM Comments C
WHERE C.UserId = @UserId
GROUP BY DATEPART(HOUR, C.CreationDate)

fork query

edited mar 22 23

created mar 20 23 starball