Access time of specific tag users

0

Please login or register to vote for this query.

(click on this box to dismiss)

Movies and TV

Q&A for movie and tv enthusiasts

DECLARE @StartTime DATETIME = convert(DATETIME, '##StartTime?2012/01/01 00:00:00##')
DECLARE @Pattern VARCHAR(25) = ##TagPattern:string?%identify-this-%##;
DECLARE @Period INT = ##TimePeriod:int?30##;

WITH UserQuestions AS
(
  SELECT U.*, Q.Tags, Q.CreationDate AS [QuestionDate]
  FROM Users U INNER JOIN Posts Q ON 
    Q.PostTypeId = 1 AND Q.OwnerUserId = U.Id AND 
    NOT EXISTS (SELECT * FROM Posts P WHERE P.OwnerUserId = Q.OwnerUserId AND 
      P.PostTypeId = 1 AND P.CreationDate < Q.CreationDate)
  WHERE U.CreationDate > @StartTime
),
Times AS
(
  SELECT 0 AS [TimeValue]
  UNION ALL
  SELECT TimeValue + 1 FROM Times WHERE TimeValue + 1 <= @Period * 24
)

SELECT T.TimeValue / 24.0 AS [Days], 
  100.0 * (SELECT count(*) FROM UserQuestions U WHERE 
    dateadd(hour, T.TimeValue, U.QuestionDate) <= U.LastAccessDate) / 
    (SELECT count(*) FROM UserQuestions) AS [Askers  %], 
  100.0 * (SELECT count(*) FROM UserQuestions U WHERE Tags LIKE @Pattern AND 
    dateadd(hour, T.TimeValue, U.QuestionDate) <= U.LastAccessDate) / 
    (SELECT count(*) FROM UserQuestions WHERE Tags LIKE @Pattern) AS [ID Askers %], 
  100.0 * (SELECT count(*) FROM UserQuestions U WHERE Tags NOT LIKE @Pattern AND 
    dateadd(hour, T.TimeValue, U.QuestionDate) <= U.LastAccessDate) / 
    (SELECT count(*) FROM UserQuestions WHERE Tags NOT LIKE @Pattern) AS [Non-ID Askers %]
FROM
  Times AS T
ORDER BY T.TimeValue
OPTION (MAXRECURSION 0)

Enter Parameters

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