Streamlit_NBA/streamlit_app/app_pages/quarter_analysis.py

import plotly.express as px
import streamlit as st

from utils.charts import AMBER, MADE_EMERALD, MISS_CRIMSON, apply_arena_theme
from utils.db import get_selected_season, query

SEASON, SEASON_LABEL = get_selected_season()

st.title(f":material/timer: Momentum — {SEASON_LABEL}")

tab_overview, tab_teams, tab_comebacks = st.tabs(
    [
        ":material/bar_chart: Quarter Scoring",
        ":material/emoji_events: Team by Quarter",
        ":material/replay: Comebacks",
    ]
)

QUARTER_COLORS = {
    "Q1": "#38BDF8",
    "Q2": MADE_EMERALD,
    "Q3": AMBER,
    "Q4": MISS_CRIMSON,
}

with tab_overview:
    quarter_avg = query(
        """
        SELECT q.period,
               round(avg(q.points_scored), 1) AS avg_pts,
               round(avg(abs(q.period_point_differential)), 1) AS avg_abs_diff,
               round(stddev(q.points_scored), 1) AS scoring_volatility
        FROM main_marts.fct_quarter_scoring q
        JOIN main_marts.dim_seasons s ON q.season_key = s.season_key
        WHERE s.season_start_year = ? AND q.period IN ('Q1','Q2','Q3','Q4')
        GROUP BY q.period ORDER BY q.period
        """,
        [SEASON],
    )

    col1, col2 = st.columns(2)
    with col1:
        fig = px.bar(
            quarter_avg,
            x="period",
            y="avg_pts",
            title="Avg Points per Quarter",
            color="period",
            color_discrete_map=QUARTER_COLORS,
            labels={"period": "Quarter", "avg_pts": "Avg Points"},
        )
        fig.update_layout(showlegend=False)
        apply_arena_theme(fig)
        st.plotly_chart(fig, width="stretch")
    with col2:
        fig2 = px.bar(
            quarter_avg,
            x="period",
            y="avg_abs_diff",
            title="Avg Quarter Lopsidedness (|differential|)",
            color="period",
            color_discrete_map=QUARTER_COLORS,
            labels={"period": "Quarter", "avg_abs_diff": "Avg |Differential|"},
        )
        fig2.update_layout(showlegend=False)
        apply_arena_theme(fig2)
        st.plotly_chart(fig2, width="stretch")

    st.caption("Q4 has lower scoring and tighter margins — games tighten up in crunch time.")

with tab_teams:
    quarter_sel = (
        st.segmented_control(
            "Quarter",
            ["Q1", "Q2", "Q3", "Q4"],
            default="Q1",
        )
        or "Q1"
    )

    team_quarter = query(
        """
        SELECT t.team_abbr AS team,
               round(avg(q.points_scored), 1) AS avg_pts,
               round(avg(q.period_point_differential), 2) AS avg_diff,
               count(*) AS games
        FROM main_marts.fct_quarter_scoring q
        JOIN main_marts.dim_teams t ON q.team_key = t.team_key
        JOIN main_marts.dim_seasons s ON q.season_key = s.season_key
        WHERE s.season_start_year = ? AND q.period = ?
        GROUP BY t.team_abbr HAVING count(*) >= 10
        ORDER BY avg_diff DESC
        """,
        [SEASON, quarter_sel],
    )

    fig = px.bar(
        team_quarter,
        x="avg_pts",
        y="team",
        orientation="h",
        color="avg_diff",
        color_continuous_scale="RdYlGn",
        title=f"{quarter_sel} Leaders — sorted by differential (color = +/-)",
    )
    fig.update_layout(yaxis=dict(autorange="reversed"))
    apply_arena_theme(fig, height=600)
    st.plotly_chart(fig, width="stretch")

with tab_comebacks:
    comebacks = query(
        """
        WITH q3_trailing AS (
            SELECT q.game_id, q.team_key,
                   sum(q.period_point_differential) AS through_q3_diff
            FROM main_marts.fct_quarter_scoring q
            JOIN main_marts.dim_seasons s ON q.season_key = s.season_key
            WHERE s.season_start_year = ? AND q.period IN ('Q1','Q2','Q3')
            GROUP BY q.game_id, q.team_key
            HAVING sum(q.period_point_differential) < -10
        ),
        q4_results AS (
            SELECT q.game_id, q.team_key, q.period_point_differential AS q4_diff
            FROM main_marts.fct_quarter_scoring q
            JOIN main_marts.dim_seasons s ON q.season_key = s.season_key
            WHERE s.season_start_year = ? AND q.period = 'Q4'
        )
        SELECT t.team_abbr AS team,
               count(*) AS trailing_10_after_q3,
               sum(CASE WHEN c.through_q3_diff + r.q4_diff > 0 THEN 1 ELSE 0 END) AS comebacks,
               round(100.0 * sum(CASE WHEN c.through_q3_diff + r.q4_diff > 0 THEN 1 ELSE 0 END) / count(*), 1) AS comeback_pct
        FROM q3_trailing c
        JOIN q4_results r ON c.game_id = r.game_id AND c.team_key = r.team_key
        JOIN main_marts.dim_teams t ON c.team_key = t.team_key
        GROUP BY t.team_abbr HAVING count(*) >= 3
        ORDER BY comeback_pct DESC
        """,
        [SEASON, SEASON],
    )

    if not comebacks.empty:
        fig = px.bar(
            comebacks,
            x="team",
            y="comeback_pct",
            color="comebacks",
            color_continuous_scale="Greens",
            title="Comeback Rate (Down 10+ After Q3)",
        )
        fig.update_layout(xaxis_title="", yaxis_title="Comeback %")
        apply_arena_theme(fig)
        st.plotly_chart(fig, width="stretch")
        st.dataframe(comebacks, hide_index=True, width="stretch")
    else:
        st.info("No comeback data available for this season.")