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),
('2020-01-01', 'Bangalore', 'Daredevils', 'CSK', 1, 1),
('2021-01-01', 'Indore', 'Kolkata', 'RCB', 2, 2),
('2021-01-01', 'Mumbai', 'CSK', 'Kolkata',4, 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;
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;
SELECT location, team, total
from (
select location, team,total, rank() over(PARTITION by location order by total desc) as the_rank
from (
select location, team, sum(goals) total
from (
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;
Telegram: DB developers channel