Goalkeepers' Clean-Sheet Percentage — SQL Joins Problem
A goalkeeper keeps a clean sheet when the opposing team scores no goals in a match they played. KeeperAppearance says who kept goal for each side.
- Difficulty: Medium
- Topics: Joins, Conditional Logic
- Dialect: MySQL
- Problem: #465
Problem statement
A goalkeeper keeps a clean sheet when the opposing team scores no goals in a match they played. KeeperAppearance says who kept goal for each side.
For every goalkeeper with at least 3 appearances, return the columns goalkeeper, appearances, clean_sheets and clean_sheet_pct = 100 * clean_sheets / appearances, rounded to 1 decimal place. Order the rows by clean_sheet_pct descending, then by goalkeeper.
Tables
Table: MatchResult
| Column | Type |
|---|---|
| match_id | int |
| home_club | varchar |
| away_club | varchar |
| home_goals | int |
| away_goals | int |
Primary key: match_id.
One row per match played.
Table: KeeperAppearance
| Column | Type |
|---|---|
| match_id | int |
| club | varchar |
| goalkeeper | varchar |
Primary key: match_id, club.
The goalkeeper who played for club in the match; club is that match's home or away club.
Examples
Example 1
MatchResult
| match_id | home_club | away_club | home_goals | away_goals |
|---|---|---|---|---|
| 1 | Goa Gulls | Pune Rovers | 2 | 0 |
| 2 | Pune Rovers | Delhi Falcons | 0 | 0 |
| 3 | Delhi Falcons | Goa Gulls | 1 | 3 |
| 4 | Goa Gulls | Delhi Falcons | 0 | 0 |
| 5 | Pune Rovers | Goa Gulls | 2 | 1 |
KeeperAppearance
| match_id | club | goalkeeper |
|---|---|---|
| 1 | Goa Gulls | Arjun Nair |
| 1 | Pune Rovers | Kabir Khan |
| 2 | Pune Rovers | Kabir Khan |
| 2 | Delhi Falcons | Vikram Rao |
| 3 | Delhi Falcons | Vikram Rao |
| 3 | Goa Gulls | Arjun Nair |
| 4 | Goa Gulls | Arjun Nair |
| 4 | Delhi Falcons | Dev Mehta |
| 5 | Pune Rovers | Kabir Khan |
| 5 | Goa Gulls | Arjun Nair |
Output
| goalkeeper | appearances | clean_sheets | clean_sheet_pct |
|---|---|---|---|
| Arjun Nair | 4 | 2 | 50 |
| Kabir Khan | 3 | 1 | 33.3 |
How to solve Goalkeepers' Clean-Sheet Percentage
Each appearance row says which club the keeper played for; joined to MatchResult, the question "did the opponent score?" depends on which side that club was. For the home club the opponent's goals are away_goals, for the away club home_goals. Written as IF(club = home_club, away_goals, home_goals) it is the goals conceded, and a clean sheet is when that is 0.
Grouping by goalkeeper, COUNT(*) is appearances and a conditional sum counts the clean sheets. A goalless draw (match 2, match 4) is a clean sheet for both keepers — the per-appearance view handles that naturally because each keeper has their own row. The minimum of three appearances is a condition on the group, so it goes in HAVING. The percentage is rounded only for display, and appearances is never 0 for a listed keeper.
The CTE alternative computes goals conceded once and aggregates it; the solution writes the condition inline. Both are one join and one grouping.
Reference solution (MySQL)
SELECT k.goalkeeper,
COUNT(*) AS appearances,
SUM(CASE WHEN (k.club = r.home_club AND r.away_goals = 0)
OR (k.club = r.away_club AND r.home_goals = 0) THEN 1 ELSE 0 END) AS clean_sheets,
ROUND(100 * SUM(CASE WHEN (k.club = r.home_club AND r.away_goals = 0)
OR (k.club = r.away_club AND r.home_goals = 0) THEN 1 ELSE 0 END) / COUNT(*), 1) AS clean_sheet_pct
FROM KeeperAppearance k
JOIN MatchResult r ON r.match_id = k.match_id
GROUP BY k.goalkeeper
HAVING COUNT(*) >= 3
ORDER BY clean_sheet_pct DESC, k.goalkeeperAnother way
WITH conceded AS (
SELECT k.goalkeeper, IF(k.club = r.home_club, r.away_goals, r.home_goals) AS against
FROM KeeperAppearance k JOIN MatchResult r ON r.match_id = k.match_id
)
SELECT goalkeeper, COUNT(*) AS appearances, SUM(IF(against = 0, 1, 0)) AS clean_sheets,
ROUND(100 * SUM(IF(against = 0, 1, 0)) / COUNT(*), 1) AS clean_sheet_pct
FROM conceded GROUP BY goalkeeper HAVING COUNT(*) >= 3
ORDER BY clean_sheet_pct DESC, goalkeeper← Wickets Credited to Each Bowler From the Dismissal Text · Longest Winning Streak of Each Football Club →