Baseball Player Analysis
L3
PostgresSports
Consolidate scattered baseball player data into comprehensive analysis table combining offensive and defensive statistics.
Created by Lingxiao Du
2025-08-18
Reporting And AnalyticsStatistical AggregationSchema Design
Model Ranking
Click on the dots to view the trajectory of each task run
Task State
Table "addresses" {
"id" int4 [not null, increment]
"location_id" int4 [not null]
"language" varchar(100)
"suite" varchar(100)
"floor" varchar(100)
"building" varchar(100)
"street_number" varchar(100)
"street_prefix" varchar(100)
"street" varchar(100)
"street_suffix" varchar(100)
"neighborhood" varchar(100)
"district" varchar(100)
"locality" varchar(100)
"county" varchar(100)
"region" varchar(100)
"postal_code" varchar(100)
"country" varchar(100)
}
Table "affiliation_phases" {
"id" int4 [not null, increment]
"affiliation_id" int4 [not null]
"ancestor_affiliation_id" int4
"start_season_id" int4
"start_date_time" timestamp
"end_season_id" int4
"end_date_time" timestamp
}
Table "affiliations" {
"id" int4 [not null, increment]
"affiliation_key" varchar(100) [not null]
"affiliation_type" varchar(100)
"publisher_id" int4 [not null]
}
Table "affiliations_documents" {
"affiliation_id" int4 [not null]
"document_id" int4 [not null]
}
Table "affiliations_events" {
"affiliation_id" int4 [not null]
"event_id" int4 [not null]
}
Table "affiliations_media" {
"affiliation_id" int4 [not null]
"media_id" int4 [not null]
}
Table "american_football_action_participants" {
"id" int4 [not null, increment]
"american_football_action_play_id" int4 [not null]
"person_id" int4 [not null]
"participant_role" varchar(100) [not null]
"score_type" varchar(100)
"field_line" int4
"yardage" int4
"score_credit" int4
"yards_gained" int4
}
Table "american_football_action_plays" {
"id" int4 [not null, increment]
"american_football_event_state_id" int4 [not null]
"play_type" varchar(100)
"score_attempt_type" varchar(100)
"drive_result" varchar(100)
"points" int4
"comment" varchar(255)
}
Table "american_football_defensive_stats" {
"id" int4 [not null, increment]
"tackles_total" varchar(100)
"tackles_solo" varchar(100)
"tackles_assists" varchar(100)
"interceptions_total" varchar(100)
"interceptions_yards" varchar(100)
"interceptions_average" varchar(100)
"interceptions_longest" varchar(100)
"interceptions_touchdown" varchar(100)
"quarterback_hurries" varchar(100)
"sacks_total" varchar(100)
"sacks_yards" varchar(100)
"passes_defensed" varchar(100)
}
Table "american_football_down_progress_stats" {
"id" int4 [not null, increment]
"first_downs_total" varchar(100)
"first_downs_pass" varchar(100)
"first_downs_run" varchar(100)
"first_downs_penalty" varchar(100)
"conversions_third_down" varchar(100)
"conversions_third_down_attempts" varchar(100)
"conversions_third_down_percentage" varchar(100)
"conversions_fourth_down" varchar(100)
"conversions_fourth_down_attempts" varchar(100)
"conversions_fourth_down_percentage" varchar(100)
}
Table "american_football_event_states" {
"id" int4 [not null, increment]
"event_id" int4 [not null]
"current_state" int2
"sequence_number" int4
"period_value" int4
"period_time_elapsed" varchar(100)
"period_time_remaining" varchar(100)
"clock_state" varchar(100)
"down" int4
"team_in_possession_id" int4
"distance_for_1st_down" int4
"field_side" varchar(100)
"field_line" int4
"context" varchar(40)
}
Table "american_football_fumbles_stats" {
"id" int4 [not null, increment]
"fumbles_committed" varchar(100)
"fumbles_forced" varchar(100)
"fumbles_recovered" varchar(100)
"fumbles_lost" varchar(100)
"fumbles_yards_gained" varchar(100)
"fumbles_own_committed" varchar(100)
"fumbles_own_recovered" varchar(100)
"fumbles_own_lost" varchar(100)
"fumbles_own_yards_gained" varchar(100)
"fumbles_opposing_committed" varchar(100)
"fumbles_opposing_recovered" varchar(100)
"fumbles_opposing_lost" varchar(100)
"fumbles_opposing_yards_gained" varchar(100)
}
Table "american_football_offensive_stats" {
"id" int4 [not null, increment]
"offensive_plays_yards" varchar(100)
"offensive_plays_number" varchar(100)
"offensive_plays_average_yards_per" varchar(100)
"possession_duration" varchar(100)
"turnovers_giveaway" varchar(100)
}
Table "american_football_passing_stats" {
"id" int4 [not null, increment]
"passes_attempts" varchar(100)
"passes_completions" varchar(100)
"passes_percentage" varchar(100)
"passes_yards_gross" varchar(100)
"passes_yards_net" varchar(100)
"passes_yards_lost" varchar(100)
"passes_touchdowns" varchar(100)
"passes_touchdowns_percentage" varchar(100)
"passes_interceptions" varchar(100)
"passes_interceptions_percentage" varchar(100)
"passes_longest" varchar(100)
"passes_average_yards_per" varchar(100)
"passer_rating" varchar(100)
"receptions_total" varchar(100)
"receptions_yards" varchar(100)
"receptions_touchdowns" varchar(100)
"receptions_first_down" varchar(100)
"receptions_longest" varchar(100)
"receptions_average_yards_per" varchar(100)
}
Table "american_football_penalties_stats" {
"id" int4 [not null, increment]
"penalties_total" varchar(100)
"penalty_yards" varchar(100)
"penalty_first_downs" varchar(100)
}
Table "american_football_rushing_stats" {
"id" int4 [not null, increment]
"rushes_attempts" varchar(100)
"rushes_yards" varchar(100)
"rushes_touchdowns" varchar(100)
"rushing_average_yards_per" varchar(100)
"rushes_first_down" varchar(100)
"rushes_longest" varchar(100)
}
Table "american_football_sacks_against_stats" {
"id" int4 [not null, increment]
"sacks_against_yards" varchar(100)
"sacks_against_total" varchar(100)
}
Table "american_football_scoring_stats" {
"id" int4 [not null, increment]
"touchdowns_total" varchar(100)
"touchdowns_passing" varchar(100)
"touchdowns_rushing" varchar(100)
"touchdowns_special_teams" varchar(100)
"touchdowns_defensive" varchar(100)
"extra_points_attempts" varchar(100)
"extra_points_made" varchar(100)
"extra_points_missed" varchar(100)
"extra_points_blocked" varchar(100)
"field_goal_attempts" varchar(100)
"field_goals_made" varchar(100)
"field_goals_missed" varchar(100)
"field_goals_blocked" varchar(100)
"safeties_against" varchar(100)
"two_point_conversions_attempts" varchar(100)
"two_point_conversions_made" varchar(100)
"touchbacks_total" varchar(100)
}
Table "american_football_special_teams_stats" {
"id" int4 [not null, increment]
"returns_punt_total" varchar(100)
"returns_punt_yards" varchar(100)
"returns_punt_average" varchar(100)
"returns_punt_longest" varchar(100)
"returns_punt_touchdown" varchar(100)
"returns_kickoff_total" varchar(100)
"returns_kickoff_yards" varchar(100)
"returns_kickoff_average" varchar(100)
"returns_kickoff_longest" varchar(100)
"returns_kickoff_touchdown" varchar(100)
"returns_total" varchar(100)
"returns_yards" varchar(100)
"punts_total" varchar(100)
"punts_yards_gross" varchar(100)
"punts_yards_net" varchar(100)
"punts_longest" varchar(100)
"punts_inside_20" varchar(100)
"punts_inside_20_percentage" varchar(100)
"punts_average" varchar(100)
"punts_blocked" varchar(100)
"touchbacks_total" varchar(100)
"touchbacks_total_percentage" varchar(100)
"touchbacks_kickoffs" varchar(100)
"touchbacks_kickoffs_percentage" varchar(100)
"touchbacks_punts" varchar(100)
"touchbacks_punts_percentage" varchar(100)
"touchbacks_interceptions" varchar(100)
"touchbacks_interceptions_percentage" varchar(100)
"fair_catches" varchar(100)
}
Table "baseball_action_contact_details" {
"id" int4 [not null, increment]
"baseball_action_pitch_id" int4 [not null]
"location" varchar(100)
"strength" varchar(100)
"velocity" int4
"comment" text
"trajectory_coordinates" varchar(100)
"trajectory_formula" varchar(100)
}
Table "baseball_action_pitches" {
"id" int4 [not null, increment]
"baseball_action_play_id" int4 [not null]
"sequence_number" int4
"baseball_defensive_group_id" int4
"umpire_call" varchar(100)
"pitch_location" varchar(100)
"pitch_type" varchar(100)
"pitch_velocity" int4
"comment" text
"trajectory_coordinates" varchar(100)
"trajectory_formula" varchar(100)
"ball_type" varchar(40)
"strike_type" varchar(40)
}
Table "baseball_action_plays" {
"id" int4 [not null, increment]
"baseball_event_state_id" int4 [not null]
"play_type" varchar(100)
"notation" varchar(100)
"notation_yaml" text
"baseball_defensive_group_id" int4
"comment" varchar(255)
"runner_on_first_advance" int4
"runner_on_second_advance" int4
"runner_on_third_advance" int4
"outs_recorded" int4
"rbi" int4
"runs_scored" int4
"earned_runs_scored" varchar(100)
}
Table "baseball_action_substitutions" {
"id" int4 [not null, increment]
"baseball_event_state_id" int4 [not null]
"sequence_number" int4
"person_type" varchar(100)
"person_original_id" int4
"person_original_position_id" int4
"person_original_lineup_slot" int4
"person_replacing_id" int4
"person_replacing_position_id" int4
"person_replacing_lineup_slot" int4
"substitution_reason" varchar(100)
"comment" varchar(100)
}
Table "baseball_defensive_group" {
"id" int4 [not null, increment]
}
Table "baseball_defensive_players" {
"id" int4 [not null, increment]
"baseball_defensive_group_id" int4 [not null]
"player_id" int4 [not null]
"position_id" int4 [not null]
}
Table "baseball_defensive_stats" {
"id" int4 [not null, increment]
"double_plays" int4
"triple_plays" int4
"putouts" int4
"assists" int4
"errors" int4
"fielding_percentage" numeric
"defensive_average" numeric
"errors_passed_ball" int4
"errors_catchers_interference" int4
}
Table "baseball_event_states" {
"id" int4 [not null, increment]
"event_id" int4 [not null]
"current_state" int2
"sequence_number" int4
"at_bat_number" int4
"inning_value" int4
"inning_half" varchar(100)
"outs" int4
"balls" int4
"strikes" int4
"runner_on_first_id" int4
"runner_on_second_id" int4
"runner_on_third_id" int4
"runner_on_first" int2
"runner_on_second" int2
"runner_on_third" int2
"runs_this_inning_half" int4
"pitcher_id" int4
"batter_id" int4
"batter_side" varchar(100)
"context" varchar(40)
}
Table "baseball_offensive_stats" {
"id" int4 [not null, increment]
"average" numeric
"runs_scored" int4
"at_bats" int4
"hits" int4
"rbi" int4
"total_bases" int4
"slugging_percentage" numeric
"bases_on_balls" int4
"strikeouts" int4
"left_on_base" int4
"left_in_scoring_position" int4
"singles" int4
"doubles" int4
"triples" int4
"home_runs" int4
"grand_slams" int4
"at_bats_per_rbi" numeric
"plate_appearances_per_rbi" numeric
"at_bats_per_home_run" numeric
"plate_appearances_per_home_run" numeric
"sac_flies" int4
"sac_bunts" int4
"grounded_into_double_play" int4
"moved_up" int4
"on_base_percentage" numeric
"stolen_bases" int4
"stolen_bases_caught" int4
"stolen_bases_average" numeric
"hit_by_pitch" int4
"defensive_interferance_reaches" int4
"on_base_plus_slugging" numeric
"plate_appearances" int4
"hits_extra_base" int4
}
Table "baseball_pitching_stats" {
"id" int4 [not null, increment]
"runs_allowed" int4
"singles_allowed" int4
"doubles_allowed" int4
"triples_allowed" int4
"home_runs_allowed" int4
"innings_pitched" varchar(20)
"hits" int4
"earned_runs" int4
"unearned_runs" int4
"bases_on_balls" int4
"bases_on_balls_intentional" int4
"strikeouts" int4
"strikeout_to_bb_ratio" numeric
"number_of_pitches" int4
"era" numeric
"inherited_runners_scored" int4
"pick_offs" int4
"errors_hit_with_pitch" int4
"errors_wild_pitch" int4
"balks" int4
"wins" int4
"losses" int4
"saves" int4
"shutouts" int4
"games_complete" int4
"games_finished" int4
"winning_percentage" numeric
"event_credit" varchar(40)
"save_credit" varchar(40)
}
Table "basketball_defensive_stats" {
"id" int4 [not null, increment]
"steals_total" varchar(100)
"steals_per_game" varchar(100)
"blocks_total" varchar(100)
"blocks_per_game" varchar(100)
}
Table "basketball_event_states" {
"id" int4 [not null, increment]
"event_id" int4 [not null]
"current_state" int4
"sequence_number" int4
"period_value" varchar(100)
"period_time_elapsed" varchar(100)
"period_time_remaining" varchar(100)
"context" varchar(40)
}
Table "basketball_offensive_stats" {
"id" int4 [not null, increment]
"field_goals_made" int4
"field_goals_attempted" int4
"field_goals_percentage" varchar(100)
"field_goals_per_game" varchar(100)
"field_goals_attempted_per_game" varchar(100)
"field_goals_percentage_adjusted" varchar(100)
"three_pointers_made" int4
"three_pointers_attempted" int4
"three_pointers_percentage" varchar(100)
"three_pointers_per_game" varchar(100)
"three_pointers_attempted_per_game" varchar(100)
"free_throws_made" varchar(100)
"free_throws_attempted" varchar(100)
"free_throws_percentage" varchar(100)
"free_throws_per_game" varchar(100)
"free_throws_attempted_per_game" varchar(100)
"points_scored_total" varchar(100)
"points_scored_per_game" varchar(100)
"assists_total" varchar(100)
"assists_per_game" varchar(100)
"turnovers_total" varchar(100)
"turnovers_per_game" varchar(100)
"points_scored_off_turnovers" varchar(100)
"points_scored_in_paint" varchar(100)
"points_scored_on_second_chance" varchar(100)
"points_scored_on_fast_break" varchar(100)
}
Table "basketball_rebounding_stats" {
"id" int4 [not null, increment]
"rebounds_total" varchar(100)
"rebounds_per_game" varchar(100)
"rebounds_defensive" varchar(100)
"rebounds_offensive" varchar(100)
"team_rebounds_total" varchar(100)
"team_rebounds_per_game" varchar(100)
"team_rebounds_defensive" varchar(100)
"team_rebounds_offensive" varchar(100)
}
Table "basketball_team_stats" {
"id" int4 [not null, increment]
"timeouts_left" varchar(100)
"largest_lead" varchar(100)
"fouls_total" varchar(100)
"turnover_margin" varchar(100)
}
Table "bookmakers" {
"id" int4 [not null, increment]
"bookmaker_key" varchar(100)
"publisher_id" int4 [not null]
"location_id" int4
}
Table "core_person_stats" {
"id" int4 [not null, increment]
"time_played_event" varchar(40)
"time_played_total" varchar(40)
"time_played_event_average" varchar(40)
"events_played" int4
"events_started" int4
"position_id" int4
}
Table "core_stats" {
"id" int4 [not null, increment]
"score" varchar(100)
"score_opposing" varchar(100)
"score_attempts" varchar(100)
"score_attempts_opposing" varchar(100)
"score_percentage" varchar(100)
"score_percentage_opposing" varchar(100)
}
Table "db_info" {
"version" varchar(100) [not null, default: 16]
}
Table "display_names" {
"id" int4 [not null, increment]
"language" varchar(100) [not null]
"entity_type" varchar(100) [not null]
"entity_id" int4 [not null]
"full_name" varchar(100)
"first_name" varchar(100)
"middle_name" varchar(100)
"last_name" varchar(100)
"alias" varchar(100)
"abbreviation" varchar(100)
"short_name" varchar(100)
"prefix" varchar(20)
"suffix" varchar(20)
}
Table "document_classes" {
"id" int4 [not null, increment]
"name" varchar(100)
}
Table "document_contents" {
"id" int4 [not null, increment]
"document_id" int4 [not null]
"sportsml" varchar(200)
"abstract" text
}
Table "document_fixtures" {
"id" int4 [not null, increment]
"fixture_key" varchar(100)
"publisher_id" int4 [not null]
"name" varchar(100)
"document_class_id" int4 [not null]
}
Table "document_fixtures_events" {
"id" int4 [not null, increment]
"document_fixture_id" int4 [not null]
"event_id" int4 [not null]
"latest_document_id" int4 [not null]
"last_update" timestamp
}
Table "document_package_entry" {
"id" int4 [not null, increment]
"document_package_id" int4 [not null]
"rank" varchar(100)
"document_id" int4 [not null]
"headline" varchar(100)
"short_headline" varchar(100)
}
Table "document_packages" {
"id" int4 [not null, increment]
"package_key" varchar(100)
"package_name" varchar(100)
"date_time" date
}
Table "documents" {
"id" int4 [not null, increment]
"doc_id" varchar(75) [not null]
"publisher_id" int4 [not null]
"date_time" timestamp
"title" varchar(255)
"language" varchar(100)
"priority" varchar(100)
"revision_id" varchar(75)
"stats_coverage" varchar(100)
"document_fixture_id" int4 [not null]
"source_id" int4
"db_loading_date_time" timestamp
}
Table "documents_media" {
"id" int4 [not null, increment]
"document_id" int4 [not null]
"media_id" int4 [not null]
"media_caption_id" int4 [not null]
}
Table "events" {
"id" int4 [not null, increment]
"event_key" varchar(100) [not null]
"publisher_id" int4 [not null]
"start_date_time" timestamp
"site_id" int4
"site_alignment" varchar(100)
"event_status" varchar(100)
"duration" varchar(100)
"attendance" varchar(100)
"last_update" timestamp
}
Table "events_documents" {
"event_id" int4 [not null]
"document_id" int4 [not null]
}
Table "events_media" {
"event_id" int4 [not null]
"media_id" int4 [not null]
}
Table "events_sub_seasons" {
"event_id" int4 [not null]
"sub_season_id" int4 [not null]
}
Table "ice_hockey_action_participants" {
"id" int4 [not null, increment]
"ice_hockey_action_play_id" int4 [not null]
"person_id" int4 [not null]
"participant_role" varchar(100) [not null]
"point_credit" int4
}
Table "ice_hockey_action_plays" {
"id" int4 [not null, increment]
"ice_hockey_event_state_id" int4 [not null]
"play_type" varchar(100)
"score_attempt_type" varchar(100)
"play_result" varchar(100)
"comment" varchar(255)
}
Table "ice_hockey_defensive_stats" {
"id" int4 [not null, increment]
"shots_power_play_allowed" varchar(100)
"shots_penalty_shot_allowed" varchar(100)
"goals_power_play_allowed" varchar(100)
"goals_penalty_shot_allowed" varchar(100)
"goals_against_average" varchar(100)
"saves" varchar(100)
"save_percentage" varchar(100)
"penalty_killing_amount" varchar(100)
"penalty_killing_percentage" varchar(100)
"shots_blocked" varchar(100)
"takeaways" varchar(100)
"shutouts" varchar(100)
"minutes_penalty_killing" varchar(100)
"hits" varchar(100)
"goals_empty_net_allowed" varchar(100)
"goals_short_handed_allowed" varchar(100)
"goals_shootout_allowed" varchar(100)
"shots_shootout_allowed" varchar(100)
}
Table "ice_hockey_event_states" {
"id" int4 [not null, increment]
"event_id" int4 [not null]
"current_state" int4
"sequence_number" int4
"period_value" varchar(100)
"period_time_elapsed" varchar(100)
"period_time_remaining" varchar(100)
"context" varchar(40)
}
Table "ice_hockey_offensive_stats" {
"id" int4 [not null, increment]
"goals_game_winning" varchar(100)
"goals_game_tying" varchar(100)
"goals_power_play" varchar(100)
"goals_short_handed" varchar(100)
"goals_even_strength" varchar(100)
"goals_empty_net" varchar(100)
"goals_overtime" varchar(100)
"goals_shootout" varchar(100)
"goals_penalty_shot" varchar(100)
"assists" varchar(100)
"points" varchar(100)
"power_play_amount" varchar(100)
"power_play_percentage" varchar(100)
"shots_penalty_shot_taken" varchar(100)
"shots_penalty_shot_missed" varchar(100)
"shots_penalty_shot_percentage" varchar(100)
"giveaways" varchar(100)
"minutes_power_play" varchar(100)
"faceoff_wins" varchar(100)
"faceoff_losses" varchar(100)
"faceoff_win_percentage" varchar(100)
"scoring_chances" varchar(100)
}
Table "ice_hockey_player_stats" {
"id" int4 [not null, increment]
"plus_minus" varchar(100)
}
Table "injury_phases" {
"id" int4 [not null, increment]
"person_id" int4 [not null]
"injury_status" varchar(100)
"injury_type" varchar(100)
"injury_comment" varchar(100)
"disabled_list" varchar(100)
"start_date_time" timestamp
"end_date_time" timestamp
"season_id" int4
"phase_type" varchar(100)
"injury_side" varchar(100)
}
Table "key_aliases" {
"id" int4 [not null, increment]
"key_id" int4 [not null]
"key_root_id" int4 [not null]
}
Table "key_roots" {
"id" int4 [not null, increment]
"key_type" varchar(100)
}
Table "latest_revisions" {
"id" int4 [not null, increment]
"revision_id" varchar(75) [not null]
"latest_document_id" int4 [not null]
}
Table "locations" {
"id" int4 [not null, increment]
"timezone" varchar(100)
"latitude" varchar(100)
"longitude" varchar(100)
"country_code" varchar(100)
}
Table "media" {
"id" int4 [not null, increment]
"object_id" int4
"source_id" int4
"revision_id" int4
"media_type" varchar(100)
"publisher_id" int4 [not null]
"date_time" varchar(100)
"credit_id" int4 [not null]
"db_loading_date_time" timestamp
"creation_location_id" int4 [not null]
}
Table "media_captions" {
"id" int4 [not null, increment]
"media_id" int4 [not null]
"caption_type" varchar(100)
"caption" varchar(100)
"caption_author_id" int4 [not null]
"language" varchar(100)
"caption_size" varchar(100)
}
Table "media_contents" {
"id" int4 [not null, increment]
"media_id" int4 [not null]
"object" varchar(100)
"format" varchar(100)
"mime_type" varchar(100)
"height" varchar(100)
"width" varchar(100)
"duration" varchar(100)
"file_size" varchar(100)
"resolution" varchar(100)
}
Table "media_keywords" {
"id" int4 [not null, increment]
"keyword" varchar(100)
"media_id" int4 [not null]
}
Table "motor_racing_event_states" {
"id" int4 [not null, increment]
"event_id" int4 [not null]
"current_state" int4
"sequence_number" int4
"lap" varchar(100)
"laps_remaining" varchar(100)
"time_elapsed" varchar(100)
"flag_state" varchar(100)
"context" varchar(40)
}
Table "motor_racing_qualifying_stats" {
"id" int4 [not null, increment]
"grid" varchar(100)
"pole_position" varchar(100)
"pole_wins" varchar(100)
"qualifying_speed" varchar(100)
"qualifying_speed_units" varchar(100)
"qualifying_time" varchar(100)
"qualifying_position" varchar(100)
}
Table "motor_racing_race_stats" {
"id" int4 [not null, increment]
"time_behind_leader" varchar(100)
"laps_behind_leader" varchar(100)
"time_ahead_follower" varchar(100)
"laps_ahead_follower" varchar(100)
"time" varchar(100)
"points" varchar(100)
"points_rookie" varchar(100)
"bonus" varchar(100)
"laps_completed" varchar(100)
"laps_leading_total" varchar(100)
"distance_leading" varchar(100)
"distance_completed" varchar(100)
"distance_units" varchar(40)
"speed_average" varchar(40)
"speed_units" varchar(40)
"status" varchar(40)
"finishes_top_5" varchar(40)
"finishes_top_10" varchar(40)
"starts" varchar(40)
"finishes" varchar(40)
"non_finishes" varchar(40)
"wins" varchar(40)
"races_leading" varchar(40)
"money" varchar(40)
"money_units" varchar(40)
"leads_total" varchar(40)
}
Table "outcome_totals" {
"id" int4 [not null, increment]
"standing_subgroup_id" int4 [not null]
"outcome_holder_type" varchar(100)
"outcome_holder_id" int4
"rank" varchar(100)
"wins" varchar(100)
"losses" varchar(100)
"ties" varchar(100)
"undecideds" varchar(100)
"winning_percentage" varchar(100)
"points_scored_for" varchar(100)
"points_scored_against" varchar(100)
"points_difference" varchar(100)
"standing_points" varchar(100)
"streak_type" varchar(100)
"streak_duration" varchar(100)
"streak_total" varchar(100)
"streak_start" date
"streak_end" date
}
Table "participants_events" {
"id" int4 [not null, increment]
"participant_type" varchar(100) [not null]
"participant_id" int4 [not null]
"event_id" int4 [not null]
"alignment" varchar(100)
"score" varchar(100)
"event_outcome" varchar(100)
"rank" int4
}
Table "periods" {
"id" int4 [not null, increment]
"participant_event_id" int4 [not null]
"period_value" varchar(100)
"score" varchar(100)
}
Table "person_event_metadata" {
"id" int4 [not null, increment]
"person_id" int4 [not null]
"event_id" int4 [not null]
"status" varchar(100)
"health" varchar(100)
"weight" varchar(100)
"role_id" int4
"position_id" int4
"team_id" int4
"lineup_slot" int4
"lineup_slot_sequence" int4
}
Table "person_phases" {
"id" int4 [not null, increment]
"person_id" int4 [not null]
"membership_type" varchar(40) [not null]
"membership_id" int4 [not null]
"role_id" int4
"role_status" varchar(40)
"phase_status" varchar(40)
"uniform_number" varchar(20)
"regular_position_id" int4
"regular_position_depth" varchar(40)
"height" varchar(100)
"weight" varchar(100)
"start_date_time" timestamp
"start_season_id" int4
"end_date_time" timestamp
"end_season_id" int4
"entry_reason" varchar(40)
"exit_reason" varchar(40)
"selection_level" int4
"selection_sublevel" int4
"selection_overall" int4
}
Table "persons" {
"id" int4 [not null, increment]
"person_key" varchar(100) [not null]
"publisher_id" int4 [not null]
"gender" varchar(20)
"birth_date" varchar(30)
"death_date" varchar(30)
"birth_location_id" int4
"hometown_location_id" int4
"residence_location_id" int4
"death_location_id" int4
}
Table "persons_documents" {
"person_id" int4 [not null]
"document_id" int4 [not null]
}
Table "persons_media" {
"person_id" int4 [not null]
"media_id" int4 [not null]
}
Table "positions" {
"id" int4 [not null, increment]
"affiliation_id" int4 [not null]
"abbreviation" varchar(100) [not null]
}
Table "publishers" {
"id" int4 [not null, increment]
"publisher_key" varchar(100) [not null]
"publisher_name" varchar(100)
}
Table "roles" {
"id" int4 [not null, increment]
"role_key" varchar(100) [not null]
"role_name" varchar(100)
"comment" varchar(100)
}
Table "seasons" {
"id" int4 [not null, increment]
"season_key" int4 [not null]
"publisher_id" int4 [not null]
"league_id" int4 [not null]
"start_date_time" timestamp
"end_date_time" timestamp
}
Table "sites" {
"id" int4 [not null, increment]
"site_key" int4 [not null]
"publisher_id" int4 [not null]
"location_id" int4
}
Table "soccer_defensive_stats" {
"id" int4 [not null, increment]
"shots_penalty_shot_allowed" varchar(100)
"goals_penalty_shot_allowed" varchar(100)
"goals_against_average" varchar(100)
"goals_against_total" varchar(100)
"saves" varchar(100)
"save_percentage" varchar(100)
"catches_punches" varchar(100)
"shots_on_goal_total" varchar(100)
"shots_shootout_total" varchar(100)
"shots_shootout_allowed" varchar(100)
"shots_blocked" varchar(100)
"shutouts" varchar(100)
}
Table "soccer_event_states" {
"id" int4 [not null, increment]
"event_id" int4 [not null]
"current_state" int4
"sequence_number" int4
"period_value" varchar(100)
"period_time_elapsed" varchar(100)
"period_time_remaining" varchar(100)
"minutes_elapsed" varchar(100)
"period_minute_elapsed" varchar(100)
"context" varchar(40)
}
Table "soccer_foul_stats" {
"id" int4 [not null, increment]
"fouls_suffered" varchar(100)
"fouls_commited" varchar(100)
"cautions_total" varchar(100)
"cautions_pending" varchar(100)
"caution_points_total" varchar(100)
"caution_points_pending" varchar(100)
"ejections_total" varchar(100)
}
Table "soccer_offensive_stats" {
"id" int4 [not null, increment]
"goals_game_winning" varchar(100)
"goals_game_tying" varchar(100)
"goals_overtime" varchar(100)
"goals_shootout" varchar(100)
"goals_total" varchar(100)
"assists_game_winning" varchar(100)
"assists_game_tying" varchar(100)
"assists_overtime" varchar(100)
"assists_total" varchar(100)
"points" varchar(100)
"shots_total" varchar(100)
"shots_on_goal_total" varchar(100)
"shots_hit_frame" varchar(100)
"shots_penalty_shot_taken" varchar(100)
"shots_penalty_shot_scored" varchar(100)
"shots_penalty_shot_missed" varchar(40)
"shots_penalty_shot_percentage" varchar(40)
"shots_shootout_taken" varchar(40)
"shots_shootout_scored" varchar(40)
"shots_shootout_missed" varchar(40)
"shots_shootout_percentage" varchar(40)
"giveaways" varchar(40)
"offsides" varchar(40)
"corner_kicks" varchar(40)
"hat_tricks" varchar(40)
}
Table "standing_subgroups" {
"id" int4 [not null, increment]
"standing_id" int4 [not null]
"affiliation_id" int4 [not null]
}
Table "standings" {
"id" int4 [not null, increment]
"affiliation_id" int4 [not null]
"standing_type" varchar(100)
"sub_season_id" int4 [not null]
"last_updated" varchar(100)
"duration_scope" varchar(100)
"competition_scope" varchar(100)
"competition_scope_id" varchar(100)
"alignment_scope" varchar(100)
"site_scope" varchar(100)
"scoping_label" varchar(100)
"publisher_id" int4 [not null]
"source" varchar(100)
}
Table "stats" {
"id" int4 [not null, increment]
"stat_repository_type" varchar(100)
"stat_repository_id" int4 [not null]
"stat_holder_type" varchar(100)
"stat_holder_id" int4
"stat_coverage_type" varchar(100)
"stat_coverage_id" int4
"context" varchar(40) [not null]
}
Table "sub_periods" {
"id" int4 [not null, increment]
"period_id" int4 [not null]
"sub_period_value" varchar(100)
"score" varchar(100)
}
Table "sub_seasons" {
"id" int4 [not null, increment]
"sub_season_key" varchar(100) [not null]
"season_id" int4 [not null]
"sub_season_type" varchar(100) [not null]
"start_date_time" timestamp
"end_date_time" timestamp
}
Table "team_american_football_stats" {
"id" int4 [not null, increment]
"yards_per_attempt" varchar(100)
"average_starting_position" varchar(100)
"timeouts" varchar(100)
"time_of_possession" varchar(100)
"turnover_ratio" varchar(100)
}
Table "team_phases" {
"id" int4 [not null, increment]
"team_id" int4 [not null]
"start_season_id" int4
"end_season_id" int4
"affiliation_id" int4 [not null]
"start_date_time" varchar(100)
"end_date_time" varchar(100)
"phase_status" varchar(40)
"role_id" int4
}
Table "teams" {
"id" int4 [not null, increment]
"team_key" varchar(100) [not null]
"publisher_id" int4 [not null]
"home_site_id" int4
}
Table "teams_documents" {
"team_id" int4 [not null]
"document_id" int4 [not null]
}
Table "teams_media" {
"team_id" int4 [not null]
"media_id" int4 [not null]
}
Table "tennis_action_points" {
"id" int4 [not null, increment]
"sub_period_id" varchar(100)
"sequence_number" varchar(100)
"win_type" varchar(100)
}
Table "tennis_action_volleys" {
"id" int4 [not null, increment]
"sequence_number" varchar(100)
"tennis_action_points_id" int4
"landing_location" varchar(100)
"swing_type" varchar(100)
"result" varchar(100)
"spin_type" varchar(100)
"trajectory_details" varchar(100)
}
Table "tennis_event_states" {
"id" int4 [not null, increment]
"event_id" int4 [not null]
"current_state" int4
"sequence_number" int4
"tennis_set" varchar(100)
"game" varchar(100)
"server_person_id" int4
"server_score" varchar(100)
"receiver_person_id" int4
"receiver_score" varchar(100)
"service_number" varchar(100)
"context" varchar(40)
}
Table "tennis_return_stats" {
"id" int4 [not null, increment]
"returns_played" varchar(100)
"matches_played" varchar(100)
"first_service_return_points_won" varchar(100)
"first_service_return_points_won_pct" varchar(100)
"second_service_return_points_won" varchar(100)
"second_service_return_points_won_pct" varchar(100)
"return_games_played" varchar(100)
"return_games_won" varchar(100)
"return_games_won_pct" varchar(100)
"break_points_played" varchar(100)
"break_points_converted" varchar(100)
"break_points_converted_pct" varchar(100)
}
Table "tennis_service_stats" {
"id" int4 [not null, increment]
"services_played" varchar(100)
"matches_played" varchar(100)
"aces" varchar(100)
"first_services_good" varchar(100)
"first_services_good_pct" varchar(100)
"first_service_points_won" varchar(100)
"first_service_points_won_pct" varchar(100)
"second_service_points_won" varchar(100)
"second_service_points_won_pct" varchar(100)
"service_games_played" varchar(100)
"service_games_won" varchar(100)
"service_games_won_pct" varchar(100)
"break_points_played" varchar(100)
"break_points_saved" varchar(100)
"break_points_saved_pct" varchar(100)
}
Table "wagering_moneylines" {
"id" int4 [not null, increment]
"bookmaker_id" int4 [not null]
"event_id" int4 [not null]
"date_time" timestamp
"team_id" int4 [not null]
"person_id" int4
"rotation_key" varchar(100)
"comment" varchar(100)
"vigorish" varchar(100)
"line" varchar(100)
"line_opening" varchar(100)
"prediction" varchar(100)
}
Table "wagering_odds_lines" {
"id" int4 [not null, increment]
"bookmaker_id" int4 [not null]
"event_id" int4 [not null]
"date_time" timestamp
"team_id" int4 [not null]
"person_id" int4
"rotation_key" varchar(100)
"comment" varchar(100)
"numerator" varchar(100)
"denominator" varchar(100)
"prediction" varchar(100)
"payout_calculation" varchar(100)
"payout_amount" varchar(100)
}
Table "wagering_runlines" {
"id" int4 [not null, increment]
"bookmaker_id" int4 [not null]
"event_id" int4 [not null]
"date_time" timestamp
"team_id" int4 [not null]
"person_id" int4
"rotation_key" varchar(100)
"comment" varchar(100)
"vigorish" varchar(100)
"line" varchar(100)
"line_opening" varchar(100)
"line_value" varchar(100)
"prediction" varchar(100)
}
Table "wagering_straight_spread_lines" {
"id" int4 [not null, increment]
"bookmaker_id" int4 [not null]
"event_id" int4 [not null]
"date_time" timestamp
"team_id" int4 [not null]
"person_id" int4
"rotation_key" varchar(100)
"comment" varchar(100)
"vigorish" varchar(100)
"line_value" varchar(100)
"line_value_opening" varchar(100)
"prediction" varchar(100)
}
Table "wagering_total_score_lines" {
"id" int4 [not null, increment]
"bookmaker_id" int4 [not null]
"event_id" int4 [not null]
"date_time" timestamp
"team_id" int4 [not null]
"person_id" int4
"rotation_key" varchar(100)
"comment" varchar(100)
"vigorish" varchar(100)
"line_over" varchar(100)
"line_under" varchar(100)
"total" varchar(100)
"total_opening" varchar(100)
"prediction" varchar(100)
}
Table "weather_conditions" {
"id" int4 [not null, increment]
"event_id" int4 [not null]
"temperature" varchar(100)
"temperature_units" varchar(40)
"humidity" varchar(100)
"clouds" varchar(100)
"wind_direction" varchar(100)
"wind_velocity" varchar(100)
"weather_code" varchar(100)
}
Instruction
Create comprehensive baseball player performance analysis in the sports database.
Background
You are a sports analyst working with a comprehensive sports database. The analytics team needs to create a detailed analysis of baseball players by combining their offensive and defensive statistics with personal information. Currently, this data is scattered across multiple tables and needs to be consolidated for reporting purposes.
Your Task
Create a table called baseball_player_analysis that consolidates baseball player performance data. The table should provide a comprehensive view of each qualifying player's performance metrics.
Table Structure
Create the baseball_player_analysis table with the following columns:
player_id(INTEGER, NOT NULL) - Player identifierplayer_name(VARCHAR(255), NOT NULL) - Player's full nameteam_name(VARCHAR(255)) - Set to 'Unknown' for all playersgames_played(INTEGER) - Number of games/events the player participated inat_bats(INTEGER) - Total at-bats for the playerhits(INTEGER) - Total hits for the playerruns_scored(INTEGER) - Total runs scored by the playerrbi(INTEGER) - Total runs batted in by the playerhome_runs(INTEGER) - Total home runs hit by the playerbatting_average(DECIMAL) - Calculated as hits/at_batsdefensive_games(INTEGER) - Number of defensive games played (same as games_played)putouts(INTEGER) - Total putouts in defensive playassists(INTEGER) - Total assists in defensive playerrors(INTEGER) - Total errors made in defensive playfielding_percentage(DECIMAL) - Calculated as (putouts + assists)/(putouts + assists + errors)
Data Requirements
Include only baseball players that meet ALL of the following criteria:
- Have offensive statistics available for regular season play
- Have played at least 10 games/events
- Have at least 50 at-bats
- Have a valid name available in the system
Important Notes
- Focus on regular season statistics only
- Handle NULL values appropriately in calculations (use 0 for missing stats)
- Ensure batting average and fielding percentage calculations handle division by zero
- Do NOT use ROUND functions - keep the full precision of calculated values
- Sort results by batting average descending, then by games played descending
Requirements
- Explore the database to understand the table structure and relationships
- Create the table with the exact structure specified above
- Populate the table using appropriate queries and joins
- Ensure all calculations are mathematically correct
- Handle edge cases properly (division by zero, NULL values)
Verify
Python