How to sum values when joining tables?

Question

<hyperbole>Whoever answers this question can claim credit for solving the world's most challenging SQL query, according to yours truly.</hyperbole>

Working with 3 tables: users, badges, awards.

Relationships: user has many awards; award belongs to user; badge has many awards; award belongs to badge. So badge_id and user_id are foreign keys in the awards table.

The business logic at work here is that every time a user wins a badge, he/she receives it as an award. A user can be awarded the same badge multiple times. Each badge is assigned a designated point value (point_value is a field in the badges table). For example, BadgeA can be worth 500 Points, BadgeB 1000 Points, and so on. As further example, let's say UserX won BadgeA 10 times and BadgeB 5 times. BadgeA being worth 500 Points, and BadgeB being worth 1000 Points, UserX has accumulated a total of 10,000 Points ((10 x 500) + (5 x 1000)).

The end game here is to return a list of top 50 users who have accumulated the most badge points.

Can you do it?

Answer 1

My sample tables are:

user:

+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    |       |
| name  | varchar(200) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

badge:

+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
| score | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+

award:

+----------+---------+------+-----+---------+-------+
| Field    | Type    | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| id       | int(11) | YES  |     | NULL    |       |
| user_id  | int(11) | YES  |     | NULL    |       |
| badge_id | int(11) | YES  |     | NULL    |       |
+----------+---------+------+-----+---------+-------+

Thus the query is:

SELECT user.name, SUM(score)
  FROM badge JOIN award ON badge.id = award.badge_id
       JOIN user ON user.id = award.user_id
 GROUP BY user.name
 ORDER BY 2
 LIMIT 50

Answer 2

No, that's not the worlds most challenging query. Something simple like this should do it:

select u.id, u.name, sum(b.points) as Points
from users u
inner join awards a on a.user_id = u.id
inner join badges b on b.id = a.badge_id
group by u.id, u.name
order by 2 desc
limit 50