SELECT host_team, 3 as points
FROM matches where host_goals > guest_goals
UNION
SELECT guest_team, 3 as points
FROM matches where host_goals < guest_goals
UNION
SELECT host_team, 1 as points
FROM matches where host_goals = guest_goals
UNION
SELECT guest_team, 1 as points
FROM matches where host_goals = guest_goals
;with results as(
    SELECT host_team as team_id, 3 as points
    FROM matches where host_goals > guest_goals
    UNION
    SELECT guest_team as team_id, 3 as points
    FROM matches where host_goals < guest_goals
    UNION
    SELECT host_team as team_id, 1 as points
    FROM matches where host_goals = guest_goals
    UNION
    SELECT guest_team as team_id, 1 as points
    FROM matches where host_goals = guest_goals
), totals as (
    SELECT team_id, SUM(points) as num_points
    from results
    GROUP BY team_id
)
SELECT team_id, sum(points)
FROM matches,
     LATERAL (
         SELECT host_team AS team_id, CASE WHEN host_goals>guest_goals THEN 3 WHEN host_goals<guest_goals THEN 0 ELSE 1 END AS points
         UNION ALL
         SELECT guest_team, CASE WHEN host_goals>guest_goals THEN 0 WHEN host_goals<guest_goals THEN 3 ELSE 1 END
     ) t
GROUP BY team_id
ORDER BY sum DESC;