3

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

-- Post Reputation Over Time
-- Calculates the average post score each month over the site's existence.

DECLARE @daysAfterPosting int = 48

DECLARE @postAverages table
(
  PostTypeId tinyint NOT NULL,
  Date datetime NOT NULL,
  AvgPostReputation decimal(9, 2) NOT NULL,

  PRIMARY KEY CLUSTERED(PostTypeId, Date)
)

INSERT INTO @postAverages (PostTypeId, Date, AvgPostReputation)
  SELECT
    PostTypeId,
    Date,
    AVG(PostReputation) AS AvgPostReputation
    FROM
    (
       SELECT
        PostTypeId,
        /* Get year and month components of CreationDate as a datetime */
        DATEADD(MONTH, MONTH(CreationDate) - 1, DATEADD(YEAR, YEAR(CreationDate) - 1900, 0)) AS Date,
        CAST(PostReputation AS decimal(9, 2)) AS PostReputation
        FROM
        (
          /* Calculate post scores within the first @daysAfterPosting days */
          SELECT
            p.PostTypeId,
            p.CreationDate,
            SUM
            (
              CASE p.PostTypeId
                WHEN 1 THEN (CASE pv.VoteTypeId WHEN 2 THEN 5 WHEN 3 THEN -2 END)
                WHEN 2 THEN (CASE pv.VoteTypeId WHEN 2 THEN 10 WHEN 3 THEN -2 END)
              END
            ) AS PostReputation
            FROM Posts p
            INNER JOIN Votes pv ON pv.PostId = p.Id
            WHERE
              (p.PostTypeId IN (1, 2)) AND
              (pv.VoteTypeId IN (2, 3)) AND
              (pv.CreationDate <= DATEADD(DAY, @daysAfterPosting, p.CreationDate))
            GROUP BY
              p.PostTypeId,
              p.CreationDate
        ) a
    ) b
    GROUP BY
      PostTypeId,
      Date

SELECT
  pa1.Date,
  pa1.AvgPostReputation AS AvgQuestionRep,
  pa2.AvgPostReputation AS AvgAnswerRep
  FROM @postAverages pa1
  INNER JOIN @postAverages pa2 ON pa2.Date = pa1.Date
  WHERE
    (pa1.PostTypeId = 1) AND
    (pa2.PostTypeId = 2)
  ORDER BY pa1.Date

fork query

edited nov 29 12

created nov 29 12 anonymous