Finding or calculating the stats on a tournament-CodePudding

This problem was bugging me for the last couple of weeks, but the solution I've reached so far doesn't seem good enough to aleviate the doubts I have.

Problem Statement: Build a table (query) that shows the followings columns (team name, number of matches, victories, defeats, draws, and score) the score of each team is calculated knowing that each victory gives 3 points and a draw gives one point, while defeats don't afect at all.

Table Schemas:

Table "teams":

Column name	Type
id	int
name	varchar(50)

Table "matches":

Column name	Type
id	int
team_1	int
team_2	int
team_1_goals	int
team_2_goals	int

Sample Data:

Table "teams":

id	name
1	CEARA
2	FORTALEZA
3	GUARANY DE SOBRAL
4	FLORESTA

Table "matches":

id	team_1	team_2	team_1_goals	team_2_goals
1	4	1	0	4
2	3	2	0	1
3	1	3	3	0
4	3	4	0	1
5	1	2	0	0
6	2	4	2	1

Expected Output:

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

What I've managed to do so far:

SELECT
    t.name, 
    count(m.team_1) filter(WHERE t.id = m.team_1)
          count(m.team_2) filter(WHERE t.id = m.team_2) "matches",
    count(m.team_1) filter(WHERE t.id = m.team_1 AND m.team_1_goals > m.team_2_goals)
          count(m.team_2) filter(WHERE t.id = m.team_2 AND m.team_1_goals < m.team_2_goals) "victories",
    count(m.team_1) filter(WHERE t.id = m.team_1 AND m.team_1_goals < m.team_2_goals)
          count(m.team_2) filter(WHERE t.id = m.team_2 AND m.team_1_goals > m.team_2_goals) "defeats",
    count(m.team_1) filter(WHERE t.id = m.team_1 AND m.team_1_goals = m.team_2_goals)
          count(m.team_2) filter(WHERE t.id = m.team_2 AND m.team_1_goals = m.team_2_goals) "draws",
    ((count(m.team_1) filter(WHERE t.id = m.team_1 AND m.team_1_goals > m.team_2_goals)
          count(m.team_2) filter(WHERE t.id = m.team_2 AND m.team_1_goals < m.team_2_goals))* 3)  
        count(m.team_1) filter(WHERE t.id = m.team_1 AND m.team_1_goals = m.team_2_goals)
          count(m.team_2) filter(WHERE t.id = m.team_2 AND m.team_1_goals = m.team_2_goals) "score"
FROM
    teams t
JOIN matches m ON t.id IN (m.team_1, m.team_2)
GROUP BY t.name
ORDER BY "victories" DESC

which theoretically outputs the correct answer.

I've tried to make it by using fancy things like CASE WHEN or a bigger JOIN but with no good results. What I want to know is if there is a better way to do this query in terms of writing and performance in the server.

Appreciate any help!

CodePudding user response：

One option is to

unify the "matches" fields into "team" and "goals" twice (one for each team), while keeping trace of the wins (1 if team A won on team B, 0 for draws, -1 for lost match)
apply a LEFT JOIN between "teams" table and the just-updated "matches" table
use all needed aggregations in combination of CASE statements, as in the example below (COALESCE will convert any null value into zero).

WITH cte_matches AS (
    SELECT id, 
           team_1                                          AS team, 
           team_1_goals                                    AS goals, 
           CASE WHEN team_1_goals > team_2_goals THEN 1
                WHEN team_1_goals < team_2_goals THEN -1
                ELSE 0 END                                 AS has_won
    FROM matches
    UNION ALL
    SELECT id, 
           team_2                                          AS team, 
           team_2_goals                                    AS goals, 
           CASE WHEN team_2_goals > team_1_goals THEN 1
                WHEN team_2_goals < team_1_goals THEN -1
                ELSE 0 END                                 AS has_won
    FROM matches
)
SELECT t.name, 
       COUNT(t.id)      AS matches,
       COALESCE(SUM(CASE WHEN has_won =  1 THEN 1 END), 0) AS victories,
       COALESCE(SUM(CASE WHEN has_won = -1 THEN 1 END), 0) AS defeats,
       COALESCE(SUM(CASE WHEN has_won =  0 THEN 1 END), 0) AS draws,
       COALESCE(SUM(CASE WHEN has_won =  1 THEN 3
                         WHEN has_won =  0 THEN 1 END), 0) AS score
FROM      teams       t
LEFT JOIN cte_matches m
       ON t.id = m.team
GROUP BY t.name

Check the demo here.

CodePudding user response：

I normalized the layout of the matches table to simplify things, but I assume that the structure of that table was given to you. Please let me know if you have control over the structure of the matches table, and I can show you how to use lead() to determine the win/loss/draw status for each side of a match.

If you represent the point value as a table (or a CTE, common table expression, in this case), you can simplify the aggregations:

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
;

db<>fiddle here