0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

CREATE TABLE #questions (Id int, Tags nvarchar(250), CreationDate datetime);

INSERT INTO #questions
SELECT P.Id, P.Tags, P.CreationDate
FROM  Posts P
INNER JOIN PostTags PT ON PT.PostId = P.Id
INNER JOIN Tags T ON PT.TagId = T.Id
WHERE P.PostTypeId = 1
  AND P.CreationDate > '2017-01-01'
  /*AND T.TagName = 'firebase'*/
  AND T.TagName like 'firebase-%' or T.TagName = 'google-cloud-functions' or T.TagName = 'google-cloud-firestore'


SELECT c.Month, c.Type, c.Count
FROM (
  SELECT 
    DATEADD(MONTH, DATEDIFF(MONTH, 0, CreationDate), 0) AS [Month]
    , COUNT(DISTINCT Q.Id) AS Count
    , 'Flutter' AS Type
  FROM #questions Q
  WHERE Q.Tags LIKE '%flutter%' 
  GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, CreationDate), 0)
  
  UNION ALL SELECT 
    DATEADD(MONTH, DATEDIFF(MONTH, 0, CreationDate), 0) AS [Month]
    , COUNT(DISTINCT Q.Id) AS Count
    , 'Swift' AS Type
  FROM #questions Q
  WHERE Q.Tags LIKE '%swift%' 
  GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, CreationDate), 0)
  
  
  UNION ALL SELECT 
    DATEADD(MONTH, DATEDIFF(MONTH, 0, CreationDate), 0) AS [Month]
    , COUNT(DISTINCT Q.Id) AS Count
    , 'Android' AS Type
  FROM #questions Q
  WHERE Q.Tags LIKE '%android%' 
  GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, CreationDate), 0)
    
  UNION ALL SELECT 
    DATEADD(MONTH, DATEDIFF(MONTH, 0, CreationDate), 0) AS [Month]
    , COUNT(DISTINCT Q.Id) AS Count
    , 'JavaScript' AS Type
  FROM #questions Q
  WHERE Q.Tags LIKE '%javascript%' 
  GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, CreationDate), 0)
) as c
ORDER BY [MONTH] DESC
GO

fork query

edited may 29 19 Frank van Puffelen

created may 29 19 anonymous