A statistical approach to separating Type A and Type B popular posts.
Q&A for those who study, teach, research and apply economics and econometrics
-- 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; WITH rawData(type,pid,uid,age,score,vMax,vSum,vNStd) AS( SELECT (CASE tmp.posttype WHEN 1 THEN 'Q' WHEN 2 THEN 'A' END), tmp.[Post Link], tmp.[User Link], tmp.[Age], tmp.score, MAX(tmp.dVotes), SUM(tmp.dVotes), CASE -- 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. ELSE SQRT( ( 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 END FROM -- Subquery forked from: -- http://data.stackexchange.com/stackoverflow/query/108188/upvotes-per-post-and-per-day (SELECT 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 WHERE v.VoteTypeId = 2 and p.PostTypeId is not null GROUP BY v.PostId, v.CreationDate, p.CreationDate, p.OwnerUserId, p.PostTypeId, p.Score ) AS tmp WHERE tmp.score > 0 GROUP BY tmp.[Post Link], tmp.posttype, tmp.[User Link], tmp.score, tmp.[Age] ) 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] FROM rawData ORDER BY [Score] DESC