Ratio of user's questions in a tag

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

DECLARE @UserId VARCHAR(25) = ##UserId##;
DECLARE @StartDate DATETIME = (SELECT CreationDate FROM Users WHERE Id=@UserId);
DECLARE @EndDate DATETIME = (SELECT max(CreationDate) FROM PostHistory);
DECLARE @Pattern VARCHAR(25) = ##TagPattern:string?%identification%##;

WITH Dates AS (
  SELECT @StartDate AS [DateValue]
  UNION ALL
  SELECT DateValue + 1 FROM Dates WHERE DateValue + 1 < @EndDate
),
UserQuestions AS (
  SELECT P.Id, P.CreationDate, P.Tags
  FROM Posts P
  WHERE
    P.PostTypeId = 1 /* Questions */
    AND P.OwnerUserId=@UserId
)
SELECT D.DateValue AS [Date],
  (CASE WHEN EXISTS (SELECT * FROM UserQuestions P 
    WHERE P.CreationDate < D.DateValue)
   THEN
    100.0 * (SELECT count(*) FROM UserQuestions P 
      WHERE P.Tags LIKE @Pattern AND P.CreationDate < D.DateValue) / 
    (SELECT count(*) FROM UserQuestions P 
      WHERE P.CreationDate < D.DateValue)
   ELSE 0 END) AS [ID %]
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