DECLARE @StartDate DATETIME; SET @StartDate = '2015-01-01';

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 @StartDate DATETIME; 
SET @StartDate = '2015-01-01';
DECLARE @EndDate DATETIME; 
SET @EndDate = DATEADD(MONTH, 1, @StartDate);

WITH QuestionsWithAcceptedAnswer AS (
  SELECT allQ.Id
  FROM Posts allQ
  INNER JOIN Posts allA
    ON allQ.PostTypeId = 1 
    AND allA.PostTypeId = 2
    AND allQ.AcceptedAnswerId = allA.Id
    AND allQ.CreationDate >= @StartDate
), QuestionsWithAcceptedAnswersWithinOneMonth AS (
  SELECT monQ.Id
  FROM Posts monQ
  INNER JOIN Posts monA
    ON monQ.PostTypeId = 1 
    AND monA.PostTypeId = 2
    AND monQ.AcceptedAnswerId = monA.Id
    AND monQ.CreationDate >= @StartDate
    AND monA.CreationDate < @EndDate
  INNER JOIN Votes monV
    ON monV.PostId = monA.Id
    AND monA.PostTypeId =2
    AND monV.VoteTypeId = 1 -- accepted by OP
    AND monV.CreationDate <= @EndDate  -- accepted before end date (1 month from start)
)
SELECT TOP 100
  Users.Id AS [User Link]
, Users.Reputation
, (SELECT COUNT(*) FROM Posts 
    WHERE Posts.CreationDate >= @StartDate
    AND Posts.CreationDate < @EndDate
    AND Posts.OwnerUserId = Users.Id
  ) AS [Questions in time period]
/*
, SUM(
    CASE
    WHEN EXISTS 
      (SELECT TOP 1 1 FROM QuestionsWithAcceptedAnswer WHERE Id = allQ.Id) 
      THEN 1 
      ELSE 0
    END
  ) AS [AllQAccept]
, SUM(
    CASE
    WHEN EXISTS 
      (SELECT TOP 1 1 FROM QuestionsWithAcceptedAnswersWithinOneMonth WHERE Id = allQ.Id) 
      THEN 1 
      ELSE 0
    END
  ) AS [MonQAccept]
*/
FROM Users
  INNER JOIN Posts 
    ON Users.Id = Posts.OwnerUserId
  INNER JOIN QuestionsWithAcceptedAnswer QA
    ON Posts.Id = QA.Id
  INNER JOIN QuestionsWithAcceptedAnswersWithinOneMonth QM
    ON Posts.Id = QM.Id
GROUP BY 
  Users.Id
, Users.Reputation
, Posts.CreationDate
ORDER BY Users.Reputation DESC;

Enter Parameters

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