1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for practitioners, researchers, and students in cognitive science, psychology, neuroscience, and psychiatry

WITH Raw AS (
  SELECT
    A.OwnerUserId AS UserId,
    COUNT(CASE WHEN A.OwnerUserId = AA.OwnerUserId THEN 1 END)
      AS AcceptedAnswers,
    COUNT(*) AS AllAnswers
  FROM Posts A
  LEFT JOIN Posts Q ON A.ParentId = Q.Id
  LEFT JOIN Posts AA ON Q.AcceptedAnswerId = AA.Id
  WHERE
    A.OwnerUserId > 0
    AND
    (Q.OwnerUserId IS NULL OR Q.OwnerUserId <> A.OwnerUserId)
  GROUP BY A.OwnerUserId
),
Raw2 AS (
  SELECT *, 100.0 * AcceptedAnswers / AllAnswers AS AcceptedRatio
  FROM Raw
),
Raw3 AS (
  SELECT
    *,
    DENSE_RANK() OVER (ORDER BY AcceptedRatio DESC) AS AcceptedRatioRank,
    DENSE_RANK() OVER (ORDER BY AllAnswers DESC) AS AllAnswersRank
  FROM Raw2
),
Raw4 AS (
  SELECT *,
    1.0 * (AcceptedRatioRank - 1) * ##AcceptRatioImportance:float?1##
    / (SELECT MAX(AcceptedRatioRank) - 1 FROM Raw3)
    AS AcceptedRatioRankRel,
    1.0 * (AllAnswersRank - 1) * ##AnswerCountImportance:float?1##
    / (SELECT MAX(AllAnswersRank) - 1 FROM Raw3)
    AS AllAnswersRankRel
  FROM Raw3
)
SELECT TOP 100
  UserId AS [User Link],
  AcceptedAnswers,
  AllAnswers,
  AcceptedRatio
FROM Raw4
ORDER BY AcceptedRatioRankRel + AllAnswersRankRel

fork query

edited jan 27

created jan 27 peterh - PLEASE DELETE THIS ACCOUNT