1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for scientists using computers to solve scientific problems

SELECT 
  YEAR(q.creationDate) AS Year, 
  count(*) AS Number, 
  ROUND(AVG(CAST(q.Score AS FLOAT)),2) AS Q_AvgScore, 
  MAX(q.Score) AS Q_MaxScore, 
  SUM(CASE WHEN q.Score > 0 THEN q.Score ELSE 0 END) AS Q_SumPositive,
  SUM(CASE WHEN q.Score < 0 THEN q.Score ELSE 0 END) AS Q_SumNegative,
  SUM(CASE WHEN q.Score > 0 THEN 1 ELSE 0 END) AS Q_NumPositive,
  SUM(CASE WHEN q.Score = 0 THEN 1 ELSE 0 END) AS Q_NumZero,
  SUM(CASE WHEN q.Score < 0 THEN 1 ELSE 0 END) AS Q_NumNegative,
  ROUND(AVG(CAST(a.Score AS FLOAT)),2) AS A_AvgScore, 
  MAX(a.Score) AS A_MaxScore,
  SUM(CASE WHEN a.Score > 0 THEN 1 ELSE 0 END) AS A_NumPositive,
  SUM(CASE WHEN a.Score = 0 THEN 1 ELSE 0 END) AS A_NumZero,
  SUM(CASE WHEN a.Score < 0 THEN 1 ELSE 0 END) AS A_NumNegative
FROM Posts q
INNER JOIN Posts a ON q.Id = a.ParentId
INNER JOIN Users uq ON q.OwnerUserId = uq.Id
INNER JOIN Users ua ON a.OwnerUserId = ua.Id
WHERE
  q.AnswerCount = 1 AND
  uq.Reputation <= ##AskerReputation:int?1## AND     -- Try from 1 to 50 
  ua.Reputation <= ##AnswererReputation:int?1## AND  -- Try from 1 to 50
  Year(q.creationDate) >= ##StartYear:int?2021## AND -- Try from 2010 to 2021
  DateDiff(day, q.creationDate, a.creationDate) <= ##DaysBeforeAnswering:int?1##
GROUP BY YEAR(q.creationDate)
ORDER BY YEAR(q.creationDate)

fork query

edited jan 11 24

created jan 11 24 PeterJames