1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for people looking to bypass life's everyday problems with simple tricks

-- Query By: Ktash
-- 
-- Query for weighted and non-weighted upvote/downvote
-- ratio. A higher reputation means a higher weight on
-- upvotes/downvotes. The theory here is that as users
-- are on the system more, their judgement should be a
-- lot more refined, so they should vote a bit more in
-- both directions and this should therefore represent
-- better the communities overall ratio.
-- 
SELECT 
  c.num_upvotes
  , c.num_downvotes
  , c.num_upvotes / c.num_downvotes AS ratio
  , p.post_count / c.num_downvotes AS downvotes_per_qa
  , c.weighted_up
  , c.weighted_down
  , c.weighted_up / c.weighted_down AS weight_ratio
FROM (
  SELECT
    SUM(upvotes) AS num_upvotes
    , SUM(downvotes) AS num_downvotes
    -- The higher the rep, the greater the "weight" of your votes
    , SUM(upvotes * (reputation / 200)) AS weighted_up
    , SUM(downvotes * (reputation / 200)) AS weighted_down
  FROM Users
  ) AS c
LEFT JOIN (
  SELECT
    COUNT(1) AS post_count
  FROM Posts
  WHERE 
    PostTypeId = 1
    OR PostTypeId = 2
  ) p
  ON 1=1

fork query

edited apr 18 12 Ktash

created apr 18 12 anonymous