SQL Basics: Simple table totaling.

For this challenge you need to create a simple query to display each unique clan with their total points and ranked by their total points.

people table schema

• name
• points
• clan

You should then return a table that resembles below

select on

• rank
• clan
• total_points
• total_people

The query must rank each clan by their total_points, you must return each unqiue clan and if there is no clan name (i.e. it's an empty string) you must replace it with [no clan specified], you must sum the total_points for each clan and the total_people within that clan.

 

 

 

Solution:

SELECT
  RANK () OVER (ORDER BY SUM(points) DESC),
  CASE WHEN clan = '' THEN '[no clan specified]' ELSE clan END,
  SUM(points) as total_points,
  COUNT(name) as total_people
FROM 
  people
GROUP BY clan

 

 

Result:

rank	clan	total_points	total_people
1	[no clan specified]	2398178	44
2	none	387976	1
3	codewars	344611	3
4	中国 长垣	284525	1
5	freecodecamp	258436	1