0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for engineers, producers, editors, and enthusiasts spanning the fields of video, and media creation

-- 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 
            (SELECT * FROM Posts WHERE OwnerUserID=##UserId##)
            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
  Date, AvgPostReputation
  FROM @postAverages WHERE PostTypeId=2
    ORDER BY Date

fork query

edited dec 4 12

created dec 4 12 anonymous