CREATE TABLE "Country" (
    country_id INTEGER PRIMARY KEY AUTOINCREMENT,
    country_name TEXT NOT NULL,
    country_code TEXT NOT NULL UNIQUE,
    continent TEXT NOT NULL
);

CREATE TABLE "Venue" (
    venue_id INTEGER PRIMARY KEY AUTOINCREMENT,
    venue_name TEXT NOT NULL,
    city TEXT NOT NULL,
    country_id INTEGER,
    capacity INTEGER,
    FOREIGN KEY (country_id) REFERENCES Country (country_id)
);

CREATE TABLE "Sport" (
    sport_id INTEGER PRIMARY KEY AUTOINCREMENT,
    sport_name TEXT NOT NULL UNIQUE,
    category TEXT NOT NULL  
);

CREATE TABLE "Athlete" (
    athlete_id INTEGER PRIMARY KEY AUTOINCREMENT,
    athlete_name TEXT NOT NULL,
    birth_date DATE NOT NULL,
    gender TEXT,
    country_id INTEGER,
    FOREIGN KEY (country_id) REFERENCES Country (country_id)
);

CREATE TABLE "Event" (
    event_id INTEGER PRIMARY KEY AUTOINCREMENT,
    event_name TEXT NOT NULL,
    sport_id INTEGER,
    event_type TEXT NOT NULL,  
    FOREIGN KEY (sport_id) REFERENCES Sport (sport_id)
);

CREATE TABLE "Stats" (
    stat_id INTEGER PRIMARY KEY AUTOINCREMENT,
    athlete_id INTEGER,
    event_id INTEGER,
    score REAL NOT NULL,  
    FOREIGN KEY (event_id) REFERENCES Event (event_id),
    FOREIGN KEY (athlete_id) REFERENCES Athlete (athlete_id)
);

CREATE TABLE "Medal" (
    medal_id INTEGER PRIMARY KEY AUTOINCREMENT,
    athlete_id INTEGER,
    event_id INTEGER,
    medal_type TEXT CHECK(medal_type IN ('Gold', 'Silver', 'Bronze')),
    FOREIGN KEY (athlete_id) REFERENCES Athlete (athlete_id),
    FOREIGN KEY (event_id) REFERENCES Event (event_id)
);

INSERT INTO Country (country_name, country_code, continent) VALUES
('United States', 'USA', 'North America'),
('Canada', 'CAN', 'North America'),
('Mexico', 'MEX', 'North America'),
('Brazil', 'BRA', 'South America'),
('Argentina', 'ARG', 'South America'),
('Chile', 'CHI', 'South America'),
('United Kingdom', 'GBR', 'Europe'),
('France', 'FRA', 'Europe'),
('Germany', 'GER', 'Europe'),
('Spain', 'ESP', 'Europe'),
('Italy', 'ITA', 'Europe'),
('Russia', 'RUS', 'Europe'),
('China', 'CHN', 'Asia'),
('Japan', 'JPN', 'Asia'),
('South Korea', 'KOR', 'Asia'),
('India', 'IND', 'Asia'),
('Australia', 'AUS', 'Oceania'),
('New Zealand', 'NZL', 'Oceania'),
('South Africa', 'RSA', 'Africa'),
('Nigeria', 'NGA', 'Africa'),
('Kenya', 'KEN', 'Africa'),
('Egypt', 'EGY', 'Africa'),
('Morocco', 'MAR', 'Africa'),
('Turkey', 'TUR', 'Europe/Asia'),
('Saudi Arabia', 'KSA', 'Asia'),
('Israel', 'ISR', 'Asia'),
('Ukraine', 'UKR', 'Europe'),
('Sweden', 'SWE', 'Europe'),
('Norway', 'NOR', 'Europe'),
('Denmark', 'DEN', 'Europe'),
('Finland', 'FIN', 'Europe'),
('Poland', 'POL', 'Europe'),
('Portugal', 'POR', 'Europe'),
('Greece', 'GRE', 'Europe'),
('Czech Republic', 'CZE', 'Europe'),
('Hungary', 'HUN', 'Europe'),
('Romania', 'ROU', 'Europe'),
('Bulgaria', 'BUL', 'Europe'),
('Netherlands', 'NED', 'Europe'),
('Belgium', 'BEL', 'Europe'),
('Switzerland', 'SUI', 'Europe'),
('Austria', 'AUT', 'Europe'),
('Croatia', 'CRO', 'Europe'),
('Serbia', 'SRB', 'Europe'),
('Slovakia', 'SVK', 'Europe'),
('Slovenia', 'SLO', 'Europe'),
('Kazakhstan', 'KAZ', 'Asia'),
('Uzbekistan', 'UZB', 'Asia'),
('Iran', 'IRN', 'Asia'),
('Iraq', 'IRQ', 'Asia'),
('Thailand', 'THA', 'Asia'),
('Vietnam', 'VNM', 'Asia'),
('Malaysia', 'MAS', 'Asia'),
('Indonesia', 'INA', 'Asia'),
('Philippines', 'PHI', 'Asia'),
('Singapore', 'SIN', 'Asia'),
('Pakistan', 'PAK', 'Asia'),
('Bangladesh', 'BAN', 'Asia'),
('Qatar', 'QAT', 'Asia');

INSERT INTO Venue (venue_name, city, country_id, capacity) VALUES
('Olympic Stadium', 'Tokyo', 13, 68000),
('National Aquatics Center', 'Beijing', 12, 17000),
('Maracanã Stadium', 'Rio de Janeiro', 4, 78838),
('Wembley Stadium', 'London', 7, 90000),
('Stade de France', 'Paris', 8, 81338),
('Olympiastadion', 'Berlin', 9, 74475),
('Camp Nou', 'Barcelona', 10, 99354),
('Stadio Olimpico', 'Rome', 11, 70634),
('Fisht Olympic Stadium', 'Sochi', 6, 47659),
('Melbourne Cricket Ground', 'Melbourne', 17, 100024),
('Lusail Iconic Stadium', 'Lusail', 60, 88000),
('Estadio Monumental', 'Buenos Aires', 5, 70074),
('BC Place', 'Vancouver', 2, 54320),
('Madison Square Garden', 'New York', 1, 20789),
('Bird’s Nest Stadium', 'Beijing', 12, 91000),
('Ellis Park Stadium', 'Johannesburg', 19, 62000),
('National Stadium', 'Warsaw', 31, 58500),
('Friends Arena', 'Stockholm', 28, 54000),
('Santiago Bernabéu Stadium', 'Madrid', 10, 81044),
('Allianz Arena', 'Munich', 9, 75000);

INSERT INTO Sport (sport_name, category) VALUES
('Athletics', 'Track & Field'),
('Swimming', 'Aquatics'),
('Gymnastics', 'Artistic'),
('Basketball', 'Team Sport'),
('Football (Soccer)', 'Team Sport'),
('Tennis', 'Racket Sport'),
('Boxing', 'Combat Sport'),
('Wrestling', 'Combat Sport'),
('Weightlifting', 'Strength Sport'),
('Cycling', 'Endurance Sport'),
('Table Tennis', 'Racket Sport'),
('Badminton', 'Racket Sport'),
('Volleyball', 'Team Sport'),
('Archery', 'Precision Sport'),
('Equestrian', 'Equestrian'),
('Fencing', 'Combat Sport'),
('Judo', 'Combat Sport'),
('Taekwondo', 'Combat Sport'),
('Shooting', 'Precision Sport'),
('Rowing', 'Endurance Sport');

INSERT INTO Athlete (athlete_name, birth_date, gender, country_id) VALUES
('Usain Bolt', '1986-08-21', 'Male', 24), 
('Michael Phelps', '1985-06-30', 'Male', 1), 
('Simone Biles', '1997-03-14', 'Female', 1), 
('Allyson Felix', '1985-11-18', 'Female', 1), 
('Katie Ledecky', '1997-03-17', 'Female', 1), 
('Neymar', '1992-02-05', 'Male', 4), 
('Lionel Messi', '1987-06-24', 'Male', 5), 
('Serena Williams', '1981-09-26', 'Female', 1), 
('Novak Djokovic', '1987-05-22', 'Male', 43), 
('Roger Federer', '1981-08-08', 'Male', 41), 
('LeBron James', '1984-12-30', 'Male', 1), 
('Cristiano Ronaldo', '1985-02-05', 'Male', 32), 
('Mo Farah', '1983-03-23', 'Male', 7), 
('Gabby Douglas', '1995-12-31', 'Female', 1), 
('Naomi Osaka', '1997-10-16', 'Female', 13), 
('Rafael Nadal', '1986-06-03', 'Male', 10), 
('Simona Halep', '1991-09-27', 'Female', 38), 
('Shelly-Ann Fraser-Pryce', '1986-12-27', 'Female', 24), 
('Yelena Isinbayeva', '1982-06-03', 'Female', 12), 
('Caster Semenya', '1991-01-07', 'Female', 19), 
('Eliud Kipchoge', '1984-11-05', 'Male', 20), 
('Dina Asher-Smith', '1995-12-04', 'Female', 7), 
('Sun Yang', '1991-12-01', 'Male', 12), 
('Andy Murray', '1987-05-15', 'Male', 7), 
('Alina Zagitova', '2002-05-18', 'Female', 12), 
('Kylian Mbappe', '1998-12-20', 'Male', 8), 
('Megan Rapinoe', '1985-07-05', 'Female', 1), 
('Tom Daley', '1994-05-21', 'Male', 7), 
('Kohei Uchimura', '1989-01-03', 'Male', 13), 
('Kevin Durant', '1988-09-29', 'Male', 1), 
('Caeleb Dressel', '1996-08-16', 'Male', 1), 
('Simone Manuel', '1996-08-02', 'Female', 1), 
('Paul Pogba', '1993-03-15', 'Male', 8), 
('Sifan Hassan', '1993-01-01', 'Female', 31), 
('Zlatan Ibrahimović', '1981-10-03', 'Male', 28), 
('Marc Márquez', '1993-02-17', 'Male', 10), 
('Romain Bardet', '1990-11-09', 'Male', 8), 
('Egan Bernal', '1997-01-13', 'Male', 16), 
('Marta Vieira da Silva', '1986-02-19', 'Female', 4), 
('Sergio Ramos', '1986-03-30', 'Male', 10), 
('Mesut Özil', '1988-10-15', 'Male', 9), 
('Yohan Blake', '1989-12-26', 'Male', 24), 
('Wayde van Niekerk', '1992-07-15', 'Male', 19), 
('Mireia Belmonte', '1990-11-10', 'Female', 10), 
('Monica Puig', '1993-09-27', 'Female', 45), 
('Caroline Wozniacki', '1990-07-11', 'Female', 30), 
('Jessica Ennis-Hill', '1986-01-28', 'Female', 7), 
('Karim Benzema', '1987-12-19', 'Male', 8), 
('Ma Long', '1988-10-20', 'Male', 12), 
('Ayumu Hirano', '1998-11-29', 'Male', 13), 
('P.V. Sindhu', '1995-07-05', 'Female', 15), 
('Armand Duplantis', '1999-11-10', 'Male', 28), 
('Pernille Blume', '1994-05-14', 'Female', 30), 
('Dafne Schippers', '1992-06-15', 'Female', 40), 
('Luka Modric', '1985-09-09', 'Male', 40), 
('Rudy Gobert', '1992-06-26', 'Male', 8), 
('Viktor Axelsen', '1994-01-04', 'Male', 30), 
('Petra Kvitová', '1990-03-08', 'Female', 30);

INSERT INTO Event (event_name, sport_id, event_type) VALUES
('100m Sprint', 1, 'Individual'),
('200m Sprint', 1, 'Individual'),
('Marathon', 1, 'Individual'),
('4x100m Relay', 1, 'Team'),
('100m Freestyle', 2, 'Individual'),
('200m Freestyle', 2, 'Individual'),
('4x100m Freestyle Relay', 2, 'Team'),
('Gymnastics All-Around', 3, 'Individual'),
('Gymnastics Team', 3, 'Team'),
('Men’s Basketball', 4, 'Team'),
('Women’s Basketball', 4, 'Team'),
('Men’s Football', 5, 'Team'),
('Women’s Football', 5, 'Team'),
('Men’s Singles Tennis', 6, 'Individual'),
('Women’s Singles Tennis', 6, 'Individual'),
('Men’s Boxing - Heavyweight', 7, 'Individual'),
('Women’s Boxing - Flyweight', 7, 'Individual'),
('Men’s Wrestling - Freestyle', 8, 'Individual'),
('Women’s Wrestling - Freestyle', 8, 'Individual'),
('Weightlifting - Men’s 85kg', 9, 'Individual'),
('Weightlifting - Women’s 75kg', 9, 'Individual');

INSERT INTO Stats (athlete_id, event_id, score) VALUES
(1, 1, 9.58),  
(1, 2, 19.19),  
(2, 5, 47.51),  
(2, 6, 1.42),  
(3, 8, 62.366),  
(3, 9, 176.330),  
(4, 4, 40.82),  
(5, 7, 3.52),  
(6, 12, 2),  
(7, 12, 3),  
(8, 14, 2),  
(9, 14, 3),  
(10, 14, 1),  
(11, 10, 28),  
(12, 13, 2),  
(13, 19, 43.44),  
(14, 8, 60.132),  
(15, 14, 2),  
(16, 14, 3),  
(17, 20, 187),  
(18, 1, 10.74),  
(19, 3, '00:02:04.56'), 
(20, 1, 9.80),  
(21, 15, 60.12),  
(22, 6, 1.43),  
(23, 14, 3),  
(24, 9, 180.000),  
(25, 11, 4),  
(26, 18, 1.42),  
(27, 7, 3.43),  
(28, 17, 5.52),  
(29, 10, 32),  
(30, 5, 48.30),  
(31, 5, 48.60),  
(32, 11, 2),  
(33, 16, '00:02:06.34'), 
(34, 12, 2),  
(35, 4, 41.73),  
(36, 9, 3),  
(37, 1, 9.79),  
(38, 12, 1),  
(39, 5, 48.50),  
(40, 7, 3.53),  
(41, 1, 10.12),  
(42, 20, '00:01:59.99'), 
(43, 5, 47.80),  
(44, 14, 2),  
(45, 19, 44.33),  
(46, 19, 44.12),  
(47, 12, 3),  
(48, 6, 1.42),  
(49, 11, 2),  
(50, 19, 43.98),  
(51, 2, 47.70),  
(52, 5, 48.10),  
(53, 20, '00:02:02.57'), 
(54, 9, 180.000),  
(55, 10, 29),  
(56, 6, 1.40),  
(57, 20, '00:02:00.01'), 
(58, 6, 1.42),  
(59, 6, 1.41),  
(60, 20, '00:01:59.50'); 


INSERT INTO Medal (athlete_id, event_id, medal_type) VALUES
(1, 1, 'Gold'),  
(1, 2, 'Gold'),  
(2, 5, 'Gold'),  
(2, 6, 'Gold'),  
(3, 8, 'Gold'),  
(4, 4, 'Silver'),  
(5, 7, 'Gold'),  
(6, 12, 'Gold'),  
(7, 12, 'Silver'),  
(8, 14, 'Gold'),  
(9, 14, 'Gold'),  
(10, 14, 'Gold'),  
(11, 10, 'Bronze'),  
(12, 13, 'Gold'),  
(13, 19, 'Gold'),  
(14, 8, 'Silver'),  
(15, 14, 'Silver'),  
(16, 14, 'Gold'),  
(17, 20, 'Bronze'),  
(18, 1, 'Gold'),  
(19, 3, 'Gold'),  
(20, 1, 'Gold'),  
(21, 15, 'Bronze'),  
(22, 6, 'Gold'),  
(23, 14, 'Bronze'),  
(24, 9, 'Gold'),  
(25, 11, 'Silver'),  
(26, 18, 'Bronze'),  
(27, 7, 'Gold'),  
(28, 17, 'Gold'),  
(29, 10, 'Silver'),  
(30, 5, 'Gold'),  
(31, 5, 'Gold'),  
(32, 11, 'Silver'),  
(33, 16, 'Gold'),  
(34, 12, 'Silver'),  
(35, 4, 'Bronze'),  
(36, 9, 'Silver'),  
(37, 1, 'Gold'),  
(38, 12, 'Bronze'),  
(39, 5, 'Gold'),  
(40, 7, 'Silver'),  
(41, 1, 'Silver'),  
(42, 20, 'Gold'),  
(43, 5, 'Silver'),  
(44, 14, 'Gold'),  
(45, 19, 'Gold'),  
(46, 19, 'Silver'),  
(47, 12, 'Gold'),  
(48, 6, 'Bronze'),  
(49, 11, 'Gold'),  
(50, 19, 'Gold'),  
(51, 2, 'Gold'),  
(52, 5, 'Silver'),  
(53, 20, 'Gold'),  
(54, 9, 'Gold'),  
(55, 10, 'Bronze'),  
(56, 6, 'Silver'),  
(57, 20, 'Bronze'),  
(58, 6, 'Gold'),  
(59, 6, 'Silver'),  
(60, 20, 'Gold');

-- WITH athlete_scores AS (
--     SELECT
--         a.athlete_id,
--         a.athlete_name,
--         c.country_name,
--         AVG(s.score) AS average_score,
--         COUNT(s.event_id) AS event_count
--     FROM
--         Athlete a
--         JOIN Stats s ON a.athlete_id = s.athlete_id
--         JOIN Country c ON a.country_id = c.country_id
--     GROUP BY
--         a.athlete_id,
--         a.athlete_name,
--         c.country_name
--     HAVING
--         COUNT(s.event_id) > 1
-- )
-- SELECT
--     athlete_name,
--     country_name,
--     average_score
-- FROM
--     athlete_scores
-- ORDER BY
--     average_score DESC
-- LIMIT 5;

-- Overachievers
-- WITH event_avg AS (
--     SELECT
--         e.event_id,
--         e.event_name,
--         AVG(s.score) AS avg_score
--     FROM
--         Stats s
--         JOIN Event e ON s.event_id = e.event_id
--     GROUP BY
--         e.event_id,
--         e.event_name
-- ),
-- gold_medalists AS (
--     SELECT
--         m.event_id,
--         a.athlete_name,
--         s.score
--     FROM
--         Medal m
--         JOIN Athlete a ON m.athlete_id = a.athlete_id
--         JOIN Stats s ON m.athlete_id = s.athlete_id AND m.event_id = s.event_id
--     WHERE
--         m.medal_type = 'Gold'
-- )
-- SELECT
--     ea.event_name,
--     gm.athlete_name AS gold_medalist,
--     gm.score AS gold_score,
--     ea.avg_score,
--     ROUND(((gm.score - ea.avg_score) / ea.avg_score) * 100, 2) AS percentage_exceed
-- FROM
--     event_avg ea
--     JOIN gold_medalists gm ON ea.event_id = gm.event_id
-- WHERE
--     ((gm.score - ea.avg_score) / ea.avg_score) > 0.20;

-- top countries
-- WITH country_medals AS (
--     SELECT
--         c.continent,
--         c.country_name,
--         COUNT(m.medal_id) AS total_medals
--     FROM
--         Country c
--         JOIN Athlete a ON c.country_id = a.country_id
--         JOIN Medal m ON a.athlete_id = m.athlete_id
--     GROUP BY
--         c.continent,
--         c.country_name
-- ),
-- ranked_countries AS (
--     SELECT
--         continent,
--         country_name,
--         total_medals,
--         RANK() OVER (
--             PARTITION BY continent
--             ORDER BY total_medals DESC
--         ) AS rank
--     FROM
--         country_medals
-- )
-- SELECT
--     continent,
--     country_name,
--     total_medals
-- FROM
--     ranked_countries
-- WHERE
--     rank <= 3
-- ORDER BY
--     continent,
--     rank;

-- top venues (TODO)
-- SELECT
--     v.venue_name,
--     v.city,
--     COUNT(DISTINCT sp.category) AS number_of_categories,
--     GROUP_CONCAT(DISTINCT sp.category) AS categories_hosted
-- FROM
--     Venue v
--     JOIN Event e ON v.venue_id = e.event_id  -- Assuming events are assigned to venues
--     JOIN Sport sp ON e.sport_id = sp.sport_id
-- GROUP BY
--     v.venue_id,
--     v.venue_name,
--     v.city
-- HAVING
--     COUNT(DISTINCT sp.category) >= 1;

-- top sports
-- WITH sport_medals AS (
--     SELECT
--         sp.sport_name,
--         COUNT(DISTINCT e.event_id) AS total_events,
--         COUNT(m.medal_id) AS total_medals,
--         AVG(medals_per_event.medal_count) AS avg_medals_per_event
--     FROM
--         Sport sp
--         JOIN Event e ON sp.sport_id = e.sport_id
--         LEFT JOIN (
--             SELECT
--                 event_id,
--                 COUNT(medal_id) AS medal_count
--             FROM
--                 Medal
--             GROUP BY
--                 event_id
--         ) medals_per_event ON e.event_id = medals_per_event.event_id
--         LEFT JOIN Medal m ON e.event_id = m.event_id
--     GROUP BY
--         sp.sport_id,
--         sp.sport_name
-- )
-- SELECT
--     sport_name,
--     total_events,
--     total_medals,
--     avg_medals_per_event
-- FROM
--     sport_medals
-- ORDER BY
--     avg_medals_per_event DESC
-- LIMIT 3;

-- most active athlete
-- WITH sport_event_counts AS (
--     SELECT
--         sp.sport_id,
--         sp.sport_name,
--         COUNT(DISTINCT e.event_id) AS total_events
--     FROM
--         Sport sp
--         JOIN Event e ON sp.sport_id = e.sport_id
--     GROUP BY
--         sp.sport_id,
--         sp.sport_name
-- ),
-- athlete_event_counts AS (
--     SELECT
--         a.athlete_id,
--         a.athlete_name,
--         sp.sport_id,
--         sp.sport_name,
--         COUNT(DISTINCT s.event_id) AS events_participated
--     FROM
--         Athlete a
--         JOIN Stats s ON a.athlete_id = s.athlete_id
--         JOIN Event e ON s.event_id = e.event_id
--         JOIN Sport sp ON e.sport_id = sp.sport_id
--     GROUP BY
--         a.athlete_id,
--         a.athlete_name,
--         sp.sport_id,
--         sp.sport_name
-- )
-- SELECT
--     aec.athlete_name,
--     aec.sport_name,
--     aec.events_participated
-- FROM
--     athlete_event_counts aec
--     JOIN sport_event_counts sec ON aec.sport_id = sec.sport_id
-- WHERE
--     aec.events_participated = sec.total_events;

-- successful events
-- WITH event_participants AS (
--     SELECT
--         e.event_id,
--         e.event_name,
--         COUNT(DISTINCT s.athlete_id) AS total_participants
--     FROM
--         Event e
--         JOIN Stats s ON e.event_id = s.event_id
--     GROUP BY
--         e.event_id,
--         e.event_name
-- ),
-- event_medalists AS (
--     SELECT
--         e.event_id,
--         COUNT(DISTINCT m.athlete_id) AS total_medalists
--     FROM
--         Event e
--         JOIN Medal m ON e.event_id = m.event_id
--     GROUP BY
--         e.event_id
-- )
-- SELECT
--     ep.event_name,
--     ep.total_participants,
--     em.total_medalists,
--     ROUND((em.total_medalists * 1.0 / ep.total_participants) * 100, 2) AS percent_medalists
-- FROM
--     event_participants ep
--     JOIN event_medalists em ON ep.event_id = em.event_id
-- WHERE
--     em.total_medalists > ep.total_participants / 2
-- ORDER BY
--     em.total_medalists DESC
-- LIMIT 3;

-- lowest score winners
-- WITH event_scores AS (
--     SELECT
--         e.event_id,
--         e.event_name,
--         s.athlete_id,
--         s.score,
--         CASE WHEN m.medal_id IS NOT NULL THEN 'Medalist' ELSE 'Non-Medalist' END AS medal_status
--     FROM
--         Stats s
--         JOIN Event e ON s.event_id = e.event_id
--         LEFT JOIN Medal m ON s.athlete_id = m.athlete_id AND s.event_id = m.event_id
-- ),
-- average_scores AS (
--     SELECT
--         event_id,
--         event_name,
--         medal_status,
--         AVG(score) AS avg_score
--     FROM
--         event_scores
--     GROUP BY
--         event_id,
--         event_name,
--         medal_status
-- )
-- SELECT
--     m.event_name,
--     m.avg_score AS medalist_avg_score,
--     n.avg_score AS non_medalist_avg_score
-- FROM
--     average_scores m
--     JOIN average_scores n ON m.event_id = n.event_id AND m.medal_status = 'Medalist' AND n.medal_status = 'Non-Medalist'
-- WHERE
--     m.avg_score < n.avg_score * 0.80;

-- most medals for country
-- WITH athlete_medals AS (
--     SELECT
--         a.athlete_id,
--         a.athlete_name,
--         c.country_name,
--         COUNT(m.medal_id) AS total_medals
--     FROM
--         Athlete a
--         JOIN Medal m ON a.athlete_id = m.athlete_id
--         JOIN Country c ON a.country_id = c.country_id
--     GROUP BY
--         a.athlete_id,
--         a.athlete_name,
--         c.country_name
-- ),
-- max_total_medals AS (
--     SELECT
--         MAX(total_medals) AS max_medals
--     FROM
--         athlete_medals
-- )
-- SELECT
--     am.athlete_name,
--     am.country_name,
--     am.total_medals
-- FROM
--     athlete_medals am
--     CROSS JOIN max_total_medals mtm
-- WHERE
--     am.total_medals = mtm.max_medals;

-- WITH event_avg_scores AS (
--     SELECT
--         e.event_id,
--         AVG(s.score) AS event_avg_score
--     FROM
--         Event e
--         JOIN Stats s ON e.event_id = s.event_id
--     GROUP BY
--         e.event_id
-- ),
-- overall_avg_score AS (
--     SELECT AVG(score) AS overall_avg FROM Stats
-- ),
-- events_above_avg AS (
--     SELECT
--         e.event_id
--     FROM
--         event_avg_scores e
--         CROSS JOIN overall_avg_score o
--     WHERE
--         e.event_avg_score > o.overall_avg
-- )
-- SELECT
--     AVG(v.capacity) AS average_venue_capacity
-- FROM
--     Venue v
--     JOIN Event e ON v.venue_id = e.event_id
--     WHERE
--         e.event_id IN (SELECT event_id FROM events_above_avg);

-- WITH sport_medals AS (
--     SELECT
--         sp.sport_id,
--         sp.sport_name,
--         COUNT(m.medal_id) AS total_medals,
--         SUM(CASE WHEN m.medal_type = 'Gold' THEN 1 ELSE 0 END) AS gold_medals
--     FROM
--         Sport sp
--         JOIN Event e ON sp.sport_id = e.sport_id
--         JOIN Medal m ON e.event_id = m.event_id
--     GROUP BY
--         sp.sport_id,
--         sp.sport_name
-- )
-- SELECT
--     sport_name,
--     total_medals,
--     gold_medals,
--     ROUND((gold_medals * 1.0 / total_medals) * 100, 2) AS gold_medal_percentage
-- FROM
--     sport_medals
-- WHERE
--     (gold_medals * 1.0 / total_medals) > 0.50
-- ORDER BY sport_name;

-- best athletes
-- WITH event_max_scores AS (
--     SELECT
--         s.event_id,
--         MAX(s.score) AS max_score
--     FROM
--         Stats s
--     GROUP BY
--         s.event_id
-- ),
-- athlete_records AS (
--     SELECT
--         a.athlete_name,
--         e.event_name,
--         s.score
--     FROM
--         Stats s
--         JOIN Athlete a ON s.athlete_id = a.athlete_id
--         JOIN Event e ON s.event_id = e.event_id
--         JOIN event_max_scores em ON s.event_id = em.event_id AND s.score = em.max_score
-- )
-- SELECT
--     athlete_name,
--     event_name,
--     score AS record_score
-- FROM
--     athlete_records
-- ORDER BY athlete_name;

-- top sports
-- WITH event_avg_scores AS (
--     SELECT
--         e.event_id,
--         e.event_name,
--         sp.category,
--         AVG(s.score) AS avg_score
--     FROM
--         Event e
--         JOIN Sport sp ON e.sport_id = sp.sport_id
--         JOIN Stats s ON e.event_id = s.event_id
--     GROUP BY
--         e.event_id,
--         e.event_name,
--         sp.category
-- ),
-- category_max_scores AS (
--     SELECT
--         category,
--         MAX(avg_score) AS max_avg_score
--     FROM
--         event_avg_scores
--     GROUP BY
--         category
-- )
-- SELECT
--     eas.category,
--     eas.event_name,
--     eas.avg_score
-- FROM
--     event_avg_scores eas
--     JOIN category_max_scores cms ON eas.category = cms.category AND eas.avg_score = cms.max_avg_score;


-- WITH event_scores AS (
--     SELECT
--         e.sport_id,
--         e.event_id,
--         AVG(s.score) AS avg_score,
--         e.event_id AS event_order
--     FROM
--         Event e
--         JOIN Stats s ON e.event_id = s.event_id
--     GROUP BY
--         e.sport_id,
--         e.event_id
-- ),
-- sport_event_scores AS (
--     SELECT
--         sp.sport_name,
--         MIN(es.event_order) AS first_event_id,
--         MAX(es.event_order) AS last_event_id
--     FROM
--         event_scores es
--         JOIN Sport sp ON es.sport_id = sp.sport_id
--     GROUP BY
--         sp.sport_id,
--         sp.sport_name
-- ),
-- sport_avg_scores AS (
--     SELECT
--         ses.sport_name,
--         (SELECT avg_score FROM event_scores WHERE event_id = ses.first_event_id) AS first_avg_score,
--         (SELECT avg_score FROM event_scores WHERE event_id = ses.last_event_id) AS last_avg_score
--     FROM
--         sport_event_scores ses
-- )
-- SELECT
--     sport_name,
--     first_avg_score,
--     last_avg_score,
--     ROUND(((last_avg_score - first_avg_score) / first_avg_score) * 100, 2) AS percentage_increase
-- FROM
--     sport_avg_scores
-- WHERE
--     last_avg_score > first_avg_score;

WITH event_participants AS (
    SELECT
        e.event_id,
        COUNT(DISTINCT s.athlete_id) AS total_participants
    FROM
        Event e
        JOIN Stats s ON e.event_id = s.event_id
    GROUP BY
        e.event_id
),
event_medalists AS (
    SELECT
        e.event_id,
        COUNT(DISTINCT m.athlete_id) AS total_medalists
    FROM
        Event e
        JOIN Medal m ON e.event_id = m.event_id
    GROUP BY
        e.event_id
),
full_medal_events AS (
    SELECT
        ep.event_id
    FROM
        event_participants ep
        JOIN event_medalists em ON ep.event_id = em.event_id
    WHERE
        ep.total_participants = em.total_medalists
),
venue_event_counts AS (
    SELECT
        v.venue_name,
        v.city,
        COUNT(fme.event_id) AS full_medal_events
    FROM
        Venue v
        JOIN Event e ON v.venue_id = e.event_id  -- Assuming events are associated with venues via event_id = venue_id
        JOIN full_medal_events fme ON e.event_id = fme.event_id
    GROUP BY
        v.venue_name,
        v.city
)
SELECT
    venue_name,
    city,
    full_medal_events
FROM
    venue_event_counts
ORDER BY
    full_medal_events DESC;