Goalkeepers' Clean-Sheet Percentage

Medium
465.

Goalkeepers' Clean-Sheet Percentage

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

ColumnType
match_idint
home_clubvarchar
away_clubvarchar
home_goalsint
away_goalsint

Primary key: match_id.

One row per match played.

Table: KeeperAppearance

ColumnType
match_idint
clubvarchar
goalkeepervarchar

Primary key (together): match_id, club.

The goalkeeper who played for club in the match; club is that match's home or away club.

Example

The rows must come back in the order shown.

MatchResult

match_idhome_clubaway_clubhome_goalsaway_goals
1Goa GullsPune Rovers20
2Pune RoversDelhi Falcons00
3Delhi FalconsGoa Gulls13
4Goa GullsDelhi Falcons00
5Pune RoversGoa Gulls21

KeeperAppearance

match_idclubgoalkeeper
1Goa GullsArjun Nair
1Pune RoversKabir Khan
2Pune RoversKabir Khan
2Delhi FalconsVikram Rao
3Delhi FalconsVikram Rao
3Goa GullsArjun Nair
4Goa GullsArjun Nair
4Delhi FalconsDev Mehta
5Pune RoversKabir Khan
5Goa GullsArjun Nair

Output

goalkeeperappearancesclean_sheetsclean_sheet_pct
Arjun Nair4250
Kabir Khan3133.3
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
match_idint
home_clubvarchar
away_clubvarchar
home_goalsint
away_goalsint

Primary key: match_id.

One row per match played.

Table: KeeperAppearance

ColumnType
match_idint
clubvarchar
goalkeepervarchar

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_idhome_clubaway_clubhome_goalsaway_goals
1Goa GullsPune Rovers20
2Pune RoversDelhi Falcons00
3Delhi FalconsGoa Gulls13
4Goa GullsDelhi Falcons00
5Pune RoversGoa Gulls21

KeeperAppearance

match_idclubgoalkeeper
1Goa GullsArjun Nair
1Pune RoversKabir Khan
2Pune RoversKabir Khan
2Delhi FalconsVikram Rao
3Delhi FalconsVikram Rao
3Goa GullsArjun Nair
4Goa GullsArjun Nair
4Delhi FalconsDev Mehta
5Pune RoversKabir Khan
5Goa GullsArjun Nair

Output

goalkeeperappearancesclean_sheetsclean_sheet_pct
Arjun Nair4250
Kabir Khan3133.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.goalkeeper

Another 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 →