SQL Query for Tournament between various teams

Question

I have a table for tournament competition between various teams as shown below and my goal is to write a sql query to get most recent date of match played by each team at each location.

Table Tournament Snapshot:

Match Date  Match_Location    Team_a           Team_b       Team_a_Goals         Team_b_Goals
2020-01-01  Chennai            CSK             RCB          2                    4
2020-01-01  Mumbai             Kolkata         CSK          4                    6
2020-12-31  Bangalore          Daredevils      Kolkata      1                    1
2020-05-03  Kolkata            RCB             CSK          3                    2
2020-06-15  Indore             Kolkata         RCB          1                    0

1. Get most recent date of Match played by each of the teams at each of the locations

Expected Result:

recent_match_date,    recent_location,  team 
2020-05-03            Kolkata           CSK
2020-06-15            Indore            Kolkata
2020-12-31            Bangalore         Daredevils
2020-06-15            Indore            RCB

2. Total number of matches at each location that ended as draw. List all location.

Expected Result:

location          n_matches_draw
Kolkata           0
Indore            0
Bangalore         1
Indore            0

3. Get the team and total goals of the highest scoring team at each location.

My approach for first query is using Union but it is not optimum solution as I am using Union and subquery.

select team, location , max(match_date) from (
select team_a as team, match_location as location, min(match_date) as match_date
from tournament group by team_a, match_location
union 
select team_b as team, match_location as location, max(match_date) as match_date
from tournament group by team_b, match_location
) group by team, match_location

For Second question (total draw count at each location) below is my query:

select location, count(*) as draw_count
from (select *  from tournament where team_a_goals=team_b_goals)
group by location

For third query too I have approach using Union. However I am not clear if my query solves what is asked.

select location, case when team_a_goals > team_b_goals then team_a_goals as goals, team_a as team
union 
select location, case when team_b_goals > team_a_goals then team_b_goals as goals, team_b as team

Answer 1

Query #1:

select date, team, location
from (
        select date, location, team, rank() over (partition by team, location order by date desc) as the_rank
        from (
                select location, date, team_a as team
                from tournament
                union
                select location, date, team_b
                from tournament
             ) as t
     ) as s
where the_rank = 1

Query #2:

select location, count(draw)
from (
        select location, case when team_a_goals = team_b_goals then 1 end as draw
        from tournament
     ) as t
group by location

Query #3:

SELECT location, team, total 
from (  --team with highest total goals would have the first rank, as it is order by total in descending order
        select location, team,total, rank() over(PARTITION by location order by total desc) as the_rank
        from (
                --then sum their goals in each location
                select location, team, sum(goals) total
                from (  --we need to collect all teams from all locations
                        select location, team_a as team, team_a_goals as goals
                        from tournament
                        union all
                        select location, team_b, team_b_goals 
                        from tournament
                     ) as t 
                group by location, team
             ) as t
     ) as t
where the_rank = 1

I don't know the expected result for this query, so I just guessed it, that you need a highest score team in each location.

P.S. There is some mess with data in the table, I populated it with more random data, hope that helps.

View on SQL Lize Online

Answer 2

If your Mysql version support ROW_NUMBER + window function, that will be easier to solve question 1 & 2.

Query #1

SELECT   match_date recent_match_date,
          Match_Location,
          team 
FROM (
  SELECT *,ROW_NUMBER() OVER(PARTITION BY team ORDER BY match_date DESC) rn
  FROM (
    SELECT Team_a team, match_date,Match_Location              
    FROM tournament
    UNION 
    SELECT Team_b  , match_date,Match_Location          
    FROM tournament 
  ) t1 
) t1
WHERE rn =1;

---

Query #2

SELECT t1.match_location,draw_count
FROM (
select match_location, 
       COUNT(CASE WHEN team_a_goals = team_b_goals THEN 1 END) as draw_count
from tournament
group by match_location
 ) t1 RIGHT JOIN  (
  SELECT  match_date recent_match_date,
          Match_Location,
          team 
  FROM (
    SELECT *,ROW_NUMBER() OVER(PARTITION BY team ORDER BY match_date DESC) rn
    FROM (
      SELECT Team_a team, match_date,Match_Location              
      FROM tournament
      UNION 
      SELECT Team_b  , match_date,Match_Location          
      FROM tournament 
    ) t1 
  ) t1
  WHERE rn =1
) t2
ON t1.match_location = t2.match_location;

---

View on DB Fiddle

Answer 3

The first question:

with team_games as (
  select team_a team, location, date
  from tournament
  union all
  select team_b team, location, date
  from tournament
) select team, location, max(date) last_game from  team_games
group by team, location
order by last_game desc;

The second question can be solved by:

select location, sum(team_a_goals=team_b_goals) as draw_count
from tournament
group by location;

MySQL sandbox