1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for people interested in the history and origins of science and mathematics

declare @EndDate datetime = (select case
  -- If we request 'today', actually use the date
  -- of the most recent transfer to this database,
  -- which may be as much as a week in the past.
  when lower(##EndDate:string?today##) = 'today' then max(CreationDate)
  -- Otherwise, specify the most recent date to include.
  else ##EndDate##
  end
  from Posts
);
  
declare @StartDate datetime = dateadd(day, -1*##DaysBack?30##, @EndDate);

SELECT
ISNULL(vote_info.Score,0) as Score,
COUNT(answers.Id) AS 'Answers with Score'
FROM Posts Questions 
INNER JOIN Posts answers ON answers.ParentId = Questions.Id
--Add up the score for the answers
LEFT JOIN (
  SELECT Score=SUM(case 
--Always count downvotes
    when VoteTypeId = 3 then -1
--Ignore anything except an upvote
    when VoteTypeId != 2 then 0
--If the question is still not HNQ, +1
    when ph.MadeHNQ IS NULL then 1
--If the question was HNQ before the vote, don't count the upvote
--Since vote times are truncated, be pessimistic and add a day to the vote time
    when (ph.MadeHNQ) <= dateadd(day,1,Votes.CreationDate) then 0
--Otherwise +1
    else 1 end), PostId=Votes.PostId
  FROM Votes
  INNER JOIN Posts answers ON answers.Id=Votes.PostId
  INNER JOIN Posts questions ON questions.Id=answers.ParentID
  LEFT JOIN (SELECT PostId, MadeHNQ=MIN(CreationDate) FROM PostHistory WHERE PostHistoryTypeId=52 GROUP BY PostId) ph ON Questions.Id=ph.PostId
  GROUP BY Votes.PostId
  ) vote_info ON answers.Id = vote_info.PostId
WHERE answers.OwnerUserId = ##userId##
  AND questions.CommunityOwnedDate IS NULL
  AND answers.CreationDate between @StartDate and @EndDate
GROUP BY ISNULL(vote_info.Score,0)
ORDER BY ISNULL(vote_info.Score,0);

fork query

edited apr 29

created apr 29 chris.pse