## Post Reputation Over Time

3

(click on this box to dismiss)

Q&A for people studying math at any level and professionals in related fields

``````-- 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 */
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
GROUP BY
p.PostTypeId,
p.CreationDate
) a
) b
GROUP BY
PostTypeId,
Date

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

### Enter Parameters

Options:
-
Switch sites:
Hold tight while we fetch your results
:records returned in :time ms:cached