Ratio of questions with specific tag

0

Please login or register to vote for this query.

(click on this box to dismiss)

Science Fiction and Fantasy

Q&A for science fiction and fantasy enthusiasts

DECLARE @BeginDate DATETIME = 'December 1, 2015';
DECLARE @EndDate DATETIME = (SELECT max(CreationDate) FROM PostHistory);

WITH Dates AS
(
  SELECT @BeginDate AS [DateValue]
  UNION ALL
  SELECT DateValue + 1 FROM Dates WHERE DateValue + 1 < @EndDate
)

SELECT D.DateValue AS [Date],

  (CASE WHEN EXISTS (SELECT * FROM Posts P 
    WHERE P.PostTypeId = 1 AND P.CreationDate < D.DateValue)
   THEN
    100.0 * (SELECT count(*) FROM Posts P 
      WHERE P.Tags LIKE '%star-wars%' AND P.CreationDate < D.DateValue) / 
    (SELECT count(*) FROM Posts P 
      WHERE P.PostTypeId = 1 AND P.CreationDate < D.DateValue)
   ELSE 0 END) AS [SW %],
  (CASE WHEN EXISTS (SELECT * FROM Posts P 
    WHERE P.PostTypeId = 1 AND P.CreationDate < D.DateValue)
   THEN
    100.0 * (SELECT count(*) FROM Posts P 
      WHERE P.Tags LIKE '%star-trek%' AND P.CreationDate < D.DateValue) / 
    (SELECT count(*) FROM Posts P 
      WHERE P.PostTypeId = 1 AND P.CreationDate < D.DateValue)
   ELSE 0 END) AS [ST %]
FROM
  Dates AS D
ORDER BY D.DateValue ASC
OPTION (MAXRECURSION 0)

Enter Parameters

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