A statistical approach to separating Type A and Type B popular posts.
Q&A about the site for people interested in improving and participating in the patent system
-- 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.
declare @minAge numeric = 30
DECLARE @endDate date
SELECT @endDate = max(CreationDate) from Posts
SELECT top 1000
WHEN 1 THEN 'Q'
WHEN 2 THEN 'A'
tmp.score AS [Score],
Max(tmp.dVotes) AS [Vote Spike],
-- Cast((STDEVP(tmp.dVotes) + 1) / SQRT(SUM(tmp.dVotes)) * 100 AS DECIMAL(20,5))
-- AS [Variance Index],
Cast(STDEVP(tmp.dVotes) + 1 AS DECIMAL(20, 3))
AS [Type A],
Cast(tmp.score / (STDEVP(tmp.dVotes) + 1) AS DECIMAL(20, 3))
AS [Type B],
(STDEVP(tmp.dVotes) + 1 + tmp.score / (STDEVP(tmp.dVotes) + 1))
AS DECIMAL(20, 3)) AS [Balance]
-- 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
DATEDIFF(DAY,p.CreationDate,@endDate) >= 30 and
v.VoteTypeId = 2 and p.PostTypeId is not null
) AS tmp
WHERE tmp.score > 0
COUNT(tmp.dVotes) > 1
ORDER BY [Balance] DESC