The identification requests are taking over! (averaged instantaneous)

0

Please login or register to vote for this query.

(click on this box to dismiss)

The y-axis is the fraction of questions within a 2-week bucket starting on the date on the x-axis that had the tag `TagName`.

Science Fiction and Fantasy

Q&A for science fiction and fantasy enthusiasts

DECLARE @begin_date datetime = (SELECT MIN(CreationDate) FROM PostHistory);
DECLARE @end_date datetime = (SELECT MAX(CreationDate) FROM PostHistory);
DECLARE @width int = 28;

DECLARE @tag nvarchar(max) = ##TagName:string?identification-request##;

WITH dates AS
(
  SELECT (@begin_date + 7) AS DateValue -- exclude week 1, which is always anomalous
  UNION ALL
  SELECT DateValue + @width FROM dates WHERE DateValue + 2*@width < @end_date
)

SELECT
  d.DateValue AS [Date],
  (SELECT CAST(COUNT(p.Id) AS float)
   FROM Posts AS p 
   WHERE p.PostTypeId = 1 
         AND p.Tags LIKE '%' + @tag + '%'
         AND d.DateValue < p.CreationDate
         AND p.CreationDate < d.DateValue + @width) /
  (SELECT CAST(COUNT(p.Id) AS float)
   FROM Posts AS p 
   WHERE p.PostTypeId = 1
         AND d.DateValue < p.CreationDate
         AND p.CreationDate < d.DateValue + @width) AS [Total]
FROM
  dates AS d
OPTION (MAXRECURSION 0)

Enter Parameters

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