import plotly.express as px import streamlit as st from streamlit_echarts import st_echarts from utils.charts import AMBER, MISS_CRIMSON, apply_arena_theme from utils.db import get_selected_season, query SEASON, SEASON_LABEL = get_selected_season() st.title(f":material/swords: Matchup Center — {SEASON_LABEL}") teams = query("SELECT DISTINCT team FROM main_intermediate.int_team_performance ORDER BY team") team_list = teams["team"].tolist() col1, col2, col3 = st.columns([2, 2, 1.2]) with col1: team_a = st.selectbox("Team A", team_list, index=0) with col2: team_b = st.selectbox("Team B", team_list, index=min(1, len(team_list) - 1)) with col3: scope = ( st.segmented_control( "Scope", ["This Season", "All-Time"], default="This Season", ) or "This Season" ) if team_a == team_b: st.warning("Select two different teams.") else: if scope == "This Season": h2h_agg = query( """ SELECT team, count(*) AS games, sum(CASE WHEN game_result = 'W' THEN 1 ELSE 0 END) AS wins, round(avg(points), 1) AS avg_pts, round(avg(offensive_rating), 1) AS off_rtg, round(avg(defensive_rating), 1) AS def_rtg, round(avg(net_rating), 1) AS net_rtg, round(avg(pace), 1) AS pace, round(avg(effective_fg_pct) * 100, 1) AS efg_pct, round(avg(turnover_rate) * 100, 1) AS tov_rate FROM main_intermediate.int_team_performance WHERE ((team = ? AND opponent_team = ?) OR (team = ? AND opponent_team = ?)) AND season_start_year = ? GROUP BY team """, [team_a, team_b, team_b, team_a, SEASON], ) else: h2h_agg = query( """ SELECT team, count(*) AS games, sum(CASE WHEN game_result = 'W' THEN 1 ELSE 0 END) AS wins, round(avg(points), 1) AS avg_pts, round(avg(offensive_rating), 1) AS off_rtg, round(avg(defensive_rating), 1) AS def_rtg, round(avg(net_rating), 1) AS net_rtg, round(avg(pace), 1) AS pace, round(avg(effective_fg_pct) * 100, 1) AS efg_pct, round(avg(turnover_rate) * 100, 1) AS tov_rate FROM main_intermediate.int_team_performance WHERE ((team = ? AND opponent_team = ?) OR (team = ? AND opponent_team = ?)) GROUP BY team """, [team_a, team_b, team_b, team_a], ) if h2h_agg.empty or len(h2h_agg) < 2: st.info("No matchup history found between these teams for this scope.") else: row_a = h2h_agg[h2h_agg["team"] == team_a].iloc[0] row_b = h2h_agg[h2h_agg["team"] == team_b].iloc[0] c1, c2, c3, c4, c5 = st.columns(5) c1.metric(f"{team_a} Wins", int(row_a["wins"])) c2.metric(f"{team_a} PPG", row_a["avg_pts"]) c3.metric("Games", int(row_a["games"])) c4.metric(f"{team_b} PPG", row_b["avg_pts"]) c5.metric(f"{team_b} Wins", int(row_b["wins"])) tab_compare, tab_games, tab_players = st.tabs( [ ":material/radar: Comparison", ":material/list_alt: Game Log", ":material/group: Key Players", ] ) with tab_compare: vals_a = [ float(row_a["avg_pts"]), float(row_a["off_rtg"]), float(row_a["pace"]), float(row_a["efg_pct"]), 130 - float(row_a["def_rtg"]), 20 - float(row_a["tov_rate"]), ] vals_b = [ float(row_b["avg_pts"]), float(row_b["off_rtg"]), float(row_b["pace"]), float(row_b["efg_pct"]), 130 - float(row_b["def_rtg"]), 20 - float(row_b["tov_rate"]), ] radar_option = { "legend": {"data": [team_a, team_b], "top": "bottom"}, "color": [AMBER, MISS_CRIMSON], "radar": { "indicator": [ {"name": "Points", "max": max(vals_a[0], vals_b[0]) * 1.15}, {"name": "Off Rating", "max": max(vals_a[1], vals_b[1]) * 1.1}, {"name": "Pace", "max": max(vals_a[2], vals_b[2]) * 1.1}, {"name": "eFG%", "max": max(vals_a[3], vals_b[3]) * 1.15}, {"name": "Defense (inv)", "max": max(vals_a[4], vals_b[4]) * 1.15}, {"name": "Ball Security (inv)", "max": max(vals_a[5], vals_b[5]) * 1.15}, ], "shape": "polygon", "splitArea": { "areaStyle": { "color": ["#0B0F17", "#151D2A", "#1E293B", "#0B0F17"] } }, "axisLine": {"lineStyle": {"color": "rgba(200,200,200,0.3)"}}, "splitLine": {"lineStyle": {"color": "rgba(200,200,200,0.2)"}}, }, "series": [ { "type": "radar", "data": [ { "value": vals_a, "name": team_a, "lineStyle": {"color": AMBER, "width": 2.5}, "areaStyle": {"color": AMBER, "opacity": 0.25}, "itemStyle": {"color": AMBER}, }, { "value": vals_b, "name": team_b, "lineStyle": {"color": MISS_CRIMSON, "width": 2.5}, "areaStyle": {"color": MISS_CRIMSON, "opacity": 0.25}, "itemStyle": {"color": MISS_CRIMSON}, }, ], } ], } st_echarts(options=radar_option, height="450px") st.dataframe( h2h_agg.set_index("team")[ [ "games", "wins", "avg_pts", "off_rtg", "def_rtg", "net_rtg", "pace", "efg_pct", "tov_rate", ] ], width="stretch", ) with tab_games: if scope == "This Season": game_log = query( """ SELECT g.game_date::date::varchar AS date, ht.team_abbr AS home, vt.team_abbr AS away, g.home_points, g.visitor_points, wt.team_abbr AS winner, g.point_differential AS margin, g.game_competitiveness_tier AS type FROM main_marts.fct_game_results g JOIN main_marts.dim_teams ht ON g.home_team_key = ht.team_key JOIN main_marts.dim_teams vt ON g.visitor_team_key = vt.team_key JOIN main_marts.dim_teams wt ON g.winning_team_key = wt.team_key JOIN main_marts.dim_seasons s ON g.season_key = s.season_key WHERE ((ht.team_abbr = ? AND vt.team_abbr = ?) OR (ht.team_abbr = ? AND vt.team_abbr = ?)) AND s.season_start_year = ? ORDER BY g.game_date DESC """, [team_a, team_b, team_b, team_a, SEASON], ) else: game_log = query( """ SELECT g.game_date::date::varchar AS date, ht.team_abbr AS home, vt.team_abbr AS away, g.home_points, g.visitor_points, wt.team_abbr AS winner, g.point_differential AS margin, g.game_competitiveness_tier AS type FROM main_marts.fct_game_results g JOIN main_marts.dim_teams ht ON g.home_team_key = ht.team_key JOIN main_marts.dim_teams vt ON g.visitor_team_key = vt.team_key JOIN main_marts.dim_teams wt ON g.winning_team_key = wt.team_key WHERE ((ht.team_abbr = ? AND vt.team_abbr = ?) OR (ht.team_abbr = ? AND vt.team_abbr = ?)) ORDER BY g.game_date DESC """, [team_a, team_b, team_b, team_a], ) if not game_log.empty: game_log["winner_color"] = game_log["winner"].apply( lambda w: team_a if w == team_a else team_b ) fig = px.bar( game_log, x="date", y="margin", color="winner_color", color_discrete_map={team_a: AMBER, team_b: MISS_CRIMSON}, title="Game Margins (color = winner)", ) fig.update_layout(xaxis_title="", yaxis_title="Point Margin") apply_arena_theme(fig, height=350) st.plotly_chart(fig, width="stretch") st.dataframe( game_log.drop(columns=["winner_color"]), hide_index=True, width="stretch" ) with tab_players: if scope == "This Season": players_in_matchup = query( """ SELECT pp.player_name, pp.team, count(*) AS games, round(avg(pp.points), 1) AS ppg, round(avg(pp.assists), 1) AS apg, round(avg(pp.total_rebounds), 1) AS rpg, round(avg(pp.box_plus_minus), 2) AS bpm FROM main_intermediate.int_player_performance pp JOIN main_intermediate.int_team_performance tp ON pp.game_id = tp.game_id AND pp.team = tp.team WHERE ((tp.team = ? AND tp.opponent_team = ?) OR (tp.team = ? AND tp.opponent_team = ?)) AND tp.season_start_year = ? AND pp.minutes_played >= 15 GROUP BY pp.player_name, pp.team HAVING count(*) >= 2 ORDER BY ppg DESC LIMIT 15 """, [team_a, team_b, team_b, team_a, SEASON], ) else: players_in_matchup = query( """ SELECT pp.player_name, pp.team, count(*) AS games, round(avg(pp.points), 1) AS ppg, round(avg(pp.assists), 1) AS apg, round(avg(pp.total_rebounds), 1) AS rpg, round(avg(pp.box_plus_minus), 2) AS bpm FROM main_intermediate.int_player_performance pp JOIN main_intermediate.int_team_performance tp ON pp.game_id = tp.game_id AND pp.team = tp.team WHERE ((tp.team = ? AND tp.opponent_team = ?) OR (tp.team = ? AND tp.opponent_team = ?)) AND pp.minutes_played >= 15 GROUP BY pp.player_name, pp.team HAVING count(*) >= 2 ORDER BY ppg DESC LIMIT 15 """, [team_a, team_b, team_b, team_a], ) if not players_in_matchup.empty: col1, col2 = st.columns(2) with col1: st.subheader(f"{team_a} Performers") team_a_players = players_in_matchup[players_in_matchup["team"] == team_a] st.dataframe( team_a_players[["player_name", "games", "ppg", "apg", "rpg", "bpm"]], hide_index=True, width="stretch", ) with col2: st.subheader(f"{team_b} Performers") team_b_players = players_in_matchup[players_in_matchup["team"] == team_b] st.dataframe( team_b_players[["player_name", "games", "ppg", "apg", "rpg", "bpm"]], hide_index=True, width="stretch", ) else: st.info("No player data available for this matchup scope.")