4

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for those who study, teach, research and apply economics and econometrics

DECLARE @max_results int = ##MaxNumberOfResults:int?100##
DECLARE @min_qualifying_answers int = ##MinimumQualifyingAnswers:int?2##
DECLARE @userId int = ##UserId:int?0##


SELECT TOP (@max_results)
  a.OwnerUserId AS [User Link],
  COUNT(*) AS [Count],
  (CASE WHEN COUNT(*) >= 500 THEN N'★★★'
        WHEN COUNT(*) >=  50 THEN N'★★'
        WHEN COUNT(*) >=   1 THEN N'★'
        ELSE                      N'' END) AS [Badge]
FROM
  Posts AS a
  LEFT OUTER JOIN Posts AS q
    ON a.ParentId = q.Id
WHERE 
      q.PostTypeId = 1
  AND q.ClosedDate IS NULL
  AND a.PostTypeId = 2
  AND a.Score >= 1
  AND ISNULL(q.OwnerUserId, -1) <> a.OwnerUserId
  AND EXISTS(
    SELECT 1
      FROM PostHistory AS ph
     WHERE     ph.PostHistoryTypeId IN (4, 5)
           AND ph.PostId = a.ParentId
           AND ph.UserId = a.OwnerUserId
           AND ABS(DATEDIFF(HOUR, ph.CreationDate, a.CreationDate)) < 12
  )
   
GROUP BY
  a.OwnerUserId
HAVING
(a.OwnerUserId = @userID or @userID=0)
  and
  COUNT(*) >= @min_qualifying_answers
ORDER BY 
  [Count] DESC

fork query

edited apr 6 15 conradfrix

created mar 19 15 anonymous