Write Complex SQL Queries 2 | Cricket Analytics | Interview questions with Solution | #SQLWednesdays

▶ Evaluate yourself if you can become a successful Business Analyst

▶ In SQL Wednesdays, I will cover 1 problem that has been asked in FAANG Interviews. So, watch these videos to improve your chances to crack the interviews.

SQL query for Creating the table and inserting the records.

Create table cricket_tournament
(Home_Team varchar(255),
Away_Team varchar(255),
Winner varchar(255)
)
insert into cricket_tournament values ('India','Australia','India')
insert into cricket_tournament values ('Sri Lanka','West Indies','West Indies')
insert into cricket_tournament values ('Australia','New Zealand','Australia')
insert into cricket_tournament values ('Pakistan','England','England')
insert into cricket_tournament values ('South Africa','India','India')

💡 SQL TUTORIAL FOR BEGINNERS
========================

[analyticalguy]

Hi Data Enthusiasts, how many of you are able to solve this problem? Do you have any other solution to this problem statement?

[michadobroczynski]

Appreciate your great work! Working on real examples is best method for learning, thanks!

[surajkumarsaw]

I was confused first then I saw that you are applying case with comparison winner column then wrote solution hit.

with cte as (
select home_team,
case when home_team = winner then 1 else 0 end as winner_flag
from cricket_tournament
union all
select away_team,
case when away_team = winner then 1 else 0 end as winner_flag
from cricket_tournament
)
select home_team, count(home_team) as total_matches_played, sum(winner_flag) as matches_won, count(case when winner_flag=0 then 0 end) as Matches_lost
from cte group by home_team order by home_team

[raulfernandezjr]

Thank you for sharing. This was really helpful in understanding How you break down the syntax and the problem with the solution. Looking forward to seeing more practice videos. Also, how do you find practice queries like this one ?