1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for operations research and analytics professionals, educators, and students

WITH CTE AS
(
  SELECT
    Name,
    UserId,
    COUNT(*) Qty,
    Points = CASE Name
      WHEN 'Altruist' THEN 5
      WHEN 'Analytical' THEN 5
      WHEN 'Announcer' THEN 5
      WHEN 'Autobiographer' THEN 5
      WHEN 'Benefactor' THEN 5
      WHEN 'Caucus' THEN 5
      WHEN 'Citizen Patrol' THEN 5
      WHEN 'Cleanup' THEN 5
      WHEN 'Commentator' THEN 5
      WHEN 'Critic' THEN 5
      WHEN 'Custodian' THEN 5
      WHEN 'Disciplined' THEN 5
      WHEN 'Editor' THEN 5
      WHEN 'Excavator' THEN 5
      WHEN 'Informed' THEN 5
      WHEN 'Investor' THEN 5
      WHEN 'Mortarboard' THEN 5
      WHEN 'Nice Answer' THEN 5
      WHEN 'Nice Question' THEN 5
      WHEN 'Organizer' THEN 5
      WHEN 'Peer Pressure' THEN 5
      WHEN 'Popular Question' THEN 5
      WHEN 'Precognitive' THEN 5
      WHEN 'Promotor' THEN 5
      WHEN 'Proofreader' THEN 5
      WHEN 'Quorum' THEN 5
      WHEN 'Revival' THEN 5
      WHEN 'Scholar' THEN 5
      WHEN 'Self-Learner' THEN 5
      WHEN 'Student' THEN 5
      WHEN 'Suffrage' THEN 5
      WHEN 'Supporter' THEN 5
      WHEN 'Synonymizer' THEN 5
      WHEN 'Tag Editor' THEN 5
      WHEN 'Talkative' THEN 5
      WHEN 'Teacher' THEN 5
      WHEN 'Tumbleweed' THEN 5
      WHEN 'Vox Populi' THEN 5
      WHEN 'Archaeologist' THEN 10
      WHEN 'Beta' THEN 10
      WHEN 'Booster' THEN 10
      WHEN 'Civic Duty' THEN 10
      WHEN 'Constituent' THEN 10
      WHEN 'Convention' THEN 10
      WHEN 'Deputy' THEN 10
      WHEN 'Enlightened' THEN 10
      WHEN 'Enthusiast' THEN 10
      WHEN 'Epic' THEN 10
      WHEN 'Favorite Question' THEN 10
      WHEN 'Generalist' THEN 10
      WHEN 'Good Answer' THEN 10
      WHEN 'Good Question' THEN 10
      WHEN 'Guru' THEN 10
      WHEN 'Necromancer' THEN 10
      WHEN 'Notable Question' THEN 10
      WHEN 'Outspoken' THEN 10
      WHEN 'Pundit' THEN 10
      WHEN 'Research Assistant' THEN 10
      WHEN 'Reviewer' THEN 10
      WHEN 'Sportsmanship' THEN 10
      WHEN 'Strunk & White' THEN 10
      WHEN 'Taxonomist' THEN 10
      WHEN 'Tenacious' THEN 10
      WHEN 'Yearling' THEN 10
      WHEN 'Copy Editor' THEN 20
      WHEN 'Electorate' THEN 20
      WHEN 'Famous Question' THEN 20
      WHEN 'Fanatic' THEN 20
      WHEN 'Great Answer' THEN 20
      WHEN 'Great Question' THEN 20
      WHEN 'Legendary' THEN 20
      WHEN 'Marshal' THEN 20
      WHEN 'Populist' THEN 20
      WHEN 'Publicist' THEN 20
      WHEN 'Reversal' THEN 20
      WHEN 'Stellar Question' THEN 20
      WHEN 'Steward' THEN 20
      WHEN 'Unsung Hero' THEN 20
      ELSE 0
    END
  FROM Badges
  GROUP BY Name, 
           UserId
),
CTE2 AS
(
  SELECT TOP 100
    UserId,
    SUM(QTY * Points) AS
      Points
  FROM CTE
  GROUP BY UserId
  ORDER BY Points DESC
)
SELECT 
  REPLACE(
    CONVERT(
      VARCHAR,
      CAST(CTE2.Points AS MONEY),
      1
    ),
    '.00',
    ''
  )
  AS Points,
  
  Users.Id AS [User Link],
  
  SUBSTRING(CAST(Users.Reputation / 100 AS VARCHAR(MAX)),
    -1, LEN(Users.Reputation)-1)
  + '.' +
  SUBSTRING(CAST(Users.Reputation / 100 AS VARCHAR(MAX)),
    LEN(Users.Reputation)-2, 1)
  + 'k'
  AS Reputation,
  
  REPLACE(
    CONVERT(
      VARCHAR,
      CAST(Users.UpVotes + Users.DownVotes AS MONEY),
      1
    ),
    '.00',
    ''
  )
  AS Votes,
  
  
  Users.Location,
  Users.WebsiteUrl,
  Users.AboutMe
FROM CTE2
LEFT JOIN Users ON Users.Id = CTE2.UserId
ORDER BY CTE2.Points DESC

fork query

edited aug 31 Franck Dernoncourt

created mar 15 13 anonymous