SQL Difficult Queries

create table icc_world_cup(
Team_1 Varchar(20),
Team_2 Varchar(20),
Winner Varchar(20)
);
INSERT INTO icc_world_cup values('India','SL','India');
INSERT INTO icc_world_cup values('SL','Aus','Aus');
INSERT INTO icc_world_cup values('SA','Eng','Eng');
INSERT INTO icc_world_cup values('Eng','NZ','NZ');
INSERT INTO icc_world_cup values('Aus','India','India');
select pp.Team, Matches_played, ISNULL(no_of_wins, 0) as no_of_wins, (Matches_played - ISNULL(no_of_wins, 0)) as no_of_losses from (
(select Team, count(Team) as Matches_played
from (
select * from (
select Team_1 as Team, Winner from icc_world_cup
union all
select Team_2 as Team, Winner from icc_world_cup
) t
) g
group by Team)
as pp
LEFT JOIN(select Winner as Team, count(Winner) as no_of_wins
from icc_world_cup
group by Winner) as hh
on pp.Team = hh.Team
)
create table players
(player_id int,
group_id int)
insert into players values (15,1);
insert into players values (25,1);
insert into players values (30,1);
insert into players values (45,1);
insert into players values (10,2);
insert into players values (35,2);
insert into players values (50,2);
insert into players values (20,3);
insert into players values (40,3);
create table matches
(
match_id int,
first_player int,
second_player int,
first_score int,
second_score int)
insert into matches values (1,15,45,3,0);
insert into matches values (2,30,25,1,2);
insert into matches values (3,30,15,2,0);
insert into matches values (4,40,20,5,2);
insert into matches values (5,35,50,1,1);
with player_score as (
select player, sum(score) as scores from (
select first_player as player, first_score as score from matches
union all
select second_player as player, second_score as score from matches) a
group by player),
final_score as (
select p.*, s.scores from players p
JOIN
player_score s
on p.player_id = s.player),
final_ranking as (
select *,
rank() over(partition by group_id order by scores desc, player_id) as ranking
from final_score )
select * from final_ranking where ranking = 1

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store