Vote Magnets - Posts with the highest passive vote rate.

2

Please login or register to vote for this query.

(click on this box to dismiss)

Posts that attract the most long-term votes.

Stack Overflow

Q&A for professional and enthusiast programmers

-- Vote Magnets: Posts with the highest passive vote rate.
-- Ranks posts by the highest rate of upvotes received in the last 60 days.

-- This is an improved version of:
-- http://data.stackexchange.com/stackoverflow/query/90451/vote-magnets-posts-that-attract-votes-after-60-days

-- Legend:
--    Score        - Current score of the post
--    Spike        - Most upvotes received in a single day in the last 30 days.
--    StdDev       - Standard Deviation of the votes/day from the last 30 days.
--    Votes / Day  - Upvotes per day in the last 30 days.
--    Prev. / Day  - Upvotes per day between 30 - 60 days ago.
--    Prev. StdDev - Standard Deviation of the votes/day between 30 - 60 days ago.

-- Explanations:
--    The "Spike" and "StdDev" columns are to identify anomalies due to linking or viral activity.
--    If a post has large values in "Spike" and "StdDev", then the "Votes / Day" value is not
--    representative of the long-term passive rep rate.
--
--    The last two columns are statistics from 30 - 60 days ago. These can be used to see trends.
--    These can also be used to see what a post "normally gets" in the event of a linking event
--    that skews the results of the current month.
--
--    The output is sorted by the lower rate from the last two 30-day windows. This is to prevent
--    linking events from skewing the order.


declare @minPostAge       numeric = 60
declare @window           numeric = 30
--declare @minDaysWithVotes numeric = 4

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

-- Get statistics for the current window.
WITH currentWindow(type,pid,uid,score,spike,std,rate)
AS (
SELECT
    tmp.posttype,
    tmp.[Post Link],
    tmp.[User Link],
--    tmp.[Age],
    tmp.score AS [Score],
--    COUNT(tmp.dVotes) AS [Days with Votes],
    MAX(tmp.dVotes) AS [Vote Spike],
    Cast(STDEVP(tmp.dVotes) AS DECIMAL(20,3)) AS [StdDev],
    Cast(SUM(tmp.dVotes) / @window AS DECIMAL(20,3)) AS [Votes / Day]
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
        DATEDIFF(DAY,v.CreationDate,@endDate) < @window 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
    
GROUP BY
    tmp.[Post Link],
    tmp.posttype,
    tmp.[User Link],
    tmp.score,
    tmp.[Age]
--HAVING
--    COUNT(tmp.dVotes) >= @minDaysWithVotes
--ORDER BY [Votes / Day] DESC
),

-- Get statistics for the previous window.
previousWindow(type,pid,uid,score,spike,std,rate)
AS (
SELECT
    tmp.posttype,
    tmp.[Post Link],
    tmp.[User Link],
--    tmp.[Age],
    tmp.score AS [Score],
--    COUNT(tmp.dVotes) AS [Days with Votes],
    MAX(tmp.dVotes) AS [Vote Spike],
    Cast(STDEVP(tmp.dVotes) AS DECIMAL(20,3)) AS [StdDev],
    Cast(SUM(tmp.dVotes) / @window AS DECIMAL(20,3)) AS [Votes / Day]
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
        DATEDIFF(DAY,v.CreationDate,@endDate) >= @window     and
        DATEDIFF(DAY,v.CreationDate,@endDate) <  @window * 2 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
    
GROUP BY
    tmp.[Post Link],
    tmp.posttype,
    tmp.[User Link],
    tmp.score,
    tmp.[Age]
--HAVING
--    COUNT(tmp.dVotes) >= @minDaysWithVotes
)

-- Join everything together
SELECT TOP 1000
    (CASE COALESCE(c.type,p.type)
        WHEN 1 THEN 'Q'
        WHEN 2 THEN 'A'
     END),
     COALESCE(c.pid,p.pid) AS [Post Link],
     COALESCE(c.uid,p.uid) AS [User Link],
     COALESCE(c.score,p.score) AS [Score],
     c.spike AS [Spike],
     c.std   AS [StdDev],
     c.rate  AS [Votes / Day],
     p.rate  AS [Prev. / Day],
     p.std   AS [Prev. StdDev]

FROM currentWindow c
  FULL OUTER JOIN
    previousWindow p ON c.pid = p.pid
ORDER BY
    (CASE WHEN IsNull(c.rate, 0) < IsNull(p.rate, 0)
      THEN IsNull(c.rate, 0)
      ELSE IsNull(p.rate, 0)
      END
    )
    DESC
    
    
-- Legend:
--    Score        - Current score of the post
--    Spike        - Most upvotes received in a single day in the last 30 days.
--    StdDev       - Standard Deviation of the votes/day from the last 30 days.
--    Votes / Day  - Upvotes per day in the last 30 days.
--    Prev. / Day  - Upvotes per day between 30 - 60 days ago.
--    Prev. StdDev - Standard Deviation of the votes/day between 30 - 60 days ago.

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results