Q&A about the site for Tridion developers and administrators
/* DECLARE @TagsFilter VARCHAR(1000) SET @TagsFilter = '(Tags.TagName like ''firebase%'')' SET @TagsFilter = 'firebase%' */ /* CREATE TABLE #tagfilters (tag nvarchar(100) COLLATE SQL_Latin1_General_CP1_CS_AS); INSERT INTO #tagfilters VALUES ('firebase%'), ('google-cloud-firestore'), ('google-cloud-functions'); */ CREATE TABLE #userinteractions (Date Date, UserId nvarchar(100), Count int, Type nvarchar(10)); INSERT INTO #userinteractions SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, CreationDate), 0) AS Date , OwnerUserId AS UserId , COUNT(*) AS Count , 'Post' AS Type FROM Posts INNER JOIN PostTags ON PostTags.PostId = Posts.Id INNER JOIN Tags ON PostTags.TagId = Tags.Id WHERE (Tags.TagName like 'flutterflow%') AND PostTypeId = 1 AND CreationDate > '2020-01-01' GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, CreationDate), 0), OwnerUserId INSERT INTO #userinteractions SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, Votes.CreationDate), 0) AS Date , UserId , COUNT(*) AS Count , 'Vote' AS Type FROM Votes INNER JOIN Posts ON Posts.Id = Votes.PostId INNER JOIN PostTags ON PostTags.PostId = Posts.Id INNER JOIN Tags ON PostTags.TagId = Tags.Id WHERE (Tags.TagName like 'flutterflow%') AND Votes.CreationDate > '2020-01-01' GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, Votes.CreationDate), 0), UserId INSERT INTO #userinteractions SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, Comments.CreationDate), 0) AS Date , UserId AS UserId , COUNT(*) AS Count , 'Comment' AS Type FROM Comments INNER JOIN Posts ON Posts.Id = Comments.PostId INNER JOIN PostTags ON PostTags.PostId = Posts.Id INNER JOIN Tags ON PostTags.TagId = Tags.Id WHERE (Tags.TagName like 'flutterflow%') AND Comments.CreationDate > '2020-01-01' GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, Comments.CreationDate), 0), UserId SELECT * FROM #userinteractions ORDER BY Date DESC SELECT Date, COUNT(UserId) FROM #userinteractions GROUP BY Date ORDER BY Date DESC -- TODO: Find other types to include -- AI: Aggregation Interval (MONTH, YEAR, WEEK)