0

Please login or register to vote for this query.

(click on this box to dismiss)

Or: How many views needed for each rep point? Ignores the fact that question view count is an over estimate of answer view count. Minimum 250 answers to qualify. View count adjusted to account for the fact that older answers have more views. Needs work: possibly amortize the answer scores? Askers may be over-ranked.

Q&A for anime and manga fans

-- Users who get the most rep for the fewest views of their answers
-- Or: How many views needed for each rep point?
-- Ignores the fact that question view count is an over estimate of answer view count.
-- Minimum 250 answers to qualify.
-- View count adjusted to account for the fact that older answers have more views.

SELECT 
      CONVERT( DATETIME, CONVERT( VARCHAR(6), p.CreationDate, 112 ) + '01' ) AS Month
    , CONVERT( FLOAT, AVG( p.ViewCount ) ) AS ViewWeight
    , IDENTITY( INT ) AS Id
INTO
    #ViewCurve
FROM
    Posts p
WHERE
    p.PostTypeId = 1
AND p.CreationDate BETWEEN '1 Aug 2008' AND ' 1 Mar 2011'
GROUP BY
    CONVERT( VARCHAR(6), p.CreationDate, 112 )

DECLARE @MinScore FLOAT

SELECT
    @MinScore = MIN( ViewWeight )
FROM
    #ViewCurve

UPDATE
    #ViewCurve
SET
    ViewWeight = @MinScore / ViewWeight

SELECT
    TOP 500
      a.OwnerUserId AS [User Link]
    , CAST( SUM( q.ViewCount * vc.ViewWeight ) AS FLOAT )
      / SUM( a.Score ) AS 'Adjusted views/net votes'
    , CAST( SUM( q.ViewCount * vc.ViewWeight ) AS FLOAT )
      / u.Reputation AS 'Adj. views/rep'
    , u.Reputation
    , SUM( a.Score ) AS 'Net votes for ans.'
    , SUM( q.ViewCount ) AS 'Est. ans. views'
    , SUM( q.ViewCount * vc.ViewWeight ) AS 'Adj. est. ans. views'

FROM
    Posts a
JOIN
    Posts q
ON  q.Id = a.ParentId
JOIN
    Users u
ON  u.Id = a.OwnerUserId
JOIN
    #ViewCurve vc
ON  vc.Month = CONVERT( DATETIME, CONVERT( VARCHAR(6), a.CreationDate, 112 ) + '01' )

GROUP BY
      a.OwnerUserId
    , u.Reputation
HAVING
    COUNT(*) > 250
ORDER BY
    3

fork query

created may 5 11 martin clayton