SQL Difficult Queries

Hi!

Poor SQL knowledge is definitely a deal breaker when it comes to interview. Following are few difficult queries to practice for your interview!

Q1: We have a list of cricket matches between different teams. We want to create an output where we get a summary of matches_playes, no_of_wins and loses for each team.

Code to create input file:

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');

Solution 1:

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
)

Q2: Write SQL query to find winner in each group. In case of a tie, the player with lowest player_id wins.

Input tables — Matches and Players.

Code to create input tables:

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);

Solution:

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