⭐️ (Medium) 1212 Team Scores in Football Tournament

https://leetcode.com/problems/team-scores-in-football-tournament

Last review: May 6, 2026 Next review: A great question to get a deep understanding of joins. Practice again.

Union All

select
    team_id,
    team_name,
    sum(num_points) as num_points
from (
    -- win as a host
    select
        t.*,
        count(m.*) * 3 as num_points
    from Teams t
    left join Matches m
        on t.team_id = m.host_team
        and m.host_goals > m.guest_goals
    group by t.team_id, t.team_name
    union all
    -- win as a guest
    select
        t.*,
        count(m.*) * 3 as num_points
    from Teams t
    left join Matches m
        on t.team_id = m.guest_team
        and m.guest_goals > m.host_goals
    group by t.team_id, t.team_name
    union all
    -- draw as a host
    select
        t.*,
        count(m.*) as num_points
    from Teams t
    left join Matches m
        on t.team_id = m.host_team
        and m.guest_goals = m.host_goals
    group by t.team_id, t.team_name
    union all
    -- draw as a guest
    select
        t.*,
        count(m.*) as num_points
    from Teams t
    left join Matches m
        on t.team_id = m.guest_team
        and m.guest_goals = m.host_goals
    group by t.team_id, t.team_name
)
group by team_id, team_name
order by num_points desc, team_id

Cross Join + Case When ⭐️

After Cross Join and Aggregation, every team has all the information they can pick. Then use Case When to pick the right ones.

select
    team_id,
    team_name,
    sum(case 
        when t.team_id = m.host_team and m.host_goals > m.guest_goals then 3
        when t.team_id = m.host_team and m.host_goals = m.guest_goals then 1
        when t.team_id = m.guest_team and m.guest_goals > m.host_goals then 3
        when t.team_id = m.guest_team and m.guest_goals = m.host_goals then 1
    else 0 end) as num_points
from teams t
cross join matches m
group by t.team_id, t.team_name
order by num_points desc, t.team_id

(Easy) 584. Find Customer Referee

https://leetcode.com/problems/find-customer-referee

Last review: Jan 08, 2026 Next review: —

In SQL, a comparison doesn’t always return true or false. It can return UNKNOWN.

NULL means “I don’t know the value”, not “no value”.

• referee_id = 1

  → 1 != 2 → TRUE → row kept

• referee_id = 2

  → 2 != 2 → FALSE → row dropped

• referee_id = NULL

  → NULL != 2 → UNKNOWN → row dropped

WHERE only keeps rows where the condition is TRUE.

FALSE and UNKNOWN are both filtered out.

SELECT name FROM Customer WHERE referee_id != 2;

Similar Questions: