Ultimate Soccer Dataset

CREATE TABLE competitions (
  "competition_id" VARCHAR,
  "slug" VARCHAR,
  "name" VARCHAR,
  "type" VARCHAR,
  "founded_year" VARCHAR,
  "abolished_year" VARCHAR,
  "country" VARCHAR
);

CREATE TABLE seasons (
  "season_id" VARCHAR,
  "competition_id" VARCHAR,
  "start_date" TIMESTAMP,
  "end_date" TIMESTAMP,
  "season_label" VARCHAR
);

CREATE TABLE teams (
  "team_id" VARCHAR,
  "name" VARCHAR,
  "team_type" VARCHAR,
  "country" VARCHAR,
  "stadium_id" VARCHAR,
  "founded_year" DOUBLE
);

CREATE TABLE players (
  "player_id" VARCHAR,
  "name" VARCHAR,
  "full_name" VARCHAR,
  "date_of_birth" VARCHAR,
  "country_of_birth" VARCHAR,
  "city_of_birth" VARCHAR,
  "country_of_citizenship" VARCHAR,
  "positions" VARCHAR,
  "height_cm" VARCHAR,
  "weight_kg" VARCHAR
);

CREATE TABLE player_team_history (
  "player_id" VARCHAR,
  "team_id" VARCHAR,
  "competition_id" VARCHAR,
  "season_id" VARCHAR,
  "position" VARCHAR
);

CREATE TABLE matches (
  "match_id" VARCHAR,
  "season_id" VARCHAR,
  "date" TIMESTAMP,
  "home_team_id" VARCHAR,
  "away_team_id" VARCHAR,
  "venue_id" VARCHAR,
  "referee_id" VARCHAR,
  "status" VARCHAR,
  "home_score" DOUBLE,
  "away_score" DOUBLE,
  "home_extratime_score" DOUBLE,
  "away_extratime_score" DOUBLE,
  "home_penalty_score" DOUBLE,
  "away_penalty_score" DOUBLE,
  "home_halftime_score" DOUBLE,
  "away_halftime_score" DOUBLE,
  "home_manager_name" VARCHAR,
  "away_manager_name" VARCHAR,
  "home_formation" VARCHAR,
  "away_formation" VARCHAR
);

CREATE TABLE referees (
  "referee_id" VARCHAR,
  "name" VARCHAR,
  "nationality" VARCHAR,
  "date_of_birth" VARCHAR
);

CREATE TABLE match_events (
  "match_event_id" VARCHAR,
  "match_id" VARCHAR,
  "minute" BIGINT,
  "stoppage_time" DOUBLE,
  "event_type" VARCHAR,
  "player_id" VARCHAR,
  "team_id" VARCHAR,
  "description" VARCHAR,
  "notes" VARCHAR
);

CREATE TABLE goal_events (
  "match_event_id" VARCHAR,
  "match_id" VARCHAR,
  "player_id" VARCHAR,
  "team_id" VARCHAR,
  "minute" BIGINT,
  "stoppage_time" DOUBLE,
  "goal_type" VARCHAR,
  "notes" VARCHAR
);

CREATE TABLE assist_events (
  "match_event_id" VARCHAR,
  "match_id" VARCHAR,
  "player_id" VARCHAR,
  "team_id" VARCHAR,
  "minute" BIGINT,
  "stoppage_time" DOUBLE
);

CREATE TABLE match_stats (
  "match_id" VARCHAR,
  "team_id" VARCHAR,
  "type" VARCHAR,
  "value" DOUBLE
);

CREATE TABLE match_player_stats (
  "match_id" VARCHAR,
  "team_id" VARCHAR,
  "player_id" VARCHAR,
  "player_number" DOUBLE,
  "is_captain" BOOLEAN,
  "is_substitute" BOOLEAN,
  "minutes_played" DOUBLE,
  "position" VARCHAR,
  "rating" VARCHAR,
  "shots_total" DOUBLE,
  "shots_on_target" DOUBLE,
  "goals_scored" DOUBLE,
  "goals_assists" DOUBLE,
  "goals_conceded" DOUBLE,
  "saves" DOUBLE,
  "passes_total" DOUBLE,
  "passes_key" DOUBLE,
  "tackles_total" DOUBLE,
  "tackles_blocks" DOUBLE,
  "tackles_interceptions" DOUBLE,
  "duels_total" DOUBLE,
  "duels_won" DOUBLE,
  "dribbles_attempts" DOUBLE,
  "dribbles_success" DOUBLE,
  "dribbled_past" DOUBLE,
  "fouls_drawn" DOUBLE,
  "fouls_committed" DOUBLE,
  "yellow_cards" BIGINT,
  "red_cards" BIGINT,
  "penalties_won" DOUBLE,
  "penalties_committed" DOUBLE,
  "penalties_scored" BIGINT,
  "penalties_missed" BIGINT,
  "penalties_saved" DOUBLE,
  "offsides" DOUBLE
);

CREATE TABLE match_lineups (
  "match_id" VARCHAR,
  "team_id" VARCHAR,
  "player_id" VARCHAR,
  "is_starting" BOOLEAN,
  "player_number" DOUBLE,
  "position" VARCHAR
);

CREATE TABLE standings (
  "snapshot_date" TIMESTAMP,
  "season_id" VARCHAR,
  "team_id" VARCHAR,
  "rank" BIGINT,
  "points" BIGINT,
  "played" BIGINT,
  "wins" BIGINT,
  "draws" BIGINT,
  "losses" BIGINT,
  "goals_for" BIGINT,
  "goals_against" BIGINT,
  "goal_difference" BIGINT,
  "home_wins" DOUBLE,
  "home_draws" DOUBLE,
  "home_losses" DOUBLE,
  "away_wins" DOUBLE,
  "away_draws" DOUBLE,
  "away_losses" DOUBLE,
  "home_goals_for" DOUBLE,
  "home_goals_against" DOUBLE,
  "home_goal_difference" BIGINT,
  "away_goals_for" DOUBLE,
  "away_goals_against" DOUBLE,
  "away_goal_difference" BIGINT,
  "notes" VARCHAR
);

CREATE TABLE venues (
  "venue_id" VARCHAR,
  "name" VARCHAR,
  "address" VARCHAR,
  "city" VARCHAR,
  "country" VARCHAR,
  "capacity" BIGINT,
  "surface" VARCHAR
);

CREATE TABLE transfers (
  "transfer_id" VARCHAR,
  "player_id" VARCHAR,
  "from_team_id" VARCHAR,
  "to_team_id" VARCHAR,
  "transfer_date" TIMESTAMP,
  "transfer_type" VARCHAR,
  "transfer_fee" DOUBLE,
  "transfer_fee_currency" VARCHAR,
  "transfer_fee_eur" DOUBLE,
  "last_player_update" TIMESTAMP
);

CREATE TABLE match_betting_odds (
  "match_id" VARCHAR,
  "bookmaker" VARCHAR,
  "market" VARCHAR,
  "outcome" VARCHAR,
  "odds" DOUBLE,
  "odds_type" VARCHAR,
  "collected_at" TIMESTAMP
);