0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for administrators and users of the CiviCRM Constituent Relationship Management software

/*
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)

fork query

edited nov 4 anonymous

created dec 15 20 Frank van Puffelen