Questions and answers by a group of users over time

2

Please login or register to vote for this query.

(click on this box to dismiss)

Electrical Engineering

Q&A for electronics and electrical engineering professionals, students, and enthusiasts

CREATE TABLE #users (UserId nvarchar (35), Name nvarchar (100));

INSERT INTO #users(UserId, Name) VALUES 
(209103, 'puf'),
(394010, 'katowulf'),
(110915, 'mikelehen'),
(879295, 'rdimarco'),
(1373162, 'deast'),
(5385206, 'sganem'),
(3875023, 'mpmcdonald'),
(5081766, 'bojeil'),
(807126, 'dougstevenson'),
(1410370, 'arthurthompson'),
(839465, 'mimming'),
(324977, 'samstern'),
(819314, 'ianbarber'),
(1172232, 'gregsoltis'),
(937583, 'raynor'),
(613115, 'dgiorgino'),
(1915327, 'dmabin'),
(4186942, 'bjiang'),
(4625829, 'aalawi'),
(4447017, 'andysoto'),
(978525, 'eobrain'),
(9849, 'rsattar'),
(2955366, 'jwenger'),
(1028647, 'jamesdaniels'),
(226391, 'bleigh'),
(7688078, 'rjh'),
(153407, 'danmcg'),
(3975963, 'mbonnell'),
(42960644, 'krausefx'),
(5190886, 'hkj'),
(2546685, 'oleksiyi'),
(556617, 'paulbeusterien'),
(6253640, 'mcg'),
(4816918, 'jhuleatt'),
(7164592, 'personjennifer'),
(6122677, 'kianam'),
(2207583, 'tburner'),
(1973552, 'ulukaya'),
(10880930, 'sumit'),
(11209105, 'Jeff Avis'),
(11229774, 'marcjordan'), 
(5827947, 'isachen'), 
(9789871, 'psanketi'),
(3945408, 'juanlara'),
(3043663, 'martinpatrick'),
(9754679, 'rich-gowman'),
(7644371, 'jim-morrison'),
(4092401, 'ying-li'),
(6122677, ' kianam'),
(11850212, 'ryanpbrewster'),
(1, 'Google Assistant'),
(12234169, 'manchan'),
(1491707, 'felker'),
(5733219, 'atulep'),
(2756266, 'taycaldwell'),
(12221640, 'jearleycha'),
(2, 'Smart Home'),
(656408, 'proppy'),
(10631310, 'tklopfenstein'),
(3, 'App actions'),
(1245915, 'diegozuluaga'),
(4, 'Other'),
(246461, 'smithdave'),
(59534213, 'martinpatrick'), 
(8200631, 'kevin-kokomani'), 
(8697185, 'scott-crossen'), 
(3618671, 'misha-brukman'), 
(281221, 'peter-friese'), 
(4092401, 'ying-li'), 
(6122677, 'kianam'), 
(7644371, 'jim-morrison'), 
(14466144, 'brettsneed'),
(13451151, 'kelsk'),
(5, '20211014'),
(8935548, 'mgoya'),
(12857703, 'rafael-lemos'),
(365738, 'sethvargo'),
(1476372, 'tony-j-huang'),
(5385206, 'steve-ganem'),
(16167525, 'vsfan'),
(12919863, 'maksym-malyhin'),
(1290844, 'vkryachko'),
(12015022, 'wesley-tarle'),
(13925433, 'alex-volkovitsky')
;

SELECT Q.MONTH, Q.Count AS "##Tag?firebase%## questions", 
(Q.Count - Closed.Count) AS "Unclosed questions", Answers.Count AS "Questions with answer", GooglerAnswers.Count AS "Questions with Googler answer"
FROM (
  SELECT 
    DATEADD(MONTH, DATEDIFF(MONTH, 0, CreationDate), 0) AS [MONTH]
    , COUNT(*) AS Count
  FROM Posts
  INNER JOIN PostTags ON PostTags.PostId = Posts.Id
  INNER JOIN Tags ON PostTags.TagId = Tags.Id
  WHERE Tags.TagName LIKE '##Tag##' AND PostTypeId = 1 AND CreationDate > '2015-01-01'
  GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, CreationDate), 0)
) AS Q
LEFT JOIN (
  SELECT 
    DATEADD(MONTH, DATEDIFF(MONTH, 0, A.CreationDate), 0) AS [MONTH]
    , COUNT(*) AS Count
  FROM Posts AS A
  INNER JOIN Posts AS Q ON A.ParentId = Q.Id
  INNER JOIN PostTags ON PostTags.PostId = Q.Id
  INNER JOIN Tags ON PostTags.TagId = Tags.Id
  WHERE Tags.TagName LIKE '##Tag##'
    AND A.CreationDate > '2015-01-01'
    AND EXISTS(SELECT * FROM #users WHERE UserId = A.OwnerUserId)
  GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, A.CreationDate), 0)
) AS GooglerAnswers
ON Q.MONTH = GooglerAnswers.MONTH
LEFT JOIN (
  SELECT 
    DATEADD(MONTH, DATEDIFF(MONTH, 0, Q.CreationDate), 0) AS [MONTH]
    , COUNT(*) AS Count
  FROM Posts AS Q
  INNER JOIN PostTags ON PostTags.PostId = Q.Id
  INNER JOIN Tags ON PostTags.TagId = Tags.Id
  WHERE Tags.TagName LIKE '##Tag##'
    AND Q.CreationDate > '2015-01-01'
    AND EXISTS(SELECT Answers.Id FROM Posts AS Answers WHERE Answers.ParentId = Q.Id)
  GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, Q.CreationDate), 0)
) AS Answers
ON Q.MONTH = Answers.MONTH
LEFT JOIN (
  SELECT 
    DATEADD(MONTH, DATEDIFF(MONTH, 0, CreationDate), 0) AS [MONTH]
    , COUNT(*) AS Count
  FROM Posts
  INNER JOIN PostTags ON PostTags.PostId = Posts.Id
  INNER JOIN Tags ON PostTags.TagId = Tags.Id
  WHERE Tags.TagName LIKE '##Tag##' AND PostTypeId = 1 AND CreationDate > '2015-01-01' AND ClosedDate > '2015-01-01'
  GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, CreationDate), 0)
) AS Closed
ON Q.MONTH = Closed.MONTH
ORDER BY Q.Month
;

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results