SQL Interview Question | 22 |

If you like this video please
like share , subscribe and comment down below,
if you have any suggestion or doubt in this video.
script:
create table cricket_match
(match_id int,team1 Varchar(20),team2 Varchar(20),result Varchar(20));
INSERT INTO cricket_match values(1,'ENG','NZ','NZ');
INSERT INTO cricket_match values(2,'PAK','NED','PAK');
INSERT INTO cricket_match values(3,'AFG','BAN','BAN');
INSERT INTO cricket_match values(4,'SA','SL','SA');
INSERT INTO cricket_match values(5,'AUS','IND','AUS');
INSERT INTO cricket_match values(6,'NZ','NED','NZ');
INSERT INTO cricket_match values(7,'ENG','BAN','ENG');
INSERT INTO cricket_match values(8,'SL','PAK','PAK');
INSERT INTO cricket_match values(9,'AFG','IND','IND');
INSERT INTO cricket_match values(10,'SA','AUS','SA');
INSERT INTO cricket_match values(11,'BAN','NZ','BAN');
INSERT INTO cricket_match values(12,'PAK','IND','IND');
INSERT INTO cricket_match values(13,'SA','IND','DRAW');

[@RaviSankar-ln3ki]

Interesting query.

[@codinglake]

Thanks, I hope you learned something new!

[@SravanJangam]

superb explanation.........

[@codinglake]

Thanks! 🙂

[@GadiWala007]

Very nice 🙂 plz continue provide more videos that are generally asked in interviews

[@codinglake]

Sure, I'll try to make more interview-focused videos.

[@quizee4970]

with cte as
(select team1 as team_name , result as wining_team from cricket_match
union
select team2 as team_name , result as wining_team from cricket_match)
select team_name , count(*),
sum(case when team_name = wining_team then 1 else 0 end )as win,
sum(case when team_name != wining_team then 1 else 0 end ) as loss
from cte
group by team_name
order by team_name

[@moinuddinhassan7791]

just keep going bro

[@codinglake]

Thanks, will do!

[@charankunthipuram2408]

While querying on top of cte sum(flag) alone would work as we have only 1 and 0 as flag values

[@codinglake]

Check again

[@moinuddinhassan7791]

if possible can post pdf of all the videos you have posted

[@codinglake]

Sure, I will try to upload PDFs for future videos.

[@jayaprakashreddyittech]

SELECT team,COUNT(*) AS NOofmatch,SUM(case when team=result then 1 ELSE 0 END) AS won,
SUM(case when team !=result then 1 ELSE 0 END) AS loss
from(
SELECT team1 AS team,result FROM cricket_match
UNION ALL
SELECT team2 as team,result FROM cricket_match) AS k GROUP BY team ORDER BY team ;

[@codinglake]

Good 👍

[@Soul-f3v]

select team_name , count(1) as no_of_matches_played,sum(win_flag) as no_of_matches_won,(count(1) - sum(win_flag)) as no_of_matches_lost from
(
select team1 as team_name,case when team1 = result then 1 else 0 end as win_flag from cricket_match
union all
select team2 as team_name, case when team2 = result then 1 else 0 end as win_flag from cricket_match
)a group by team_name;

[@codinglake]

Nice one 👍