0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for information security professionals

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 like 'firebase-%' or T.TagName = 'google-cloud-functions' or T.TagName = 'google-cloud-firestore' or T.TagName like '%crashlytics%')


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 and ObjC' AS Type
  FROM #questions Q
  WHERE Q.Tags LIKE '%swift%' OR Q.Tags LIKE '%objective-c%' OR Q.Tags LIKE '%ios%'
  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%' or Q.Tags LIKE '%kotlin%' 
  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%' OR Q.Tags LIKE '%typescript%' OR Q.Tags LIKE '%jquery%'
  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
    , 'Unity and C++' AS Type
  FROM #questions Q
  WHERE Q.Tags LIKE '%unity3d%' OR Q.Tags LIKE '%c++%'
  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
    , 'React Native' AS Type
  FROM #questions Q
  WHERE Q.Tags LIKE '%react-native%'  
  GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, CreationDate), 0)) as c
ORDER BY [MONTH] DESC
GO

view current version

edited mar 29 20

created jun 1 19 Frank van Puffelen