queries.sql

-- 1. Listado de los constructores que han participado en en una temporada ingresada, 
-- mostrando el nombre, total de puntos obtenidos, carreras corridas en la temporadas, 
-- el número de victorias conseguidas y el numero de podios. 
-- Ordenado por total de puntos de forma descendente.

SELECT 
	co.`name` AS nombre_constructor,
 	SUM(cr.points) AS total_puntos,
 	COUNT(r.raceId) AS cr_corridas,
  COUNT(CASE WHEN cs.`position` = 1 THEN 1 END) as victorias,
  COUNT(CASE WHEN cs.`position` BETWEEN 1 AND 3 THEN 1 END) as podios
FROM constructor_results cr, constructors co, races r, constructorstanding cs
WHERE cr.constructorId = co.constructorId
AND cr.raceId = r.raceId
AND cr.raceId = cs.raceId
AND cr.constructorId = cs.constructorId
AND r.`year` = 2008
GROUP BY co.`name`
ORDER BY total_puntos DESC;

-- 2. Listado de todos los pilotos con su nombre y contando las carreras en las que ha participado
-- cuantas victorias ha tenido, podios (3 primeros lugares), cuantas veces ha estado entre los primeros 10,
-- cuantas entre el 11 y 20 puesto, entre 21 y 30 y cuando de ultimo (mas de 30)
SELECT CONCAT(d.forename, ' ', d.surname) AS fullname,
    COUNT(q.raceId) AS cr_corridas,
    SUM(CASE WHEN q.`position` = 1 THEN 1 ELSE 0 END) AS victorias,
    SUM(CASE WHEN q.`position` BETWEEN 1 AND 3 THEN 1 ELSE 0 END) AS podios,
    SUM(CASE WHEN q.`position` BETWEEN 1 AND 10 THEN 1 ELSE 0 END) AS primeros_10,
    SUM(CASE WHEN q.`position` BETWEEN 11 AND 20 THEN 1 ELSE 0 END) AS p11_20,
    SUM(CASE WHEN q.`position` BETWEEN 21 AND 30 THEN 1 ELSE 0 END) AS p21_30,
    SUM(CASE WHEN q.`position` >=31 THEN 1 ELSE 0 END) AS ultimos
FROM drivers d 
JOIN qualifying q ON d.driverId = q.driverId
GROUP BY fullname
ORDER BY victorias DESC;

-- 3. Obtener el top 10 de pilotos con más victorias en toda la historia
-- Mostrar los 10 pilotos con mayor número de victorias, incluyendo su nombre completo y nacionalidad
SELECT 
    CONCAT(d.forename, ' ', d.surname) AS piloto,
    d.nationality AS nacionalidad,
    COUNT(d.driverId) AS total_victorias
FROM results r
INNER JOIN drivers d ON r.driverId = d.driverId
WHERE r.position = 1
GROUP BY d.driverId
HAVING total_victorias >= (
	SELECT
	   COUNT(r2.driverId) AS victorias
	FROM results r2
	WHERE r2.position = 1
	GROUP BY r2.driverId
	ORDER BY victorias DESC
	LIMIT 1 OFFSET 9
)
ORDER BY total_victorias DESC;

-- 4. Listar los circuitos que han albergado más Grandes Premios, 
-- Incluyendo nombre, ciudad, país y número total de carreras, primera carrera y última carrera
SELECT 
    ci.name AS circuito,
    ci.location AS ciudad,
    ci.country AS pais,
    COUNT(*) AS total_carreras,
    MIN(ra.year) AS primera_carrera,
    MAX(ra.year) AS ultima_carrera
FROM circuits ci
INNER JOIN races ra ON ci.circuitId = ra.circuitId
GROUP BY ci.circuitId, ci.name, ci.location, ci.country
ORDER BY total_carreras DESC;


-- 5. Pilotos más jóvenes en conseguir su primera victoria
-- Mostrar los 10 pilotos que ganaron su primera carrera siendo más jóvenes,
-- Incluir nombre completo, nacionalidad, nombre carrera, año de la carrera, fecha de nacimiento 
-- y edad que tenia el piloto en ese momento

SELECT 
    CONCAT(d.forename, ' ', d.surname) AS piloto,
    d.nationality AS nacionalidad,
    ra.name AS gran_premio,
    ra.year AS año,
    ra.date AS fecha_carrera,
    d.dob AS fecha_nacimiento,
    TIMESTAMPDIFF(YEAR, d.dob, ra.date) AS edad_primera_victoria
FROM results r
INNER JOIN drivers d ON r.driverId = d.driverId
INNER JOIN races ra ON r.raceId = ra.raceId
WHERE r.position = 1
AND ra.date = (
    SELECT MIN(ra2.date)
    FROM results r2
    INNER JOIN races ra2 ON r2.raceId = ra2.raceId
    WHERE r2.driverId = r.driverId AND r2.position = 1
)
ORDER BY edad_primera_victoria ASC
LIMIT 10;

-- 6. Las 10 Carreras con más abandonos por problemas de motor
-- Identificar las carreras con mayor número de abandonos por fallos del motor
-- Selecciona nombre, año, nombre del circuito y abandonos, ordenados por en forma descendente por abandonos
SELECT 
    ra.name AS gran_premio,
    ra.year AS año,
    ci.name AS circuito,
    ci.country AS pais,
    COUNT(*) AS abandonos_motor
FROM results r
INNER JOIN races ra ON r.raceId = ra.raceId
INNER JOIN circuits ci ON ra.circuitId = ci.circuitId
INNER JOIN status s ON r.statusId = s.statusId
WHERE LOWER(s.status) LIKE LOWER('%Engine%')
GROUP BY r.raceId, ra.name, ra.year, ci.name, ci.country
ORDER BY abandonos_motor DESC
LIMIT 10;

-- 7. Mostrar la progresion de un piloto durante una temporada especifica buscar por referencia de piloto
-- Seleccionar numero de la carrera, nombre de la carrera, fecha, posición en campeonato, 
-- puntos acumulados, posición carrera, puntos carrera
SELECT 
    ra.round AS carrera_numero,
    ra.name AS gran_premio,
    ra.date AS fecha,
    ds.position AS posicion_campeonato,
    ds.points AS puntos_acumulados,
    r.position AS posicion_carrera,
    r.points AS puntos_carrera
FROM driver_standings ds
INNER JOIN drivers d ON ds.driverId = d.driverId
INNER JOIN races ra ON ds.raceId = ra.raceId
INNER JOIN results r ON ra.raceId = r.raceId AND d.driverId = r.driverId
WHERE d.driverRef = 'hamilton' AND ra.year = 2008
ORDER BY ra.round;

-- 8. Listar el tiempo promedio de paradas en boxes por constructor en una temporada
-- Incluir nombre del constructor, total de paradas, tiempo promedio de paradas en ms, 
-- parada más rápida en ms parada más lenta en ms, tiempo promedio en segundos, 
-- ordenados por tiempo promedio de forma ascendente. 
-- Teniendo en cuenta que  que no todas las temporadas de los pits tienen registros
SELECT 
    c.name AS constructor,
    COUNT(*) AS total_paradas,
    AVG(ps.milliseconds) AS tiempo_promedio_ms,
    MIN(ps.milliseconds) AS parada_mas_rapida_ms,
    MAX(ps.milliseconds) AS parada_mas_lenta_ms,
    ROUND(AVG(ps.milliseconds) / 1000, 3) AS tiempo_promedio_segundos
FROM pit_stops ps
INNER JOIN races ra ON ps.raceId = ra.raceId
INNER JOIN results r ON ps.raceId = r.raceId AND ps.driverId = r.driverId
INNER JOIN constructors c ON r.constructorId = c.constructorId
WHERE ra.year = 2023
GROUP BY c.constructorId, c.name
ORDER BY tiempo_promedio_ms ASC;

-- 9. Pilotos con más podios sin haber ganado nunca
-- Seleccionar nombre completo, nacionalidad, podios sin victoria, segundos puestos, terceros puestos,
-- primera temporada, última temporada ordenados de forma descendente por podios sin victoria
SELECT 
    CONCAT(d.forename, ' ', d.surname) AS piloto,
    d.nationality AS nacionalidad,
    COUNT(CASE WHEN r.position IN (2,3) THEN 1 END) AS podios_sin_victoria,
    COUNT(CASE WHEN r.position = 2 THEN 1 END) AS segundos_puestos,
    COUNT(CASE WHEN r.position = 3 THEN 1 END) AS terceros_puestos,
    MIN(ra.year) AS primera_temporada,
    MAX(ra.year) AS ultima_temporada
FROM results r
INNER JOIN drivers d ON r.driverId = d.driverId
INNER JOIN races ra ON r.raceId = ra.raceId
WHERE d.driverId NOT IN (
    SELECT DISTINCT driverId 
    FROM results 
    WHERE position = 1
)
AND r.position IN (2, 3)
GROUP BY r.driverId
ORDER BY podios_sin_victoria DESC;

-- 10. Análisis de pole positions y conversión a victoria
-- Mostrar pilotos con más pole positions y su porcentaje de conversión a victoria
SELECT 
    CONCAT(d.forename, ' ', d.surname) AS piloto,
    COUNT(r.grid) AS pole_positions,
    COUNT(CASE WHEN r.position = 1 AND q.position = 1 THEN 1 END) AS poles_convertidas_victoria,
    ROUND(
        (COUNT(CASE WHEN r.position = 1 AND q.position = 1 THEN 1 END) * 100.0) / 
        COUNT(q.qualifyId), 2
    ) AS porcentaje_conversion
FROM qualifying q
INNER JOIN drivers d ON q.driverId = d.driverId
INNER JOIN results r ON q.raceId = r.raceId AND q.driverId = r.driverId
WHERE r.grid = 1
GROUP BY q.driverId, d.forename, d.surname
HAVING pole_positions >= 5
ORDER BY pole_positions DESC;