Chapter_3_Queries.sql

CREATE TABLE teams (
    team_id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE
);


INSERT INTO teams (name) VALUES ('Manchester United');
INSERT INTO teams (name) VALUES ('Liverpool');

CREATE TABLE players (
    player_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    team_id INTEGER REFERENCES teams(team_id) ON DELETE CASCADE,
    jersey_number INTEGER CHECK (jersey_number BETWEEN 1 AND 99),
    position VARCHAR(20) NOT NULL,        -- GK, DEF, MID, FWD
    detailed_position VARCHAR(30),        -- e.g., Centre-Back, Attacking Midfielder
    nationality VARCHAR(50) DEFAULT 'England',
    age INTEGER CHECK (age BETWEEN 16 AND 45),
    starter BOOLEAN DEFAULT TRUE          -- TRUE if usually in starting XI
);


INSERT INTO players (name, team_id, jersey_number, position, detailed_position, nationality, age, starter) VALUES
('André Onana',              1, 24, 'GK',  'Goalkeeper',            'Cameroon',      29, TRUE),
('Altay Bayındır',           1,  1, 'GK',  'Goalkeeper',            'Turkey',        27, FALSE),
('Tom Heaton',               1, 22, 'GK',  'Goalkeeper',            'England',       39, FALSE),

('Diogo Dalot',              1, 20, 'DEF', 'Right-Back',            'Portugal',      26, TRUE),
('Noussair Mazraoui',        1,  3, 'DEF', 'Right-Back',            'Morocco',       28, TRUE),
('Luke Shaw',                1, 23, 'DEF', 'Left-Back',             'England',       30, TRUE),
('Tyrell Malacia',           1, 12, 'DEF', 'Left-Back',             'Netherlands',   26, FALSE),
('Harry Maguire',            1,  5, 'DEF', 'Centre-Back',           'England',       32, TRUE),
('Lisandro Martínez',        1,  6, 'DEF', 'Centre-Back',           'Argentina',     27, TRUE),
('Matthijs de Ligt',         1,  4, 'DEF', 'Centre-Back',           'Netherlands',   26, TRUE),
('Victor Lindelöf',          1,  2, 'DEF', 'Centre-Back',           'Sweden',        31, FALSE),
('Jonny Evans',              1, 35, 'DEF', 'Centre-Back',           'Northern Ireland', 37, FALSE),

('Casemiro',                 1, 18, 'MID', 'Defensive Midfielder',  'Brazil',        33, TRUE),
('Manuel Ugarte',            1, 25, 'MID', 'Defensive Midfielder',  'Uruguay',       24, TRUE),
('Kobbie Mainoo',            1, 37, 'MID', 'Central Midfielder',    'England',       20, TRUE),
('Bruno Fernandes',          1,  8, 'MID', 'Attacking Midfielder',  'Portugal',      31, TRUE),
('Mason Mount',              1,  7, 'MID', 'Attacking Midfielder',  'England',       26, TRUE),
('Christian Eriksen',        1, 14, 'MID', 'Central Midfielder',    'Denmark',       33, FALSE),

('Marcus Rashford',          1, 10, 'FWD', 'Left Winger',           'England',       28, TRUE),
('Alejandro Garnacho',       1, 17, 'FWD', 'Left Winger',           'Argentina',     21, TRUE),
('Amad Diallo',              1, 16, 'FWD', 'Right Winger',          'Ivory Coast',   23, TRUE),
('Antony',                   1, 21, 'FWD', 'Right Winger',          'Brazil',        25, FALSE),
('Rasmus Højlund',           1,  9, 'FWD', 'Striker',               'Denmark',       22, TRUE),
('Joshua Zirkzee',           1, 11, 'FWD', 'Striker',               'Netherlands',   24, TRUE),
('Ethan Wheatley',           1, 44, 'FWD', 'Striker',               'England',       19, FALSE);


INSERT INTO players (name, team_id, jersey_number, position, detailed_position, nationality, age, starter) VALUES
('Alisson Becker',           2,  1, 'GK',  'Goalkeeper',            'Brazil',        33, TRUE),
('Caoimhín Kelleher',        2, 62, 'GK',  'Goalkeeper',            'Ireland',       27, FALSE),
('Vítězslav Jaroš',          2, 95, 'GK',  'Goalkeeper',            'Czech Republic',22, FALSE),

('Trent Alexander-Arnold',  2, 66, 'DEF', 'Right-Back',            'England',       27, TRUE),
('Conor Bradley',            2, 84, 'DEF', 'Right-Back',            'Northern Ireland',22, FALSE),
('Andrew Robertson',         2, 26, 'DEF', 'Left-Back',             'Scotland',      31, TRUE),
('Kostas Tsimikas',          2, 21, 'DEF', 'Left-Back',             'Greece',        29, FALSE),
('Virgil van Dijk',          2,  4, 'DEF', 'Centre-Back',           'Netherlands',   34, TRUE),
('Ibrahima Konaté',          2,  5, 'DEF', 'Centre-Back',           'France',        26, TRUE),
('Jarell Quansah',           2, 78, 'DEF', 'Centre-Back',           'England',       22, TRUE),
('Joe Gomez',                2,  2, 'DEF', 'Centre-Back',           'England',       28, FALSE),

('Ryan Gravenberch',         2, 38, 'MID', 'Defensive Midfielder',  'Netherlands',   23, TRUE),
('Alexis Mac Allister',      2, 10, 'MID', 'Central Midfielder',    'Argentina',     27, TRUE),
('Dominik Szoboszlai',       2,  8, 'MID', 'Attacking Midfielder',  'Hungary',       25, TRUE),
('Curtis Jones',             2, 17, 'MID', 'Central Midfielder',    'England',       24, TRUE),
('Wataru Endo',              2,  3, 'MID', 'Defensive Midfielder',  'Japan',         32, FALSE),
('Harvey Elliott',           2, 19, 'MID', 'Attacking Midfielder',  'England',       22, FALSE),

('Mohamed Salah',            2, 11, 'FWD', 'Right Winger',          'Egypt',         33, TRUE),
('Luis Díaz',                2,  7, 'FWD', 'Left Winger',           'Colombia',      28, TRUE),
('Cody Gakpo',               2, 18, 'FWD', 'Left Winger',           'Netherlands',   26, TRUE),
('Federico Chiesa',          2, 14, 'FWD', 'Right Winger',          'Italy',         28, TRUE),
('Darwin Núñez',             2,  9, 'FWD', 'Striker',               'Uruguay',       26, TRUE),
('Diogo Jota',               2, 20, 'FWD', 'Striker',               'Portugal',      29, TRUE),
('Jayden Danns',             2, 76, 'FWD', 'Striker',               'England',       19, FALSE),
('Ben Doak',                 2, 50, 'FWD', 'Right Winger',          'Scotland',      20, FALSE);

CREATE TABLE matches (
    match_id SERIAL PRIMARY KEY,
    home_team_id INTEGER REFERENCES teams(team_id),
    away_team_id INTEGER REFERENCES teams(team_id),
    match_date DATE NOT NULL,
    score_home INTEGER DEFAULT 0,
    score_away INTEGER DEFAULT 0,
    possession_home INTEGER,  -- Percentage (0-100)
    possession_away INTEGER,  -- Percentage (0-100)
    shots_home INTEGER DEFAULT 0,
    shots_away INTEGER DEFAULT 0,
    shots_on_target_home INTEGER DEFAULT 0,
    shots_on_target_away INTEGER DEFAULT 0,
    fouls_home INTEGER DEFAULT 0,
    fouls_away INTEGER DEFAULT 0
);


INSERT INTO matches (
    home_team_id, away_team_id, match_date,
    score_home, score_away,
    possession_home, possession_away,
    shots_home, shots_away,
    shots_on_target_home, shots_on_target_away,
    fouls_home, fouls_away
) VALUES (
    1, 2, '2025-12-08',
    2, 2,
    48, 52,
    12, 15,
    5, 7,
    14, 11
);


CREATE TABLE match_events (
    event_id SERIAL PRIMARY KEY,
    match_id INTEGER REFERENCES matches(match_id) ON DELETE CASCADE,
    event_type VARCHAR(20) NOT NULL,  -- e.g., 'goal', 'shot', 'foul', 'yellow_card', 'red_card'
    minute INTEGER NOT NULL CHECK (minute BETWEEN 0 AND 120),
    player_id INTEGER REFERENCES players(player_id),
    description TEXT
);


INSERT INTO match_events (match_id, event_type, minute, player_id, description)
VALUES (1, 'goal', 15, 19, 'Marcus Rashford scores a stunning opener from outside the box for Manchester United.');
INSERT INTO match_events (match_id, event_type, minute, player_id, description)
VALUES (1, 'goal', 30, 16, 'Bruno Fernandes doubles the lead with a penalty for Manchester United.');


INSERT INTO match_events (match_id, event_type, minute, player_id, description)
VALUES (1, 'goal', 60, 43, 'Mohamed Salah pulls one back with a clinical finish for Liverpool.');
INSERT INTO match_events (match_id, event_type, minute, player_id, description)
VALUES (1, 'goal', 75, 47, 'Darwin Nunez equalizes with a header from a corner for Liverpool.');

INSERT INTO match_events (match_id, event_type, minute, player_id, description)
VALUES (1, 'shot', 10, 20, 'Alejandro Garnacho shoots wide for Manchester United.');
INSERT INTO match_events (match_id, event_type, minute, player_id, description)
VALUES (1, 'shot', 20, 44, 'Luis Diaz forces a save from the keeper for Liverpool.');
INSERT INTO match_events (match_id, event_type, minute, player_id, description)
VALUES (1, 'foul', 25, 13, 'Casemiro commits a foul on Trent Alexander-Arnold.');
INSERT INTO match_events (match_id, event_type, minute, player_id, description)
VALUES (1, 'yellow_card', 25, 13, 'Casemiro booked for the foul.');
INSERT INTO match_events (match_id, event_type, minute, player_id, description)
VALUES (1, 'shot', 40, 23, 'Rasmus Hojlund hits the post for Manchester United.');
INSERT INTO match_events (match_id, event_type, minute, player_id, description)
VALUES (1, 'foul', 50, 33, 'Virgil van Dijk fouls Marcus Rashford.');
INSERT INTO match_events (match_id, event_type, minute, player_id, description)
VALUES (1, 'yellow_card', 50, 33, 'Virgil van Dijk receives a yellow card.');
INSERT INTO match_events (match_id, event_type, minute, player_id, description)
VALUES (1, 'shot', 65, 29, 'Trent Alexander-Arnold takes a free-kick, saved.');
INSERT INTO match_events (match_id, event_type, minute, player_id, description)
VALUES (1, 'shot', 80, 8, 'Harry Maguire heads over from a corner for Manchester United.');
INSERT INTO match_events (match_id, event_type, minute, player_id, description)
VALUES (1, 'foul', 85, 39, 'Dominik Szoboszlai commits a tactical foul.');
INSERT INTO match_events (match_id, event_type, minute, player_id, description)
VALUES (1, 'red_card', 90, 39, 'Dominik Szoboszlai sent off for a second yellow (hypothetical second offense).');


CREATE TABLE player_match_stats (
    stats_id SERIAL PRIMARY KEY,
    match_id INTEGER REFERENCES matches(match_id) ON DELETE CASCADE,
    player_id INTEGER REFERENCES players(player_id) ON DELETE CASCADE,
    minutes_played INTEGER CHECK (minutes_played BETWEEN 0 AND 120),
    goals INTEGER DEFAULT 0,
    assists INTEGER DEFAULT 0,
    xg DECIMAL(4,2) DEFAULT 0.00,                    -- Total expected goals
    xa DECIMAL(4,2) DEFAULT 0.00,                    -- Expected assists
    xg_first_half DECIMAL(4,2) DEFAULT 0.00,         -- xG in first half
    xg_second_half DECIMAL(4,2) DEFAULT 0.00,        -- xG in second half
    shots INTEGER DEFAULT 0,
    shots_on_target INTEGER DEFAULT 0,
    passes_completed INTEGER DEFAULT 0,
    passes_attempted INTEGER DEFAULT 0,
    tackles INTEGER DEFAULT 0,
    interceptions INTEGER DEFAULT 0,
    duels_won INTEGER DEFAULT 0,
    UNIQUE(match_id, player_id)  -- Ensures one row per player per match
);



INSERT INTO player_match_stats (
    match_id, player_id, minutes_played, goals, assists, 
    xg, xa, xg_first_half, xg_second_half,
    shots, shots_on_target, passes_completed, passes_attempted, 
    tackles, interceptions, duels_won
) VALUES

(1, 1, 90, 0, 0, 0.00, 0.05, 0.00, 0.00, 0, 0, 28, 35, 0, 0, 1),    -- André Onana
(1, 4, 90, 0, 0, 0.05, 0.10, 0.03, 0.02, 1, 0, 52, 61, 2, 1, 6),     -- Diogo Dalot
(1, 8, 90, 0, 0, 0.15, 0.08, 0.10, 0.05, 2, 1, 48, 55, 1, 2, 8),     -- Harry Maguire
(1, 9, 90, 0, 0, 0.00, 0.20, 0.00, 0.00, 0, 0, 58, 65, 3, 3, 10),    -- Lisandro Martínez
(1, 10, 90, 0, 0, 0.10, 0.15, 0.05, 0.05, 1, 0, 70, 78, 1, 2, 7),    -- Matthijs de Ligt
(1, 13, 90, 0, 0, 0.05, 0.12, 0.03, 0.02, 0, 0, 42, 50, 4, 2, 9),    -- Casemiro
(1, 16, 90, 1, 0, 0.76, 0.25, 0.76, 0.00, 3, 2, 65, 74, 1, 1, 5),    -- Bruno Fernandes (penalty 30')
(1, 15, 90, 0, 1, 0.30, 0.45, 0.18, 0.12, 2, 1, 55, 63, 2, 1, 6),    -- Kobbie Mainoo
(1, 19, 85, 1, 0, 0.85, 0.18, 0.85, 0.00, 4, 2, 32, 38, 0, 0, 4),    -- Marcus Rashford (goal 15')
(1, 20, 90, 0, 1, 0.45, 0.60, 0.30, 0.15, 3, 1, 28, 35, 1, 0, 7),    -- Alejandro Garnacho
(1, 23, 90, 0, 0, 0.68, 0.10, 0.45, 0.23, 3, 1, 18, 22, 0, 0, 5),    -- Rasmus Højlund


(1, 26, 90, 0, 0, 0.00, 0.08, 0.00, 0.00, 0, 0, 30, 38, 0, 0, 2),    -- Alisson Becker
(1, 29, 90, 0, 1, 0.18, 0.70, 0.05, 0.13, 3, 1, 82, 92, 2, 1, 6),    -- Trent Alexander-Arnold
(1, 33, 90, 0, 0, 0.08, 0.05, 0.02, 0.06, 1, 0, 68, 75, 2, 3, 11),   -- Virgil van Dijk
(1, 34, 90, 0, 0, 0.05, 0.12, 0.02, 0.03, 0, 0, 70, 78, 3, 4, 12),   -- Ibrahima Konaté
(1, 36, 90, 0, 0, 0.25, 0.30, 0.08, 0.17, 1, 0, 60, 68, 3, 2, 8),     -- Ryan Gravenberch
(1, 37, 90, 0, 0, 0.35, 0.40, 0.10, 0.25, 2, 1, 75, 84, 1, 1, 7),     -- Alexis Mac Allister
(1, 39, 88, 0, 0, 0.42, 0.55, 0.12, 0.30, 3, 1, 58, 65, 2, 1, 6),     -- Dominik Szoboszlai
(1, 43, 90, 1, 0, 0.95, 0.28, 0.15, 0.80, 5, 3, 40, 48, 0, 0, 5),     -- Mohamed Salah (goal 60')
(1, 44, 90, 0, 0, 0.60, 0.35, 0.18, 0.42, 4, 2, 35, 42, 1, 0, 8),     -- Luis Díaz
(1, 47, 90, 1, 0, 1.10, 0.15, 0.10, 1.00, 3, 2, 22, 28, 0, 0, 9),     -- Darwin Núñez (goal 75')
(1, 45, 75, 0, 1, 0.48, 0.42, 0.05, 0.43, 2, 1, 30, 36, 0, 0, 5);     -- Cody Gakpo


CREATE TABLE match_event_sequence (
    seq_id BIGSERIAL PRIMARY KEY,
    match_id INTEGER REFERENCES matches(match_id) ON DELETE CASCADE,
    event_type VARCHAR(30) NOT NULL,           -- pass, carry, shot, goal, foul, etc.
    minute INTEGER NOT NULL,
    second INTEGER DEFAULT 0,                  -- sub-minute timing
    player_id INTEGER REFERENCES players(player_id),
    team_id INTEGER REFERENCES teams(team_id),
    x_location DECIMAL(5,2),                   -- pitch x (0-100)
    y_location DECIMAL(5,2),                   -- pitch y (0-100)
    possession_id INTEGER NOT NULL,            -- groups events in same possession
    possession_team_id INTEGER REFERENCES teams(team_id),
    event_order_in_possession INTEGER NOT NULL, -- 1st, 2nd, 3rd... in possession
    is_progressive BOOLEAN DEFAULT FALSE,      -- moved ball significantly forward?
    is_in_buildup BOOLEAN DEFAULT FALSE,       -- started in own half?
    ends_with_shot BOOLEAN DEFAULT FALSE,      -- possession ended in a shot?
    ends_with_goal BOOLEAN DEFAULT FALSE       -- possession ended in a goal?
);


CREATE INDEX idx_sequence_possession 
    ON match_event_sequence (match_id, possession_id, event_order_in_possession);
CREATE INDEX idx_sequence_team 
    ON match_event_sequence (possession_team_id, match_id);
CREATE INDEX idx_sequence_type 
    ON match_event_sequence (event_type);
    

DELETE FROM match_event_sequence WHERE match_id = 1;


INSERT INTO match_event_sequence (
    match_id, event_type, minute, second, player_id, team_id,
    x_location, y_location, possession_id, possession_team_id,
    event_order_in_possession, is_progressive, is_in_buildup, ends_with_shot, ends_with_goal
) VALUES
-- Possession 1: Man United build-up → Rashford GOAL (15')
(1, 'pass',   14, 45, 9,  1, 25.0, 50.0, 1, 1, 1, TRUE,  TRUE,  TRUE, TRUE),   -- Lisandro Martínez
(1, 'carry',  14, 52, 15, 1, 45.0, 60.0, 1, 1, 2, TRUE,  TRUE,  TRUE, TRUE),   -- Kobbie Mainoo
(1, 'pass',   14, 58, 16, 1, 65.0, 30.0, 1, 1, 3, TRUE,  FALSE, TRUE, TRUE),   -- Bruno Fernandes
(1, 'shot',   15, 05, 19, 1, 88.0, 45.0, 1, 1, 4, TRUE,  FALSE, TRUE, TRUE),   -- Marcus Rashford GOAL

-- Possession 2: Liverpool counter → Díaz shot saved
(1, 'pass',   20, 10, 29, 2, 70.0, 80.0, 2, 2, 1, TRUE,  FALSE, TRUE, FALSE),  -- Trent Alexander-Arnold
(1, 'pass',   20, 12, 44, 2, 82.0, 20.0, 2, 2, 2, TRUE,  FALSE, TRUE, FALSE),  -- Luis Díaz
(1, 'shot',   20, 18, 44, 2, 92.0, 38.0, 2, 2, 3, FALSE, FALSE, TRUE, FALSE),

-- Possession 3: Man United → Bruno penalty GOAL (30')
(1, 'pass',   29, 40, 10, 1, 30.0, 70.0, 3, 1, 1, TRUE,  TRUE,  TRUE, TRUE),   -- Matthijs de Ligt
(1, 'pass',   29, 45, 13, 1, 50.0, 55.0, 3, 1, 2, TRUE,  FALSE, TRUE, TRUE),   -- Casemiro
(1, 'pass',   29, 50, 16, 1, 75.0, 40.0, 3, 1, 3, TRUE,  FALSE, TRUE, TRUE),   -- Bruno Fernandes
(1, 'shot',   30, 10, 16, 1, 88.0, 50.0, 3, 1, 4, FALSE, FALSE, TRUE, TRUE),   -- Bruno penalty GOAL

-- Possession 4: Liverpool comeback → Núñez header GOAL (75')
(1, 'pass',   74, 30, 29, 2, 40.0, 85.0, 4, 2, 1, TRUE,  TRUE,  TRUE, TRUE),   -- Trent long ball
(1, 'carry',  74, 38, 47, 2, 80.0, 50.0, 4, 2, 2, TRUE,  FALSE, TRUE, TRUE),   -- Darwin Núñez
(1, 'shot',   75, 02, 47, 2, 94.0, 48.0, 4, 2, 3, FALSE, FALSE, TRUE, TRUE);   -- Núñez header GOAL    
    
    
--SELECT * from teams;
--SELECT * from players;
--SELECT * from matches;
--SELECT * from match_events;
--SELECT * from player_match_stats;
--SELECT * FROM match_event_sequence;

SELECT
    h.name AS home_team,
    a.name AS away_team,
    m.score_home,
    m.score_away,
    m.possession_home,
    m.possession_away,
    m.shots_home,
    m.shots_away
FROM matches m
JOIN teams h ON m.home_team_id = h.team_id
JOIN teams a ON m.away_team_id = a.team_id
WHERE m.match_id = 1;


SELECT
    -- Basic info
    h.name AS home_team,
    a.name AS away_team,
    m.match_date,
    CONCAT(m.score_home, ' - ', m.score_away) AS final_score,

    -- Possession
    m.possession_home || '%' AS possession_home,
    m.possession_away || '%' AS possession_away,

    -- Total shots & shots on target
    m.shots_home AS total_shots_home,
    m.shots_away AS total_shots_away,
    m.shots_on_target_home,
    m.shots_on_target_away,

    -- Accuracy percentage (shots on target / total shots)
    ROUND(
        CASE 
            WHEN m.shots_home = 0 THEN 0 
            ELSE (m.shots_on_target_home::DECIMAL / m.shots_home) * 100 
        END, 1
    ) || '%' AS shot_accuracy_home,

    ROUND(
        CASE 
            WHEN m.shots_away = 0 THEN 0 
            ELSE (m.shots_on_target_away::DECIMAL / m.shots_away) * 100 
        END, 1
    ) || '%' AS shot_accuracy_away,

    -- Shot conversion rate (goals / total shots)
    ROUND(
        CASE 
            WHEN m.shots_home = 0 THEN 0 
            ELSE (m.score_home::DECIMAL / m.shots_home) * 100 
        END, 2
    ) || '%' AS conversion_rate_home,

    ROUND(
        CASE 
            WHEN m.shots_away = 0 THEN 0 
            ELSE (m.score_away::DECIMAL / m.shots_away) * 100 
        END, 2
    ) || '%' AS conversion_rate_away,

    -- Bonus: Goals per shot on target (clinical finishing indicator)
    ROUND(
        CASE 
            WHEN m.shots_on_target_home = 0 THEN 0 
            ELSE m.score_home::DECIMAL / m.shots_on_target_home 
        END, 2
    ) AS goals_per_shot_on_target_home,

    ROUND(
        CASE 
            WHEN m.shots_on_target_away = 0 THEN 0 
            ELSE m.score_away::DECIMAL / m.shots_on_target_away 
        END, 2
    ) AS goals_per_shot_on_target_away,

    -- Fouls for context
    m.fouls_home,
    m.fouls_away

FROM matches m
JOIN teams h ON m.home_team_id = h.team_id
JOIN teams a ON m.away_team_id = a.team_id
WHERE m.match_id = 1;

SELECT
    e.minute,
    e.event_type,
    p.name AS player,
    t.name AS team,
    e.description
FROM match_events e
JOIN players p ON e.player_id = p.player_id
JOIN teams t ON p.team_id = t.team_id
WHERE e.match_id = 1 AND e.event_type IN ('goal', 'yellow_card', 'red_card')
ORDER BY e.minute ASC;

SELECT
    p.name AS player,
    t.name AS team,
    COUNT(*) AS goals
FROM match_events e
JOIN players p ON e.player_id = p.player_id
JOIN teams t ON p.team_id = t.team_id
WHERE e.match_id = 1 AND e.event_type = 'goal'
GROUP BY p.name, t.name
ORDER BY goals DESC;

SELECT
    p.name AS player,
    t.name AS team,
    COUNT(*) AS total_shots
FROM match_events e
JOIN players p ON e.player_id = p.player_id
JOIN teams t ON p.team_id = t.team_id
WHERE e.match_id = 1 AND e.event_type IN ('shot', 'goal')
GROUP BY p.name, t.name
ORDER BY total_shots DESC
LIMIT 8;


SELECT 
    p.name,
    t.name AS team,
    ROUND(sub.xg_first_half::numeric, 2) AS xg_first_half,
    ROUND(sub.xg_second_half::numeric, 2) AS xg_second_half,
    ROUND((sub.xg_first_half + sub.xg_second_half)::numeric, 2) AS xg_total,
    ROUND(AVG(sub.xg_first_half + sub.xg_second_half) OVER (PARTITION BY p.player_id), 2) AS avg_xg_per_match,
    ROUND(
        SUM(sub.xg_first_half + sub.xg_second_half) OVER (
            PARTITION BY p.player_id 
            ORDER BY m.match_date 
            ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
        ), 2
    ) AS xg_moving_avg_last5
FROM (
    SELECT 
        player_id,
        match_id,
        SUM(xg_first_half) AS xg_first_half,
        SUM(xg_second_half) AS xg_second_half
    FROM player_match_stats
    GROUP BY player_id, match_id
) sub
JOIN players p ON sub.player_id = p.player_id
JOIN teams t ON p.team_id = t.team_id
JOIN matches m ON sub.match_id = m.match_id
WHERE p.name IN ('Marcus Rashford', 'Mohamed Salah', 'Bruno Fernandes', 'Darwin Núñez')
ORDER BY p.name;


WITH buildup AS (
    SELECT 
        possession_id,
        possession_team_id,
        MAX(event_order_in_possession) AS events_in_possession,
        BOOL_OR(ends_with_goal) AS scored_goal,
        BOOL_OR(ends_with_shot) AS ended_with_shot
    FROM match_event_sequence
    WHERE match_id = 1 AND event_type = 'pass'
    GROUP BY possession_id, possession_team_id
    HAVING COUNT(*) >= 3
)
SELECT 
    t.name AS team,
    COUNT(*) AS three_plus_pass_buildups,
    SUM(CASE WHEN scored_goal THEN 1 ELSE 0 END) AS goals_from_buildup,
    ROUND(100.0 * SUM(CASE WHEN scored_goal THEN 1 ELSE 0 END) / COUNT(*), 1) || '%' AS conversion_rate
FROM buildup b
JOIN teams t ON b.possession_team_id = t.team_id
WHERE b.ended_with_shot
GROUP BY t.name;


SELECT 
    t.name AS team,
    COUNT(*) AS progressive_buildups_from_own_half
FROM match_event_sequence e
JOIN teams t ON e.possession_team_id = t.team_id
WHERE e.match_id = 1
  AND e.is_in_buildup = TRUE
  AND e.event_type = 'pass'
  AND e.is_progressive = TRUE
GROUP BY t.name;