CREATE TABLE tournament (
date date,
location varchar(64),
team_a varchar(64),
team_b varchar(64),
team_a_goals int,
team_b_goals int
);
INSERT INTO tournament VALUES
('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);
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;
select location, sum(team_a_goals=team_b_goals) as draw_count
from tournament
group by location;
Telegram: DB developers channel