A statistical approach to separating Type A and Type B popular posts.
Вопросы и ответы о сайте для программистов
-- Type A Popular Posts: (Variance Index > 50)
-- Type A's are those that have received a lot of attention in a short
-- amount of time due to becoming "hot" at some point in its history.
-- Type A's are often linked on Reddit, Hacker News, SE Multicollider, etc...
-- Type A's are generally "interesting" or "fun" questions.
-- They are upvoted because they are "awesome", "cool", or "funny".
-- Type B Popular Posts: (Variance Index < 20)
-- Type B's are those that slowly and steadily receive traffic over time.
-- Type B's get nearly all their trafffic from a steady source such as:
-- Search Engines, top questions list, or Jon Skeet's profile.
-- The vast majority of Type B's are generally less interesting, but are very
-- useful to developers who come across the same problem in the question.
-- They are usually upvoted because they are "useful".
-- Posts that are both Type A and Type B will likely to have a variance index
-- somewhere between 20 and 50.
-- The "Variance Index" is computed as:
-- index = 100 * (Stddev(upvotes received for each UTC day*) + 1) / sqrt(total upvotes)
-- *Days where no upvotes are received are not counted.
-- *Standard Deviations with fewer than 30 days of data are normalized.
declare @minDataSize numeric = 30
DECLARE @endDate date
SELECT @endDate = max(CreationDate) from Posts;
WHEN 1 THEN 'Q'
WHEN 2 THEN 'A'
-- If more than 30 days of votes, use standard deviation.
WHEN COUNT(tmp.dVotes) >= @minDataSize THEN STDEVP(tmp.dVotes) + 1
-- If less than 30 days of data, use some sort of zero-padded standard deviation.
VARP(tmp.dVotes) * COUNT(tmp.dVotes)
- SQUARE(MAX(tmp.dVotes) - AVG(tmp.dVotes + 0.))
+ SQUARE(MAX(tmp.dVotes) - SUM(tmp.dVotes + 0.) / @minDataSize)
+ SQUARE(SUM(tmp.dVotes + 0.) / @minDataSize) * (@minDataSize - COUNT(tmp.dVotes))
) / @minDataSize
) + 1
-- Subquery forked from:
p.PostTypeId AS posttype,
v.PostId AS [Post Link],
p.OwnerUserId AS [User Link],
v.CreationDate AS [Date],
DATEDIFF(DAY,p.CreationDate,@endDate) AS [Age],
Count(*) AS dVotes,
p.score AS score
FROM Votes v
LEFT JOIN Posts p ON p.Id = v.PostId
v.VoteTypeId = 2 and p.PostTypeId is not null
) AS tmp
WHERE tmp.score > 0
SELECT TOP 1000
type AS [ ],
pid AS [Post Link],
uid AS [User Link],
age AS [Age],
score AS [Score],
vMax AS [Vote Spike],
CAST(vNStd / SQRT(vSum) * 100 AS DECIMAL(20,5)) AS [Variance Index],
CAST(vNStd AS DECIMAL(20, 3)) AS [Type A],
CAST(score / vNStd AS DECIMAL(20, 3)) AS [Type B]
ORDER BY [Score] DESC