1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

WITH BRONZE_BADGES (id, displayname, bronze) AS (
      SELECT u.id,
      u.displayname,
      count(*) AS bronze
    FROM badges b
      INNER JOIN users u ON u.id = b.userid
    WHERE b.class = 3
    GROUP BY b.userid, u.displayname, u.id),
    SILVER_BADGES(id, displayname, silver) AS (
        SELECT u.id,
        u.displayname,
        count(*) AS silver
      FROM badges b
        INNER JOIN users u ON u.id = b.userid
      WHERE b.class = 2
      GROUP BY b.userid, u.displayname, u.id),
    GOLD_BADGES(id, displayname, gold) AS (
        SELECT u.id,
        u.displayname,
        count(*) AS gold
      FROM badges b
        INNER JOIN users u ON u.id = b.userid
      WHERE b.class = 1
      GROUP BY b.userid, u.displayname, u.id)

SELECT TOP 100
  b.displayname,
  COALESCE(g.gold, 0) as gold_badges,
  COALESCE(s.silver, 0) as silver_badges,
  COALESCE(b.bronze, 0) as bronze_badges
FROM BRONZE_BADGES b
  LEFT JOIN SILVER_BADGES s ON b.id = s.id
  LEFT JOIN GOLD_BADGES g ON b.id = g.id
ORDER BY gold DESC, silver DESC, bronze DESC;

fork query

edited nov 2 15 Makoto

created jan 29 15 Olaf Dietsche