LeetCode Medium 1841 'League Statistics' Interview SQL Question with Detailed Explanation

In this video I solve and explain a medium difficulty leetcode SQL question using MySQL query. This question has been asked in Apple, Facebook, Amazon, Google, Adobe, Microsoft, Adobe interviews or what we popularly call FAANG interviews.
I explain the related concept as well. This question includes points to keep in mind to develop SQL queries.

LeetCode is the best platform to help you enhance your skills, expand your knowledge and prepare for technical interviews.

If you found this helpful, Like and Subscribe to the channel for more content.

#LeetCodeSQL #FAANG #SQLinterviewQuestions

[wendyliu]

Your solution is amazing! Thanks for the help!

[mohd.saifali]

You made it really easy.. initially ques was looking difficult

[anjanijanyavula]

May I know how much time it took for you to solve this problem??

[muddassirnazar]

Easy Solution:with cte as
(select home_team_id as team_name, away_team_id, home_team_goals, away_team_goals
from matches
union all
select away_team_id as team_name, home_team_id, away_team_goals, home_team_goals
from matches )
select t.team_name, count(*) as matches_played,
sum(case when then 3
when then 1
else 0 end) as points,
sum(home_team_goals) as goal_for,
sum(away_team_goals) as goal_against,
as goal_diff
from cte c join teams t on c.team_name=t.team_id
group by t.team_name
order by points desc, goal_diff desc, t.team_name

[andreanlobo]

Tried this to get the solution:

WITH CTE_1 AS (
SELECT
b.team_name as home_team_name,
c.team_name as away_team_name,
a.home_team_goals,
a.away_team_goals,
CASE
WHEN home_team_goals < away_team_goals THEN c.team_name
WHEN home_team_goals > away_team_goals THEN b.team_name
WHEN home_team_goals = away_team_goals THEN 'Draw'
ELSE NULL END as match_result
FROM matches a
INNER JOIN teams b
ON a.home_team_id = b.team_id
INNER JOIN teams c
ON a.away_team_id = c.team_id),

pts AS(
SELECT *,
CASE WHEN match_result = home_team_name THEN 3
WHEN match_result = 'Draw' THEN 1
ELSE 0 END as pts_home_team,
CASE WHEN match_result = away_team_name THEN 3
WHEN match_result = 'Draw' THEN 1
ELSE 0 END as pts_away_team
FROM CTE_1),

CTE_2 AS(
SELECT
home_team_name,
SUM(pts_home_team) as pts_home,
COUNT(home_team_name) as n_home_mts,
SUM(home_team_goals) as goals_for,
SUM(away_team_goals) as goals_against
FROM pts
GROUP BY 1
),

CTE_3 AS(
SELECT
away_team_name,
SUM(pts_away_team) as pts_away,
COUNT(away_team_name) as n_away_mts,
SUM(away_team_goals) as goals_for,
SUM(home_team_goals) as goals_against
FROM pts
GROUP BY 1)

SELECT
home_team_name as team,
n_home_mts + n_away_mts as matches_played,
pts_home + pts_away as points,
CTE_2.goals_for + CTE_3.goals_for as goal_for,
CTE_2.goals_against + CTE_3.goals_against as goal_against,
(CTE_2.goals_for + CTE_3.goals_for) - (CTE_2.goals_against + CTE_3.goals_against) as goal_diff
FROM CTE_2, CTE_3
WHERE home_team_name = away_team_name
ORDER BY goal_diff DESC

Would this be optimal?

[ashishvishwakarma]

This question should be of 'Hard' type