0

Please login or register to vote for this query.

(click on this box to dismiss)

A statistical approach to separating Type A and Type B popular posts.

Q&A for professional and amateur woodworkers

-- 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 @minPostAge       numeric = 30
declare @minDaysWithVotes numeric = 4
declare @skewFactor       numeric = 2.0

DECLARE @endDate date
SELECT  @endDate = max(CreationDate) from Posts

SELECT top 1000
    (CASE tmp.posttype
        WHEN 1 THEN 'Q'
        WHEN 2 THEN 'A'
     END),
    tmp.[Post Link],
    tmp.[User Link],
    tmp.[Age],
    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],
        
    Cast(tmp.score / 
        (@skewFactor * STDEVP(tmp.dVotes) + tmp.score / (STDEVP(tmp.dVotes) + 1))
        AS DECIMAL(20, 3)) AS [Balance]
    
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
        DATEDIFF(DAY,p.CreationDate,@endDate) >= @minPostAge and
        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]
HAVING
    COUNT(tmp.dVotes) >= @minDaysWithVotes
ORDER BY [Balance] DESC

fork query

edited apr 20 13 anonymous

created apr 19 13 Mysticial