0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

DECLARE @minPosts INT, @minScore INT
DECLARE @tagPattern NVARCHAR(64)

SET @minPosts = 10
SET @minScore = 100

--SET @tagPattern = '%'
--SET @tagPattern = '%mathematica%'
SET @tagPattern = 'mathematica'

;WITH
  scores AS (
    SELECT DISTINCT
      u.Id AS UserId
    , a.Title
    , a.id AS "Post Link"
    , a.Score
    FROM Posts AS q
    INNER JOIN PostTags AS xqt
      ON xqt.postId = q.id
    INNER JOIN Tags AS t
      ON t.id = xqt.tagId
      AND t.tagName LIKE @tagPattern
    INNER JOIN Posts AS a
      ON a.parentId = q.id
    INNER JOIN PostTypes AS pt
      ON pt.Id = a.PostTypeId
      AND pt.Name = 'Answer'
    INNER JOIN Users AS u
      ON u.Id = a.OwnerUserId
  )
, totals AS (
    SELECT
      UserId AS "User Link"
    , SUM(Score) AS Score
    , COUNT(*) AS Posts
    , 1.0*SUM(Score) / COUNT(*) AS AvgScore
    FROM scores
    GROUP BY UserId
    HAVING COUNT(*) > @minPosts
    AND SUM(Score) > @minScore
  )
SELECT TOP 50
  *
FROM totals
ORDER BY AvgScore DESC

fork query

edited jan 14 13

created jan 14 13 WReach