DatabaseCreation: views.sql

DROP VIEW IF EXISTS vw_head_to_head CASCADE;

DROP VIEW IF EXISTS vw_season_overview CASCADE;

DROP VIEW IF EXISTS vw_utilization CASCADE;

-- vw_head_to_head
CREATE VIEW vw_head_to_head AS WITH match_goals AS (
    -- 1: count goals per (match, team)
    -- one row per team per match they scored in
    SELECT
        e.match_id,
        e.team_id,
        COUNT(*) AS goals
    FROM
        Event e
        JOIN Event_type et ON et.event_type_id = e.event_type_id -- left join to keep own goals because they don't have attributes
        LEFT JOIN Event_attribute ea ON ea.event_id = e.event_id
        AND ea.attribute_name = 'shot.outcome'
    WHERE
        (
            et.type = 'Shot'
            AND ea.attribute_value = 'Goal'
        )
        OR et.type = 'Own Goal For'
    GROUP BY
        e.match_id,
        e.team_id
),
match_scores AS (
    -- 2: basically final score for the match
    SELECT
        m.match_id,
        m.match_date,
        m.home_team_id,
        m.away_team_id,
        -- coalesce to 0 because a team that didn't score has no row in match_goals
        COALESCE(hg.goals, 0) AS home_score,
        COALESCE(ag.goals, 0) AS away_score
    FROM
        Match m
         -- left joins to keep matches where one or both teams didn't score
        LEFT JOIN match_goals hg ON hg.match_id = m.match_id
        AND hg.team_id = m.home_team_id
        LEFT JOIN match_goals ag ON ag.match_id = m.match_id
        AND ag.team_id = m.away_team_id
),
pairs AS (
    -- 3: re-orient every match into a canonical (team_a, team_b) pair where team_a_id < team_b_id
    -- home/away goal counts are flipped accordingly
    SELECT
        LEAST(home_team_id, away_team_id) AS team_a_id,
        GREATEST(home_team_id, away_team_id) AS team_b_id,
        match_date,
        CASE
            WHEN home_team_id < away_team_id THEN home_score
            ELSE away_score
        END AS team_a_goals,
        CASE
            WHEN home_team_id < away_team_id THEN away_score
            ELSE home_score
        END AS team_b_goals
    FROM
        match_scores
),
ranked AS (
    -- 4: tag the most recent meeting in each pair with recency_rank = 1
    -- so the outer aggregate can pick out its score without a correlated subquery
    SELECT
        p.*,
        ROW_NUMBER() OVER (
            PARTITION BY team_a_id,
            team_b_id
            ORDER BY
                match_date DESC,
                team_a_goals DESC,
                team_b_goals DESC
        ) AS recency_rank
    FROM
        pairs p
) 
-- 5: collapse to one row per pair with the full record
SELECT
    r.team_a_id,
    ta.name AS team_a_name,
    r.team_b_id,
    tb.name AS team_b_name,
    COUNT(*) AS meetings,
    SUM(
        CASE
            WHEN r.team_a_goals > r.team_b_goals THEN 1
            ELSE 0
        END
    ) AS team_a_wins,
    SUM(
        CASE
            WHEN r.team_b_goals > r.team_a_goals THEN 1
            ELSE 0
        END
    ) AS team_b_wins,
    SUM(
        CASE
            WHEN r.team_a_goals = r.team_b_goals THEN 1
            ELSE 0
        END
    ) AS draws,
    SUM(r.team_a_goals + r.team_b_goals) AS total_goals,
    MAX(r.match_date) AS last_meeting_date,
    -- rows where recency_rank != 1 will have null values, so with max() we filter them out
    MAX(
        CASE
            WHEN r.recency_rank = 1 THEN r.team_a_goals
        END
    ) AS last_team_a_goals,
    MAX(
        CASE
            WHEN r.recency_rank = 1 THEN r.team_b_goals
        END
    ) AS last_team_b_goals
FROM
    ranked r
    JOIN Team ta ON ta.team_id = r.team_a_id
    JOIN Team tb ON tb.team_id = r.team_b_id
GROUP BY
    r.team_a_id,
    ta.name,
    r.team_b_id,
    tb.name;

-- vw_season_overview
-- One row per (division, season). Reuses the same Event-derived goal
-- extractor as vw_head_to_head (CTEs can't be shared across views) to build
-- per-match home/away/total/margin. Attendance per match is the count of
-- scanned tickets; matches without ticket rows fall through a LEFT JOIN as
-- NULL → 0 so averages stay comparable across seasons. Aggregation rolls up
-- to the season for totals, per-game averages, the biggest goal margin in
-- any single match, and the highest combined score in any single match.
CREATE VIEW vw_season_overview AS WITH match_goals AS (
    -- 1: same goal-extraction pattern as vw_head_to_head
    SELECT
        e.match_id,
        e.team_id,
        COUNT(*) AS goals
    FROM
        Event e
        JOIN Event_type et ON et.event_type_id = e.event_type_id
        LEFT JOIN Event_attribute ea ON ea.event_id = e.event_id
        AND ea.attribute_name = 'shot.outcome'
    WHERE
        (
            et.type = 'Shot'
            AND ea.attribute_value = 'Goal'
        )
        OR et.type = 'Own Goal For'
    GROUP BY
        e.match_id,
        e.team_id
),
match_summary AS (
    -- 2: one row per match with derived final result and other metrics
    SELECT
        m.match_id,
        m.season_id,
        COALESCE(hg.goals, 0) AS home_score,
        COALESCE(ag.goals, 0) AS away_score,
        COALESCE(hg.goals, 0) + COALESCE(ag.goals, 0) AS total_goals,
        ABS(COALESCE(hg.goals, 0) - COALESCE(ag.goals, 0)) AS goal_margin
    FROM
        Match m
        LEFT JOIN match_goals hg ON hg.match_id = m.match_id
        AND hg.team_id = m.home_team_id
        LEFT JOIN match_goals ag ON ag.match_id = m.match_id
        AND ag.team_id = m.away_team_id
),
match_attendance AS (
    -- 3: tickets actually scanned at the gate
    -- null becomes 0 via the left join + coalesce in the outer query
    SELECT
        match_id,
        COUNT(*) AS attendance
    FROM
        Ticket
    WHERE
        is_scanned = true
    GROUP BY
        match_id
)
SELECT
    s.season_id,
    s.season_name,
    d.division_id,
    d.name AS division_name,
    d.country AS division_country,
    COUNT(ms.match_id) AS total_matches,
    SUM(ms.total_goals) AS total_goals,
    ROUND(AVG(ms.total_goals) :: numeric, 2) AS avg_goals_per_game,
    -- largest goal margin in any single match this season
    MAX(ms.goal_margin) AS biggest_win_margin,
    -- max combined goals
    MAX(ms.total_goals) AS highest_scoring_match_goals,
    ROUND(AVG(COALESCE(ma.attendance, 0)) :: numeric, 0) AS avg_attendance
FROM
    Season s
    JOIN Division d ON d.division_id = s.division_id
    LEFT JOIN match_summary ms ON ms.season_id = s.season_id
    LEFT JOIN match_attendance ma ON ma.match_id = ms.match_id
GROUP BY
    s.season_id,
    s.season_name,
    d.division_id,
    d.name,
    d.country;

-- vw_utilization
CREATE VIEW vw_utilization AS WITH stadium_season_matches AS (
    -- 1: count matches hosted per (stadium, season)
    SELECT
        m.stadium_id,
        m.season_id,
        COUNT(*) AS matches_hosted
    FROM
        Match m
    GROUP BY
        m.stadium_id,
        m.season_id
),
ticket_stats AS (
    -- 2: per (stadium, season) ticket aggregates.
    -- we join Ticket with Match to map tickets back to a stadium and season
    -- left join keeps stadiums/seasons with zero tickets visible
    SELECT
        m.stadium_id,
        m.season_id,
        COUNT(t.ticket_id) AS tickets_issued,
        COUNT(t.ticket_id) FILTER (
            WHERE
                t.is_scanned
        ) AS tickets_scanned,
        AVG(t.price) AS avg_ticket_price,
        SUM(t.price) AS total_revenue
    FROM
        Match m
        LEFT JOIN Ticket t ON t.match_id = m.match_id
    GROUP BY
        m.stadium_id,
        m.season_id
)
SELECT
    st.stadium_id,
    st.name AS stadium_name,
    st.country,
    st.capacity,
    se.season_id,
    se.season_name,
    d.name AS division_name,
    ssm.matches_hosted,
    st.capacity * ssm.matches_hosted AS total_seats_available,
    COALESCE(ts.tickets_issued, 0) AS tickets_issued,
    COALESCE(ts.tickets_scanned, 0) AS tickets_scanned,
    -- null when no tickets were issued, otherwise the divide is safe
    CASE
        WHEN COALESCE(ts.tickets_issued, 0) = 0 THEN NULL
        ELSE ROUND(
            (ts.tickets_scanned :: numeric / ts.tickets_issued) * 100,
            2
        )
    END AS scan_rate_pct,
    CASE
        WHEN st.capacity * ssm.matches_hosted = 0 THEN NULL
        ELSE ROUND(
            (
                COALESCE(ts.tickets_issued, 0) :: numeric / (st.capacity * ssm.matches_hosted)
            ) * 100,
            2
        )
    END AS utilization_pct,
    ROUND(COALESCE(ts.avg_ticket_price, 0) :: numeric, 2) AS avg_ticket_price,
    COALESCE(ts.total_revenue, 0) AS total_revenue
FROM
    Stadium st -- inner join only shows stadiums that actually hosted matches in a season
    JOIN stadium_season_matches ssm ON ssm.stadium_id = st.stadium_id
    JOIN Season se ON se.season_id = ssm.season_id
    JOIN Division d ON d.division_id = se.division_id
    LEFT JOIN ticket_stats ts ON ts.stadium_id = st.stadium_id
    AND ts.season_id = ssm.season_id;


CREATE 
OR REPLACE VIEW vw_match_summary AS WITH goals AS (
    -- One row per goal scored, tagged with which side (home/away) scored it.
    SELECT
        e.match_id,
        e.team_id AS scoring_team_id
    FROM
        Event e
        JOIN Event_type et ON et.event_type_id = e.event_type_id
        JOIN Event_attribute ea ON ea.event_id = e.event_id
    WHERE
        et.type = 'Shot'
        AND ea.attribute_name = 'shot.outcome'
        AND ea.attribute_value = 'Goal'
),
goal_counts AS (
    SELECT
        m.match_id,
        COUNT(*) FILTER (
            WHERE
                g.scoring_team_id = m.home_team_id
        ) AS home_goals,
        COUNT(*) FILTER (
            WHERE
                g.scoring_team_id = m.away_team_id
        ) AS away_goals
    FROM
        "match" m
        LEFT JOIN goals g ON g.match_id = m.match_id
    GROUP BY
        m.match_id
),
cards AS (
    -- Counts both 'foul_committed.card' and 'bad_behaviour.card' attributes.
    SELECT
        e.match_id,
        COUNT(*) FILTER (
            WHERE
                ea.attribute_value IN ('Yellow Card', 'Second Yellow')
        ) AS yellow_cards,
        COUNT(*) FILTER (
            WHERE
                ea.attribute_value = 'Red Card'
        ) AS red_cards,
        COUNT(*) AS total_cards
    FROM
        Event e
        JOIN Event_attribute ea ON ea.event_id = e.event_id
    WHERE
        ea.attribute_name IN ('foul_committed.card', 'bad_behaviour.card')
    GROUP BY
        e.match_id
),
main_ref AS (
    -- The "main" referee per match. role='main' if available, else any.
    SELECT
        DISTINCT ON (rm.match_id) rm.match_id,
        rm.referee_id
    FROM
        Referee_match rm
    ORDER BY
        rm.match_id,
        (rm.role = 'main') DESC,
        rm.referee_match_id
)
SELECT
    m.match_id,
    m.match_date,
    s.season_id,
    s.season_name,
    d.division_id,
    d.name AS division_name,
    d.country AS division_country,
    ht.team_id AS home_team_id,
    ht.name AS home_team_name,
    at.team_id AS away_team_id,
    at.name AS away_team_name,
    st.stadium_id,
    st.name AS stadium_name,
    st.country AS stadium_country,
    r.referee_id,
    r.name AS referee_name,
    COALESCE(gc.home_goals, 0) AS home_goals,
    COALESCE(gc.away_goals, 0) AS away_goals,
    CASE
        WHEN COALESCE(gc.home_goals, 0) > COALESCE(gc.away_goals, 0) THEN 'H'
        WHEN COALESCE(gc.home_goals, 0) < COALESCE(gc.away_goals, 0) THEN 'A'
        ELSE 'D'
    END AS result,
    COALESCE(c.yellow_cards, 0) AS yellow_cards,
    COALESCE(c.red_cards, 0) AS red_cards,
    COALESCE(c.total_cards, 0) AS total_cards
FROM
    "match" m
    JOIN Season s ON s.season_id = m.season_id
    JOIN Division d ON d.division_id = s.division_id
    JOIN Team ht ON ht.team_id = m.home_team_id
    JOIN Team at ON at.team_id = m.away_team_id
    JOIN Stadium st ON st.stadium_id = m.stadium_id
    LEFT JOIN goal_counts gc ON gc.match_id = m.match_id
    LEFT JOIN cards c ON c.match_id = m.match_id
    LEFT JOIN main_ref mr ON mr.match_id = m.match_id
    LEFT JOIN Referee r ON r.referee_id = mr.referee_id;



CREATE
OR REPLACE VIEW vw_team_season_table AS WITH match_results AS (
    -- Reuse goal counts from vw_match_summary for consistency.
    SELECT
        match_id,
        season_id,
        home_team_id,
        away_team_id,
        home_goals,
        away_goals
    FROM
        vw_match_summary
),
team_match_rows AS (
    -- One row per (team, match) — UNION home and away perspectives.
    SELECT
        season_id,
        home_team_id AS team_id,
        home_goals AS goals_for,
        away_goals AS goals_against,
        CASE
            WHEN home_goals > away_goals THEN 'W'
            WHEN home_goals < away_goals THEN 'L'
            ELSE 'D'
        END AS result_letter
    FROM
        match_results
    UNION
    ALL
    SELECT
        season_id,
        away_team_id AS team_id,
        away_goals AS goals_for,
        home_goals AS goals_against,
        CASE
            WHEN away_goals > home_goals THEN 'W'
            WHEN away_goals < home_goals THEN 'L'
            ELSE 'D'
        END AS result_letter
    FROM
        match_results
)
SELECT
    s.season_id,
    s.season_name,
    d.division_id,
    d.name AS division_name,
    t.team_id,
    t.name AS team_name,
    COUNT(*) AS matches_played,
    COUNT(*) FILTER (
        WHERE
            tmr.result_letter = 'W'
    ) AS wins,
    COUNT(*) FILTER (
        WHERE
            tmr.result_letter = 'D'
    ) AS draws,
    COUNT(*) FILTER (
        WHERE
            tmr.result_letter = 'L'
    ) AS losses,
    COALESCE(SUM(tmr.goals_for), 0) AS goals_for,
    COALESCE(SUM(tmr.goals_against), 0) AS goals_against,
    COALESCE(SUM(tmr.goals_for) - SUM(tmr.goals_against), 0) AS goal_difference,
    3 * COUNT(*) FILTER (
        WHERE
            tmr.result_letter = 'W'
    ) + 1 * COUNT(*) FILTER (
        WHERE
            tmr.result_letter = 'D'
    ) AS points
FROM
    team_match_rows tmr
    JOIN Season s ON s.season_id = tmr.season_id
    JOIN Division d ON d.division_id = s.division_id
    JOIN Team t ON t.team_id = tmr.team_id
GROUP BY
    s.season_id,
    s.season_name,
    d.division_id,
    d.name,
    t.team_id,
    t.name;


CREATE
OR REPLACE VIEW vw_player_season_stats AS WITH lineup_per_player_season AS (
    -- The team a player primarily played for in this season (most appearances).
    SELECT
        l.player_id,
        m.season_id,
        l.team_id,
        COUNT(*) AS matches_played,
        COUNT(*) FILTER (
            WHERE
                l.is_starter
        ) AS starts,
        COUNT(*) FILTER (
            WHERE
                NOT l.is_starter
        ) AS sub_apps,
        ROW_NUMBER() OVER (
            PARTITION BY l.player_id,
            m.season_id
            ORDER BY
                COUNT(*) DESC
        ) AS rn
    FROM
        Lineup l
        JOIN "match" m ON m.match_id = l.match_id
    GROUP BY
        l.player_id,
        m.season_id,
        l.team_id
),
primary_team AS (
    SELECT
        player_id,
        season_id,
        team_id,
        matches_played,
        starts,
        sub_apps
    FROM
        lineup_per_player_season
    WHERE
        rn = 1
),
goals AS (
    SELECT
        e.player_id,
        m.season_id,
        COUNT(*) AS goals
    FROM
        Event e
        JOIN Event_type et ON et.event_type_id = e.event_type_id
        JOIN Event_attribute ea ON ea.event_id = e.event_id
        JOIN "match" m ON m.match_id = e.match_id
    WHERE
        et.type = 'Shot'
        AND ea.attribute_name = 'shot.outcome'
        AND ea.attribute_value = 'Goal'
    GROUP BY
        e.player_id,
        m.season_id
),
assists AS (
    -- A "goal assist" in StatsBomb is a Pass with pass.goal_assist = 'True'.
    SELECT
        e.player_id,
        m.season_id,
        COUNT(*) AS assists
    FROM
        Event e
        JOIN Event_type et ON et.event_type_id = e.event_type_id
        JOIN Event_attribute ea ON ea.event_id = e.event_id
        JOIN "match" m ON m.match_id = e.match_id
    WHERE
        et.type = 'Pass'
        AND ea.attribute_name = 'pass.goal_assist'
        AND ea.attribute_value IN ('True', 'true')
    GROUP BY
        e.player_id,
        m.season_id
),
yellow_cards AS (
    SELECT
        e.player_id,
        m.season_id,
        COUNT(*) AS yellow_cards
    FROM
        Event e
        JOIN Event_attribute ea ON ea.event_id = e.event_id
        JOIN "match" m ON m.match_id = e.match_id
    WHERE
        ea.attribute_name IN ('foul_committed.card', 'bad_behaviour.card')
        AND ea.attribute_value IN ('Yellow Card', 'Second Yellow')
    GROUP BY
        e.player_id,
        m.season_id
),
red_cards AS (
    SELECT
        e.player_id,
        m.season_id,
        COUNT(*) AS red_cards
    FROM
        Event e
        JOIN Event_attribute ea ON ea.event_id = e.event_id
        JOIN "match" m ON m.match_id = e.match_id
    WHERE
        ea.attribute_name IN ('foul_committed.card', 'bad_behaviour.card')
        AND ea.attribute_value = 'Red Card'
    GROUP BY
        e.player_id,
        m.season_id
)
SELECT
    p.player_id,
    p.name AS player_name,
    pt.season_id,
    s.season_name,
    pt.team_id,
    t.name AS team_name,
    pt.matches_played,
    pt.starts,
    pt.sub_apps,
    -- Minutes proxy: starters get 90, subs get 30
    (pt.starts * 90 + pt.sub_apps * 30) AS minutes_played_estimate,
    COALESCE(g.goals, 0) AS goals,
    COALESCE(a.assists, 0) AS assists,
    COALESCE(yc.yellow_cards, 0) AS yellow_cards,
    COALESCE(rc.red_cards, 0) AS red_cards
FROM
    primary_team pt
    JOIN Player p ON p.player_id = pt.player_id
    JOIN Season s ON s.season_id = pt.season_id
    JOIN Team t ON t.team_id = pt.team_id
    LEFT JOIN goals g ON g.player_id = pt.player_id
    AND g.season_id = pt.season_id
    LEFT JOIN assists a ON a.player_id = pt.player_id
    AND a.season_id = pt.season_id
    LEFT JOIN yellow_cards yc ON yc.player_id = pt.player_id
    AND yc.season_id = pt.season_id
    LEFT JOIN red_cards rc ON rc.player_id = pt.player_id
    AND rc.season_id = pt.season_id;