WITH sel AS ( SELECT u.Id , u.Reputation , u.DisplayName ...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Unix and Linux

Q&A for users of Linux, FreeBSD and other Un*x-like operating systems

WITH sel AS (
  SELECT
      u.Id
    , u.Reputation
    , u.DisplayName
    , ( SELECT
            COUNT(*)
        FROM
            Posts p
        WHERE
            p.PostTypeId = 2
        AND p.OwnerUserId = u.Id
      ) AS total_answers
    , ( SELECT
            COUNT(*)
        FROM
            Posts p
        WHERE
            p.PostTypeId = 2
        AND p.OwnerUserId = u.Id
        AND p.Score = 0
      ) AS zero_score_posts
    , ( SELECT
            MAX(p.CreationDate)
        FROM
            Posts p
        WHERE
            p.PostTypeId = 2
        AND p.OwnerUserId = u.Id
      ) AS last_answer
  FROM
      Users u
  WHERE
      u.Reputation >= 2000
  AND 0 <
    ( SELECT
          COUNT(1)
      FROM
          Posts p
      WHERE
          p.PostTypeId = 2
      AND p.OwnerUserId = u.Id
    )
)
SELECT
    COUNT(sel.Id) AS users
  , SUM(sel.total_answers) AS total_answers
  , SUM(sel.zero_score_posts) AS zero_score_posts
  , year(sel.last_answer) AS year_last
  , CONVERT(
        decimal(10,2)
      , AVG(
          CONVERT(
              decimal(10,4)
            , sel.zero_score_posts
          ) /
          CONVERT(
              decimal(10,4)
            , sel.total_answers
          )
        ) * 100
    ) AS percentage
FROM
    sel
GROUP BY
    year(sel.last_answer)
ORDER BY
    year(sel.last_answer)
;

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results