nprasad2077
11a5cc0f18
CI Health Check & Quality Gate / Code, dbt & Live App Health Check (pull_request) Successful in 1m11s
293 lines
13 KiB
Python
293 lines
13 KiB
Python
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.")
|