Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

version
PostgreSQL 14.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit

4 rows affected

6 rows affected

with norm_matches as (
  select id as match_id, team_1 as team_id, team_1_goals as goals, 
         case
           when team_1_goals > team_2_goals then 'W'
           when team_1_goals = team_2_goals then 'D'
           when team_1_goals < team_2_goals then 'L'
         end as outcome
    from matches
  union all
  select id as match_id, team_2 as team_id, team_2_goals as goals, 
         case
           when team_1_goals > team_2_goals then 'L'
           when team_1_goals = team_2_goals then 'D'
           when team_1_goals < team_2_goals then 'W'
         end as outcome
    from matches
), points (outcome, value) as (
  values ('W', 3), ('D', 1), ('L', 0)
)
select t.name, 
       count(1) as matches,
       count(1) filter (where m.outcome = 'W') as victories,
       count(1) filter (where m.outcome = 'L') as defeats,
       count(1) filter (where m.outcome = 'D') as draws,
       sum(p.value) as score
  from teams t
       join norm_matches m on m.team_id = t.id
       join points p on p.outcome = m.outcome
 group by t.name
 order by t.name
;

name	matches	victories	defeats	draws	score
CEARA	3	2	0	1	7
FLORESTA	3	1	2	0	3
FORTALEZA	3	2	0	1	7
GUARANY DE SOBRAL	3	0	3	0	0