-- ## 40: Popular Wiki pages ## --

-- Based on up/down votes

SELECT w.name AS id, sum(v.vote) as votes,
    (CASE WHEN sum(v.vote) > 10 THEN 1
          WHEN sum(v.vote) > 5  THEN 2
          WHEN sum(v.vote) < -5 THEN 5
          WHEN sum(v.vote) < -1 THEN 4
                                ELSE 3
          END) as __color__,
     'wiki' as _realm
  FROM wiki w, votes v
  WHERE w.version = 1 AND v.realm = 'wiki' AND v.resource_id = w.name
GROUP BY w.name
HAVING (sum(v.vote) > 1 OR sum(v.vote) < 1) AND NOT sum(v.vote) = 0
 ORDER BY votes DESC, w.name ASC