MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


8.0.27


CREATE TABLE `team` (
  `name` varchar(15) PRIMARY KEY NOT NULL
);

CREATE TABLE `season` (
   `name` char(9) PRIMARY KEY NOT NULL,
   `begin` date,
   `end` date
);

CREATE TABLE `game` (
  `id` int PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `Date` date DEFAULT NULL,
  `HomeTeam` varchar(15) NOT NULL,
  `AwayTeam` varchar(15) NOT NULL,
  `FTHG` int DEFAULT NULL,           -- (full time home goal)
  `FTAG` int DEFAULT NULL,           -- (full time away goal)
  `FTR` char(1) DEFAULT NULL,
  `HTHG` int DEFAULT NULL,
  `HTAG` int DEFAULT NULL,
  `HTR` char(1) DEFAULT NULL,
  `H_S` int DEFAULT NULL,
  `A_S` int DEFAULT NULL,
  `HST` int DEFAULT NULL,
  `AST` int DEFAULT NULL,
  `HC` int DEFAULT NULL,
  `AC` int DEFAULT NULL,
  `HF` int DEFAULT NULL,
  `AF` int DEFAULT NULL,
  `HY` int DEFAULT NULL,
  `AY` int DEFAULT NULL,
  `HR` int DEFAULT NULL,
  `AR` int DEFAULT NULL,
  `season` char(9),
  FOREIGN KEY (HomeTeam) REFERENCES team(name),
  FOREIGN KEY (AwayTeam) REFERENCES team(name),
  FOREIGN KEY (season) REFERENCES season(name),
  CHECK (HomeTeam != AwayTeam)
);

CREATE VIEW stats AS
  SELECT season, team
       , sum(TGS) TGS, sum(TGC) TGC, sum(pts) pts
       , RANK() OVER (PARTITION BY season ORDER BY sum(pts) DESC) AS rnk
    FROM (
           SELECT season, HomeTeam team, FTHG TGS, FTAG TGC
                , CASE WHEN FTHG > FTAG THEN 3 WHEN FTHG = FTAG THEN 1 ELSE 0 END pts
             FROM game
            UNION ALL
           SELECT season, AwayTeam team, FTAG TGS, FTHG TGC
                , CASE WHEN FTAG > FTHG THEN 3 WHEN FTAG = FTHG THEN 1 ELSE 0 END pts
             FROM game
         ) games
   GROUP BY season, team
;
SELECT * FROM stats;