Finding or calculating the stats on a tournament

user_19705212 0
Viewed 46

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!

subquery postgresql sql
2 Answers

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.

user_124928900

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

user_138083190
Related