# of questions progress

0

Please login or register to vote for this query.

(click on this box to dismiss)

Meta Stack Exchange

Q&A for meta-discussion of the Stack Exchange family of Q&A websites

-- number range, cf. https://stackoverflow.com/a/34994136/4751173
DECLARE @Min INT = 0, @Max INT = ##AgeInDays:int##;
CREATE TABLE #numbers (Number INT);
INSERT INTO #numbers
SELECT TOP (@Max - @Min + 1) @Min - 1 + ROW_NUMBER() OVER(ORDER BY t1.number) 
  FROM master..spt_values t1 
  CROSS JOIN master..spt_values t2;

CREATE TABLE #tagNames (Id INT IDENTITY(1, 1), TagName NVARCHAR(35) COLLATE SQL_Latin1_General_CP1_CS_AS);
INSERT INTO #tagNames (TagName)
SELECT VALUE FROM STRING_SPLIT(##TagNames:string##, ',');
-- TagNames: comma-separated list of tag names

CREATE TABLE #creationDates (Id INT IDENTITY(1, 1), CreationDate DATETIME);
INSERT INTO #creationDates (CreationDate)
SELECT CAST(VALUE AS DATETIME) FROM STRING_SPLIT(##CreationDates:string##, ',');
-- CreationDates: comma-separated list of dates from where to count

CREATE TABLE #results (TagName NVARCHAR(35), Age INT, NumberOfQuestions INT);

WITH TagCreationDates AS
(SELECT TagName, CreationDate
  FROM #tagNames INNER JOIN #creationDates
    ON #tagNames.Id = #creationDates.Id
), QuestionsPerDay AS 
(SELECT t.TagName, COUNT(*) AS NumberOfQuestions,
  DATEDIFF(DAY, tcd.CreationDate, q.CreationDate) AS Age
  FROM Posts AS q
  INNER JOIN PostTags AS pt
    ON q.Id = pt.PostId
  INNER JOIN Tags AS t
    ON t.Id = pt.TagId
  INNER JOIN TagCreationDates AS tcd
    ON t.TagName = tcd.TagName
   AND DATEDIFF(DAY, tcd.CreationDate, q.CreationDate) BETWEEN 0 AND ##AgeInDays:int##
  GROUP BY t.TagName, DATEDIFF(DAY, tcd.CreationDate, q.CreationDate)
), Tags_ AS
(SELECT TagName, MAX(Age) AS MaxAge
  FROM QuestionsPerDay
  GROUP BY TagName
)
INSERT INTO #results
SELECT t.TagName, n.Number, SUM(s1.NumberOfQuestions) AS NumberOfQuestions
  FROM Tags_ AS t
  INNER JOIN #numbers AS n
    ON n.Number <= t.MaxAge
  INNER JOIN QuestionsPerDay AS s1
    ON t.TagName = s1.TagName
   AND n.Number >= s1.Age
  GROUP BY t.TagName, n.Number;

-- cf. https://stackoverflow.com/a/38505375/4751173
DECLARE @columns NVARCHAR(MAX) = '';
SELECT @columns = @columns + QUOTENAME(TagName) + ',' FROM (SELECT DISTINCT TagName FROM #results) AS Temp;
SELECT @columns = SUBSTRING(@columns, 0, LEN(@columns));

EXECUTE('SELECT * FROM (SELECT TagName, NumberOfQuestions, Age FROM #results) Temp
PIVOT (MAX(NumberOfQuestions) FOR TagName IN (' + @columns + ')) Temp2');

Enter Parameters

Options:
Switch sites
loading Hold tight while we fetch your results