Daily activity on a site

1

Please login or register to vote for this query.

(click on this box to dismiss)

Raspberry Pi

Q&A for users and developers of hardware and software for Raspberry Pi

SELECT MAX(ph.CreationDate), MAX(ph.UserId) AS [User Link],
  STRING_AGG(pht.Name, ', '),
  'site://a/' + CAST(MAX(ph.PostId) AS NVARCHAR) + '|' + COALESCE(MAX(p.Title), MAX(q.Title))
  FROM PostHistory AS ph
  INNER JOIN PostHistoryTypes AS pht
    ON ph.PostHistoryTypeId = pht.Id  
  INNER JOIN Posts AS p
    ON ph.PostId = p.Id
  LEFT OUTER JOIN Posts AS q
    ON p.ParentId = q.Id
  WHERE ph.CreationDate BETWEEN ##date:string##
    AND DATEADD(DAY, 1, CAST(##date:string## AS DATE))
  GROUP BY ph.RevisionGUID
                            
UNION ALL

SELECT c.CreationDate, c.UserId AS [User Link], 'Add Comment',
  'site://posts/comments' + CAST(c.Id AS NVARCHAR) + '|' + COALESCE(p.Title, q.Title)
  FROM Comments AS c
  INNER JOIN Posts AS p
    ON c.PostId = p.Id
  LEFT OUTER JOIN Posts AS q
    ON p.ParentId = q.Id  
  WHERE c.CreationDate BETWEEN ##date:string##
    AND DATEADD(DAY, 1, CAST(##date:string## AS DATE))

ORDER BY 1

Enter Parameters

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