How do I output rating of teams?

Question

I have 2 tables: team(team_id, name), performance(team_id, stage, place, date). Teams should earn points: for 1st place - 10 points, for 2nd 5, for 3rd - 3 points and 1 point for 4-7 places. I need to output the rating of teams. I think it should be like:

SELECT team.name, CASE place
WHEN 1 points + 10
WHEN 2 points + 5
...

Expected reasult:

|---------------------|------------------|
|      team.name      |     Points       |
|---------------------|------------------|
|          Rockers    |         34       |
|---------------------|------------------|
|          Batmans    |         23       |
|---------------------|------------------|
|          ...        |         ...      | 
|---------------------|------------------|

Answer 1

First aggregate inside the table performance to calculate the total points of each team with conditional aggregation and then join the table team to the results and rank the teams with RANK() or maybe DENSE_RANK() analytical functions:

select t.team_id, t.name, 
       coalesce(p.points, 0) points,
       rank() over (order by coalesce(p.points, 0) desc) rnk
from team t left join (
  select team_id,
    sum(
      case
        when place = 1 then 10
        when place = 2 then 5
        when place = 3 then 3
        when place <= 7 then 1
        else 0
      end
    ) points
  from performance 
  group by team_id
) p on p.team_id = t.team_id