SQL Query for Tournament between various teams-CodePudding

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

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

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

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

CodePudding user response：

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

CodePudding user response：

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