Data Dictionary

Everything you need to know about the data behind KickStat. All tables live in the understat_reporting schema.

Source: Understat (understat.com) — Shot-level data from 6 European leagues: EPL (Premier League), La_Liga, Bundesliga, Serie_A, Ligue_1, and RFPL.

One row per shot

`understat_reporting.shots`

The core granularity of the dataset — every shot from every match across the top 6 European leagues.

Column	Type	Description
shot_id	int	Unique identifier for the shot.
match_id	int	Match this shot belongs to — join to match_details for match context.
player_id	int	Understat ID of the player who took the shot.
player	text	Name of the player who took the shot.
h_a	text	Whether the shooting player's team was home (h) or away (a).
minute	int	Minute of the match when the shot was taken (0–90+).
result	text	Outcome of the shot: Goal, SavedShot, BlockedShot, MissedShots, ShotOnPost, or OwnGoal.
x	float	Horizontal position of the shot on the pitch (0.0 = own goal line, 1.0 = opponent goal line).
y	float	Vertical position of the shot on the pitch (0.0 = left touchline, 1.0 = right touchline).
xg	float	Expected Goals — the probability (0.0–1.0) that this shot results in a goal, based on historical shot data from similar positions and situations. A header from 6 yards out might be 0.7 xG; a long-range volley might be 0.03.
situation	text	Raw situation type from Understat: OpenPlay, FromCorner, SetPiece, DirectFreekick, Penalty.
shot_type	text	Body part used: RightFoot, LeftFoot, Head.
last_action	text	The action immediately before the shot (e.g., Pass, Cross, Dribble, Rebound, BallRecovery).
player_assisted	text	Name of the player who provided the assist or key pass (null if unassisted).
shot_for	text	Name of the team credited with the shot (accounts for own goals).
shot_against	text	Name of the defending team.
season	text	Season year as a string (e.g., "2024", "2025") — not an integer.
date	date	Date the match was played.
play_status	text	Normalized play type: open_play, set_piece, or penalty. Simplifies the raw situation field into three categories.
distance_to_goal_meters	float	Straight-line distance from the shot location to the center of the goal, in meters. Calculated from x/y coordinates using real pitch dimensions (105m x 68m).
shot_for_team_id	int	Team ID of the team credited with the shot.
shot_against_team_id	int	Team ID of the defending team.
assisting_player_id	int	Player ID of the assisting player (null if unassisted).

Common joins

Add league and team names to shots

FROM understat_reporting.shots s
JOIN understat_reporting.match_details m ON s.match_id = m.match_id
WHERE m.league = 'EPL' AND m.season = '2025'

Example queries

Top 10 players by total xG this season

SELECT player, COUNT(*) as shots, ROUND(SUM(xg)::numeric, 2) as total_xg
FROM understat_reporting.shots s
JOIN understat_reporting.match_details m ON s.match_id = m.match_id
WHERE m.league = 'EPL' AND m.season = '2025'
GROUP BY player
ORDER BY total_xg DESC
LIMIT 10

Shot conversion rate by play type

SELECT play_status,
       COUNT(*) as shots,
       COUNT(CASE WHEN result = 'Goal' THEN 1 END) as goals,
       ROUND(COUNT(CASE WHEN result = 'Goal' THEN 1 END)::numeric / COUNT(*)::numeric * 100, 1) as conversion_pct
FROM understat_reporting.shots s
JOIN understat_reporting.match_details m ON s.match_id = m.match_id
WHERE m.league = 'EPL' AND m.season = '2025'
GROUP BY play_status
ORDER BY conversion_pct DESC

One row per match

`understat_reporting.match_details`

Match-level metadata, scores, expected goals, and goal breakdowns by play type.

Column	Type	Description
match_id	int	Unique match identifier.
league	text	League abbreviation as stored in the database: EPL (Premier League), La_Liga, Bundesliga, Serie_A, Ligue_1, or RFPL (Russian Premier League). Use the abbreviation in WHERE clauses — e.g., WHERE league = 'EPL'.
season	text	Season year as a string (e.g., "2025").
datetime	timestamp	Date and kickoff time of the match.
forecast_home_win	float	Pre-match probability of a home win (from Understat's model).
forecast_away_win	float	Pre-match probability of an away win.
home_team_id	int	Team ID of the home team.
home_team	text	Full name of the home team.
home_abbreviation	text	Short name / abbreviation of the home team (e.g., ARS for Arsenal).
away_team_id	int	Team ID of the away team.
away_team	text	Full name of the away team.
away_abbreviation	text	Short name / abbreviation of the away team.
is_result	bool	Whether the match has been played and has a final result. false for scheduled future fixtures.
home_goals	int	Goals scored by the home team.
home_team_open_play_goals	int	Home goals scored from open play.
home_team_set_piece_goals	int	Home goals from set pieces (corners, free kicks).
home_team_penalty_goals	int	Home goals from penalties.
home_xg	float	Total expected goals for the home team across all shots in the match.
away_goals	int	Goals scored by the away team.
away_team_open_play_goals	int	Away goals scored from open play.
away_team_set_piece_goals	int	Away goals from set pieces.
away_team_penalty_goals	int	Away goals from penalties.
away_xg	float	Total expected goals for the away team.

Example queries

Matches where the underdog won (lower xG team won)

SELECT home_team, away_team, home_goals, away_goals,
       ROUND(home_xg::numeric, 2) as home_xg, ROUND(away_xg::numeric, 2) as away_xg
FROM understat_reporting.match_details
WHERE league = 'EPL' AND season = '2025' AND is_result = true
  AND ((home_goals > away_goals AND home_xg < away_xg)
    OR (away_goals > home_goals AND away_xg < home_xg))
ORDER BY datetime DESC

One row per team / season / league

`understat_reporting.standings`

League table standings calculated from match results.

Column	Type	Description
league	text	League abbreviation: EPL, La_Liga, Bundesliga, Serie_A, Ligue_1, or RFPL.
season	text	Season year.
team_id	int	Team identifier.
title	text	Full team name.
short_title	text	Team abbreviation.
games_played	int	Number of matches played.
points	int	Total league points (3 for a win, 1 for a draw, 0 for a loss).
goals_for	int	Total goals scored.
goals_against	int	Total goals conceded.
goal_differential	int	Goals scored minus goals conceded — a key tiebreaker in most leagues.
place	int	League position (1 = top of the table). Ranked by points, then goal differential.

Example queries

Current Premier League table

SELECT place, title, games_played, points, goals_for, goals_against, goal_differential
FROM understat_reporting.standings
WHERE league = 'EPL' AND season = '2025'
ORDER BY place

One row per team / season / league

`understat_reporting.team_season_xg_summary`

Aggregated expected goals metrics broken down by play type — useful for understanding how a team generates its attacking output.

Column	Type	Description
season	text	Season year.
league	text	League abbreviation: EPL, La_Liga, Bundesliga, Serie_A, Ligue_1, or RFPL.
team_id	int	Team identifier.
title	text	Full team name.
short_title	text	Team abbreviation.
place	int	League position from standings.
games_played	int	Matches played.
points	int	League points.
shots	int	Total shots taken.
total_xg	float	Total expected goals across all shots.
open_play_xg	float	xG from open play situations only.
open_play_xg_pct	float	Percentage of total xG that came from open play (0.0–1.0).
set_piece_xg	float	xG from set pieces (corners, free kicks).
set_piece_xg_pct	float	Percentage of total xG from set pieces.
penalty_xg	float	xG from penalties.
penalty_xg_pct	float	Percentage of total xG from penalties.
total_non_penalty_xg	float	Total xG excluding penalties — often called npxG. A cleaner measure of a team's shot quality since penalties are high-xG events that don't reflect build-up play.
non_penalty_open_play_xg_pct	float	Share of non-penalty xG from open play.
non_penalty_set_piece_xg_pct	float	Share of non-penalty xG from set pieces.

Example queries

Teams most reliant on set pieces for xG (non-penalty)

SELECT title, ROUND(total_non_penalty_xg::numeric, 1) as npxg,
       ROUND(non_penalty_set_piece_xg_pct::numeric * 100, 1) as set_piece_pct
FROM understat_reporting.team_season_xg_summary
WHERE league = 'EPL' AND season = '2025'
ORDER BY non_penalty_set_piece_xg_pct DESC

One row per player per match

`understat_reporting.player_match_stats`

The most detailed table — individual player performance, shot breakdowns by play type, assist metrics, and on-field impact (team performance with vs. without the player).

Identity & context

Column	Type	Description
player_id	int	Player identifier.
player	text	Player name.
team_id	int	Team identifier.
team_name	text	Full team name.
team_short_name	text	Team abbreviation.
match_id	int	Match identifier.
league	text	League name.
season	text	Season year.
roster_entry_id	int	Unique ID for this player's roster entry in this match.
h_a	text	Home (h) or away (a).
position	text	Player's position in this match (e.g., FW, MC, DC, Sub).
appearance_type	text	How the player featured: start, sub (came off the bench), unused (on the bench but didn't play), absent (not in the squad).

Match context

Column	Type	Description
datetime	timestamp	Match date and time.
is_result	bool	Match completed.
home_team	text	Home team name.
away_team	text	Away team name.
opponent	text	Name of the opposing team.
opponent_id	int	Opposing team ID.
venue	text	home or away.
team_goals_for	int	Goals scored by the player's team.
team_goals_against	int	Goals conceded by the player's team.
team_xg_for	float	Team's total xG in this match.
team_xg_against	float	Opponent's total xG in this match.
team_open_play_goals	int	Team's open play goals.
team_set_piece_goals	int	Team's set piece goals.
team_penalty_goals	int	Team's penalty goals.
result	text	Match result from the player's team perspective: W, D, or L.
points_earned	int	League points earned: 3 (win), 1 (draw), 0 (loss).
opponent_league_position	int	Opponent's league position — useful for filtering by opponent strength.
opponent_tier	text	Opponent strength bucket: top_6, mid_table, or bottom_6.

Base performance

Column	Type	Description
minutes_played	int	Minutes on the field (0 if absent or unused).
goals	int	Goals scored.
own_goals	int	Own goals.
shots	int	Total shots taken.
xg	float	Total expected goals from the player's shots.
key_passes	int	Passes that directly led to a shot by a teammate.
assists	int	Passes that directly led to a goal.
xa	float	Expected Assists — the probability that each key pass leads to a goal, summed across the match. Like xG but for the passer.
xg_chain	float	xG Chain — total xG of all possessions the player was involved in. Measures overall attacking involvement.
xg_buildup	float	xG Buildup — like xG Chain but excludes the shot-taker and assister. Measures contribution to build-up play without the final ball or shot.
yellow_card	bool	Received a yellow card.
red_card	bool	Received a red card.
roster_in	int	Minute the player came on as a sub (null if started).
roster_out	int	Minute the player was subbed off (null if played the full match).

Shot breakdown by play type

Column	Type	Description
open_play_xg	float	xG from open play shots.
open_play_shots	int	Number of open play shots.
open_play_goals_scored	int	Goals scored from open play.
open_play_blocked_shots	int	Open play shots blocked by a defender.
open_play_missed_shots	int	Open play shots that missed the target entirely.
open_play_saved_shots	int	Open play shots saved by the goalkeeper.
open_play_shot_on_post	int	Open play shots that hit the post or crossbar.
set_piece_xg	float	xG from set piece shots.
set_piece_shots	int	Number of set piece shots.
set_piece_goals_scored	int	Goals scored from set piece.
set_piece_blocked_shots	int	Set piece shots blocked by a defender.
set_piece_missed_shots	int	Set piece shots that missed the target entirely.
set_piece_saved_shots	int	Set piece shots saved by the goalkeeper.
set_piece_shot_on_post	int	Set piece shots that hit the post or crossbar.
penalty_xg	float	xG from penalty shots.
penalty_shots	int	Number of penalty shots.
penalty_goals_scored	int	Goals scored from penalty.
penalty_blocked_shots	int	Penalty shots blocked by a defender.
penalty_missed_shots	int	Penalty shots that missed the target entirely.
penalty_saved_shots	int	Penalty shots saved by the goalkeeper.
penalty_shot_on_post	int	Penalty shots that hit the post or crossbar.

Assist breakdown by play type

Column	Type	Description
open_play_xa	float	Expected assists from open play.
open_play_key_passes_from_shots	int	Key passes that led to open play shots.
open_play_assists	int	Assists that led to goals from open play.
set_piece_xa	float	Expected assists from set piece.
set_piece_key_passes_from_shots	int	Key passes that led to set piece shots.
set_piece_assists	int	Assists that led to goals from set piece.

On-field impact (attacking)

Column	Type	Description
team_open_play_xg_with	float	Team's open play xG while the player was on the field.
team_open_play_xg_without	float	Team's open play xG while the player was off the field.
team_set_piece_xg_with	float	Team's set piece xG while the player was on the field.
team_set_piece_xg_without	float	Team's set piece xG while the player was off the field.
team_penalty_xg_with	float	Team's penalty xG while the player was on the field.
team_penalty_xg_without	float	Team's penalty xG while the player was off the field.
team_goals_with	int	Goals the team scored while the player was on the field.
team_goals_without	int	Goals the team scored while the player was off the field.
team_total_xg_with	float	Team's total xG while the player was on the field.
team_total_xg_without	float	Team's total xG while the player was off the field.

On-field impact (defensive)

Column	Type	Description
team_open_play_xg_against_with	float	Opponent open play xG while the player was on the field.
team_open_play_xg_against_without	float	Opponent open play xG while the player was off the field.
team_set_piece_xg_against_with	float	Opponent set piece xG while the player was on the field.
team_set_piece_xg_against_without	float	Opponent set piece xG while the player was off the field.
team_penalty_xg_against_with	float	Opponent penalty xG while the player was on the field.
team_penalty_xg_against_without	float	Opponent penalty xG while the player was off the field.
team_goals_against_with	int	Goals conceded while the player was on the field.
team_goals_against_without	int	Goals conceded while the player was off the field.
team_total_xg_against_with	float	Total opponent xG while the player was on the field.
team_total_xg_against_without	float	Total opponent xG while the player was off the field.

Example queries

Saka's per-90 output this season

SELECT player,
       SUM(minutes_played) as total_mins,
       ROUND(SUM(xg)::numeric / (SUM(minutes_played)::numeric / 90), 2) as xg_per_90,
       ROUND(SUM(xa)::numeric / (SUM(minutes_played)::numeric / 90), 2) as xa_per_90,
       SUM(goals) as goals,
       SUM(assists) as assists
FROM understat_reporting.player_match_stats
WHERE player = 'Bukayo Saka' AND season = '2025' AND appearance_type IN ('start', 'sub')
GROUP BY player

Players whose teams create more xG when they play

SELECT player, team_name,
       COUNT(*) as matches,
       ROUND(AVG(team_total_xg_with)::numeric, 2) as avg_team_xg_with,
       ROUND(AVG(team_total_xg_without)::numeric, 2) as avg_team_xg_without,
       ROUND((AVG(team_total_xg_with) - AVG(team_total_xg_without))::numeric, 2) as xg_diff
FROM understat_reporting.player_match_stats
WHERE league = 'EPL' AND season = '2025' AND appearance_type = 'start'
GROUP BY player, team_name
HAVING COUNT(*) >= 10
ORDER BY xg_diff DESC
LIMIT 10

Gotchas

Seasons are strings, not integers
Use WHERE season = '2025' — not season = 2025. The column stores the year as text.
shots has no league column
Join shots to match_details on match_id when you need to filter by league.
play_status simplifies situation
play_status normalizes the raw situation field into three buckets: open_play, set_piece, penalty.

Query limits

SELECT only. 500 row max. 10 second timeout.
Seasons from 2014/15 to present.