Baseball Player Analysis

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

Model	Run Results	Pass@4	Pass^4	Avg Time	Avg Turns	Input Tokens	Output Tokens	Total Tokens

grok-4	3 /4			180.7s	33.3	-	-	-
claude-4-sonnet	2 /4			338.4s	33.8	607,760	7,142	614,902
gpt-5	2 /4			291.2s	23.0	203,799	17,027	220,826
deepseek-chat	1 /4			817.0s	49.0	708,635	8,751	717,386
k2	1 /4			476.4s	42.3	809,537	7,770	817,308
claude-4-1-opus	0 /1	-	-	444.3s	18.0	158,872	9,194	168,066
gemini-2-5-pro	0 /4			62.7s	6.5	43,401	4,791	48,192
o3	0 /4			141.3s	23.5	224,241	8,339	232,580
qwen-3-coder	0 /4			173.3s	33.5	490,638	3,707	494,346

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

Verify

Python

"""
Verification script for PostgreSQL Sports Task 1: Baseball Player Analysis
"""

import os
import sys
import psycopg2
from decimal import Decimal

def rows_match(actual_row, expected_row):
    """Compare two rows with appropriate tolerance for decimals and floats."""
    if len(actual_row) != len(expected_row):
        return False
    
    for actual, expected in zip(actual_row, expected_row):
        if isinstance(actual, (Decimal, float)) and isinstance(expected, (Decimal, float)):
            # Use higher tolerance for floating point comparisons
            if abs(float(actual) - float(expected)) > 0.001:
                return False
        elif actual != expected:
            return False
    
    return True

def get_connection_params() -> dict:
    """Get database connection parameters."""
    return {
        "host": os.getenv("POSTGRES_HOST", "localhost"),
        "port": int(os.getenv("POSTGRES_PORT", 5432)),
        "database": os.getenv("POSTGRES_DATABASE"),
        "user": os.getenv("POSTGRES_USERNAME"),
        "password": os.getenv("POSTGRES_PASSWORD")
    }

def verify_baseball_player_analysis_table(conn) -> bool:
    """Verify the baseball_player_analysis table results."""
    with conn.cursor() as cur:
        cur.execute("""
            SELECT player_id, player_name, team_name, games_played, at_bats, hits,
                   runs_scored, rbi, home_runs, batting_average, defensive_games,
                   putouts, assists, errors, fielding_percentage
            FROM baseball_player_analysis
            ORDER BY batting_average DESC, games_played DESC
        """)
        actual_results = cur.fetchall()
        
        cur.execute("""
            SELECT
            p.id AS player_id,
            MAX(dn.full_name) AS player_name,
            'Unknown' AS team_name,
            core.events_played AS games_played,
            off.at_bats,
            off.hits,
            off.runs_scored,
            off.rbi,
            off.home_runs,
            CASE WHEN off.at_bats > 0
                THEN 1.0 * off.hits / off.at_bats
                ELSE 0
            END AS batting_average,
            core.events_played AS defensive_games,
            COALESCE(def.putouts, 0)  AS putouts,
            COALESCE(def.assists, 0)  AS assists,
            COALESCE(def.errors, 0)   AS errors,
            CASE
                WHEN (COALESCE(def.putouts,0) + COALESCE(def.assists,0) + COALESCE(def.errors,0)) > 0
                THEN 1.0 * (COALESCE(def.putouts,0) + COALESCE(def.assists,0))
                    / (COALESCE(def.putouts,0) + COALESCE(def.assists,0) + COALESCE(def.errors,0))
                ELSE 0
            END AS fielding_percentage
            FROM persons p
            JOIN display_names dn
            ON dn.entity_id = p.id
            AND dn.entity_type = 'persons'
            AND NULLIF(TRIM(dn.full_name), '') IS NOT NULL
            JOIN (
            SELECT s.stat_holder_id AS player_id,
                    SUM(bos.at_bats)       AS at_bats,
                    SUM(bos.hits)          AS hits,
                    SUM(bos.runs_scored)   AS runs_scored,
                    SUM(bos.rbi)           AS rbi,
                    SUM(bos.home_runs)     AS home_runs
            FROM stats s
            JOIN baseball_offensive_stats bos
                ON bos.id = s.stat_repository_id
            WHERE s.stat_holder_type = 'persons'
                AND s.stat_repository_type = 'baseball_offensive_stats'
                AND s.context = 'season-regular'
            GROUP BY s.stat_holder_id
            ) off ON off.player_id = p.id
            JOIN (
            SELECT s.stat_holder_id AS player_id,
                    SUM(cps.events_played) AS events_played
            FROM stats s
            JOIN core_person_stats cps
                ON cps.id = s.stat_repository_id
            WHERE s.stat_holder_type = 'persons'
                AND s.stat_repository_type = 'core_person_stats'
                AND s.context = 'season-regular'
            GROUP BY s.stat_holder_id
            ) core ON core.player_id = p.id
            LEFT JOIN (
            SELECT s.stat_holder_id AS player_id,
                    SUM(bds.putouts)  AS putouts,
                    SUM(bds.assists)  AS assists,
                    SUM(bds.errors)   AS errors
            FROM stats s
            JOIN baseball_defensive_stats bds
                ON bds.id = s.stat_repository_id
            WHERE s.stat_holder_type = 'persons'
                AND s.stat_repository_type = 'baseball_defensive_stats'
                AND s.context = 'season-regular'
            GROUP BY s.stat_holder_id
            ) def ON def.player_id = p.id
            WHERE core.events_played >= 10
            AND off.at_bats >= 50
            GROUP BY
            p.id, core.events_played,
            off.at_bats, off.hits, off.runs_scored, off.rbi, off.home_runs,
            def.putouts, def.assists, def.errors
            ORDER BY batting_average DESC, games_played DESC;
        """)
        expected_results = cur.fetchall()
        
        if len(actual_results) != len(expected_results):
            print(f"❌ baseball_player_analysis table has {len(actual_results)} records, expected {len(expected_results)}")
            return False
            
        mismatches = 0
        for i, (actual, expected) in enumerate(zip(actual_results, expected_results)):
            if not rows_match(actual, expected):
                if mismatches < 5:  # Only show first 5 mismatches
                    print(f"❌ Player analysis row {i+1} mismatch: expected {expected}, got {actual}")
                mismatches += 1
                
        if mismatches > 0:
            print(f"❌ Total player analysis mismatches: {mismatches}")
            return False
            
        print(f"✅ baseball_player_analysis table created and populated correctly ({len(actual_results)} players)")
        return True

def main():
    """Main verification function."""
    print("=" * 70)
    print("PostgreSQL Sports Task 1 Verification: Baseball Player Analysis")
    print("=" * 70)
    
    # Get connection parameters
    conn_params = get_connection_params()
    
    if not conn_params["database"]:
        print("❌ No database specified")
        sys.exit(1)
    
    try:
        # Connect to database
        conn = psycopg2.connect(**conn_params)
        
        # Verify results
        success = verify_baseball_player_analysis_table(conn)
        
        conn.close()
        
        if success:
            print("\n🎉 Task verification: PASS")
            sys.exit(0)
        else:
            print("\n❌ Task verification: FAIL")
            sys.exit(1)
            
    except psycopg2.Error as e:
        print(f"❌ Database error: {e}")
        sys.exit(1)
    except Exception as e:
        print(f"❌ Verification error: {e}")
        sys.exit(1)

if __name__ == "__main__":
    main()