Users Stats - questions/answers scores votes, accepted, days, ratios

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for programmers

WITH sq1 AS (
  SELECT
      u.Id
    , u.Reputation
    , u.CreationDate
    , u.DisplayName
    , u.UpVotes
    , u.userDays
    , u.DownVotes
    , u.Questions
    , CASE
        WHEN u.userDays = 0 THEN NULL
        ELSE
          CONVERT(
              decimal(10,2)
            ,   CONVERT(decimal(10,2),u.Questions)
              / CONVERT(decimal(10,2),u.userDays)
          )
      END QperDay
    , p.QUp
    , p.QDown
    , (p.QUp * 5 - p.QDown * 2) AS QRep
    , CASE
        WHEN u.Questions = 0 THEN NULL
        ELSE
          CONVERT(
              decimal(10,2)
            ,   CONVERT(decimal(10,2),(p.QUp * 5 - p.QDown * 2))
              / CONVERT(decimal(10,2),u.Questions)
          )
      END QAvgScore
    , u.Answers
    , CASE
        WHEN u.userDays = 0 THEN NULL
        ELSE
          CONVERT(
              decimal(10,2)
            ,   CONVERT(decimal(10,2),u.Answers)
              / CONVERT(decimal(10,2),u.userDays)
          )
      END AperDay
    , p.AUp
    , p.ADown
    , (p.AUp * 10 - p.ADown * 2) AS ARep
    , p.AAccept
    , (p.AAccept * 15) AS AccRep
    , CASE
        WHEN u.Answers = 0 THEN NULL
        ELSE
          CONVERT(
              decimal(10,2)
            ,   CONVERT(decimal(10,4),p.AAccept)
              / CONVERT(decimal(10,4),u.Answers)
          )
      END AAcceptRate
    , CASE
        WHEN u.Answers = 0 THEN NULL
        ELSE
          CONVERT(
              decimal(10,2)
            ,   CONVERT(decimal(10,2),(p.AUp * 10 - p.ADown * 2 + p.AAccept * 15))
              / CONVERT(decimal(10,2),u.Answers)
          )
      END AAvgScore
  FROM
    ( SELECT
          p.OwnerUserId
        , SUM(
            CASE
              WHEN v.VoteTypeId = 2 AND p.PostTypeId = 1 THEN 1
              ELSE 0
            END
          ) AS QUp
        , SUM(
            CASE
              WHEN v.VoteTypeId = 3 AND p.PostTypeId = 1 THEN 1
              ELSE 0
            END
          ) AS Qdown
        , SUM(
            CASE
              WHEN v.VoteTypeId = 2 AND p.PostTypeId = 2 THEN 1
              ELSE 0
            END
          ) AS AUp
        , SUM(
            CASE
              WHEN v.VoteTypeId = 3 AND p.PostTypeId = 2 THEN 1
              ELSE 0
            END
          ) AS Adown
        , SUM(
            CASE
              WHEN v.VoteTypeId = 1 AND p.PostTypeId = 2 THEN 1
              ELSE 0
            END
          ) AS AAccept
      FROM
          Posts p
      INNER JOIN
          Votes v
      ON
          v.PostId = p.Id
      WHERE
          p.PostTypeId IN (1,2)
      AND v.VoteTypeId IN (1,2,3)
      GROUP BY
          p.OwnerUserId
    ) p
  INNER JOIN
    ( SELECT
          u.Id
        , u.Reputation
        , u.CreationDate
        , u.DisplayName
        , u.UpVotes
        , u.DownVotes
        , DATEDIFF(day,u.CreationDate,GETDATE()) AS userDays
        , ISNULL(SUM(CASE WHEN p.PostTypeId = 1 THEN 1 ELSE 0 END),0) AS Questions
        , ISNULL(SUM(CASE WHEN p.PostTypeId = 2 THEN 1 ELSE 0 END),0) AS Answers
      FROM
          Users u
      INNER JOIN
          Posts p
      ON
          p.OwnerUserId = u.Id
      WHERE
          p.PostTypeId IN (1,2)
      GROUP BY
          u.Id
        , u.Reputation
        , u.CreationDate
        , u.DisplayName
        , u.UpVotes
        , u.DownVotes
    ) u
  ON
      u.Id = p.OwnerUserId
  WHERE
      u.Reputation >= 0
)
SELECT
    *
FROM
    sq1
ORDER BY
--    AAvgScore DESC
--    Reputation DESC
    QperDay + AperDay DESC

Enter Parameters

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