Grand Prix Analysis

SELECT * FROM 'winners.csv';

CREATE TABLE race_results (
    id INT PRIMARY KEY,
    grandprix VARCHAR(100),
    race_date DATE,
    winner VARCHAR(100),
    car VARCHAR(100),
    laps INT,
    race_time VARCHAR(50),
    namecode VARCHAR(10)
);

--Most successful driver by wins
SELECT winner, COUNT(*) AS wins
FROM winners.csv
GROUP BY winner
ORDER BY wins DESC;

--Top winning car brands
SELECT car, COUNT(*) AS total_wins
FROM winners.csv
GROUP BY car
ORDER BY total_wins DESC;

--Most races in a single year
SELECT EXTRACT(YEAR FROM "Date") AS year, COUNT(*) AS races
FROM 'winners.csv'
GROUP BY year
ORDER BY races DESC;

--Average number of laps per race and Driver-car combination with most wins
SELECT winner, car, COUNT(*) AS wins, ROUND(AVG(laps), 2) AS avg_laps
FROM winners.csv
GROUP BY winner, car
ORDER BY wins DESC;

--Which driver has won races across the most different car brands?
SELECT winner, COUNT(DISTINCT car) AS unique_cars
FROM winners.csv
GROUP BY winner
ORDER BY unique_cars DESC;

--Find the longest winning streak by any driver (consecutive races by date)
WITH ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY winner ORDER BY date) -
           ROW_NUMBER() OVER (ORDER BY date) AS streak_group
    FROM winners.csv
)
SELECT winner, COUNT(*) AS streak_length
FROM ranked
GROUP BY winner, streak_group
ORDER BY streak_length DESC
LIMIT 1;

--Which car brand dominated a specific decade (e.g., 2010s)?
SELECT car, COUNT(*) AS wins_in_2010s
FROM winners.csv
WHERE EXTRACT(YEAR FROM DATE) BETWEEN 2010 AND 2019
GROUP BY car
ORDER BY wins_in_2010s DESC;

--Driver with fastest average race time (excluding NULL or non-numeric times)
SELECT winner, AVG(
    SPLIT_PART(Time, ':', 1)::INT * 3600 +
    SPLIT_PART(Time, ':', 2)::INT * 60 +
    SPLIT_PART(Time, ':', 3)::DOUBLE PRECISION
) AS avg_seconds
FROM winners.csv
WHERE Time IS NOT NULL AND Time != '' AND SPLIT_PART(Time, ':', 3) != ''
GROUP BY winner
ORDER BY avg_seconds ASC;

-- Year-over-year win progression for the most successful driver
WITH top_driver AS (
    SELECT winner
    FROM "winners.csv"
    GROUP BY winner
    ORDER BY COUNT(*) DESC
    LIMIT 1
),
yearly_wins AS (
    SELECT 
        winner,
        car,
        EXTRACT(YEAR FROM date) AS year,
        COUNT(*) AS wins
    FROM "winners.csv"
    WHERE winner = (SELECT winner FROM top_driver)
    GROUP BY winner, car, year
)
SELECT 
    winner,
    year,
    STRING_AGG(DISTINCT car, ', ') AS car_brands,
    SUM(wins) AS total_wins
FROM yearly_wins
GROUP BY winner, year
ORDER BY year;