🎯 Consultas SQL sobre anuncios de autos usados

El siguiente conjunto de consultas SQL fue desarrollado con el objetivo de apoyar la toma de decisiones en una empresa del sector automotriz, específicamente en el análisis de anuncios de autos usados.

Mediante estas consultas, es posible:

- Identificar tendencias de mercado (precios, tipos de combustible, años de registro).

- Evaluar el comportamiento de los anuncios (duración activa, frecuencia, condición del vehículo).

- Detectar modelos y marcas con mayor rotación o popularidad.

- Medir el impacto de campañas A/B (en caso de tener campañas activas de marketing).

- Obtener información útil para optimizar estrategias de venta, compra o publicidad.

Estas consultas permiten transformar una base de datos cruda en información accionable, útil para áreas de marketing, ventas, compras o inteligencia comercial.

Estructura de las tablas de la base AutosBD

🚗 1. KPIs sobre autos y su oferta

Estas consultas son cruciales para obtener insights de negocio accionables. Me permiten entender el posicionamiento de precios por marca y modelo, identificar tendencias de consumo según el tipo de combustible, y reconocer los nichos de alto rendimiento del mercado, todo ello esencial para guiar estrategias de ventas, marketing y desarrollo de producto.

Precio promedio por marca y modelo

1. Precio promedio por modelo y marca
Calculo el precio promedio (AVG) por cada modelo y marca de los anuncios de autos. Utilizo una Common Table Expression (CTE) llamada promedio_por_modelo para agrupar y obtener estos promedios, y luego realizo LEFT JOINs para obtener los nombres de modelo y marca, mostrando finalmente los 10 modelos con el promedio más alto.
-- Precio promedio por marca y modelo

WITH 
promedio_por_modelo AS (
SELECT ROUND(AVG(AN.precio_eur), 2) AS Precio_promedio, 
	AU.model_id, 
    AU.marca_id
FROM anuncios AN
LEFT JOIN autos AU 
	ON AN.car_id = AU.Id
GROUP BY AU.model_id, AU.marca_id
ORDER BY AVG(AN.precio_eur) DESC
)
SELECT PPM.Precio_promedio, 
	MO.model,
    MA.nombre_marca AS marca
    FROM promedio_por_modelo PPM
    LEFT JOIN models MO 
		ON PPM.model_id = MO.model_id
	LEFT JOIN marcas MA
		ON PPM.marca_id = MA.marca_id	
	LIMIT 10; 

2. Precio promedio por marca
Determino el precio promedio (AVG) de los anuncios de autos por cada marca. Para ello, empleo una CTE (promedio_por_marca) que me permite realizar la agregación inicial, y luego un LEFT JOIN para asociar el ID de la marca con su nombre. El resultado final son las 10 marcas con el promedio de precio más elevado.
-- Precio promedio por marca    
WITH 
promedio_por_marca AS (
SELECT ROUND(AVG(AN.precio_eur), 2) AS Precio_promedio, 
    AU.marca_id
FROM anuncios AN
LEFT JOIN autos AU 
	ON AN.car_id = AU.Id
GROUP BY AU.marca_id
ORDER BY AVG(AN.precio_eur) DESC
)
SELECT PPM.Precio_promedio, 
    MA.nombre_marca AS marca
    FROM promedio_por_marca PPM
	LEFT JOIN marcas MA
		ON PPM.marca_id = MA.marca_id	
	LIMIT 10; 


3. % de Autos por Tipo de Combustible
Calculo el porcentaje de vehículos según su tipo de combustible. Primero, obtengo el conteo total de anuncios usando una CTE (total_anuncios). Posteriormente, realizo un RIGHT JOIN y un CROSS JOIN para vincular los autos con los anuncios y la tabla de totales, lo que me permite calcular la proporción de cada tipo de combustible.
-- % de autos diésel, gasolina, eléctricos, etc.
WITH total_anuncios AS (
    SELECT COUNT(*) AS total FROM anuncios
)
SELECT 
    AU.fuel_type,
    COUNT(AN.car_id) AS cantidad,
    ROUND(COUNT(AN.car_id) * 100.0 / TA.total, 3) AS porcentaje
FROM autos AU
RIGHT JOIN anuncios AN ON AU.Id = AN.car_id
CROSS JOIN total_anuncios TA
GROUP BY AU.fuel_type, TA.total;


4. Top 5 autos más potentes por Marca/Modelo
Identifico los 5 autos más potentes (potencia máxima en CV) que existen por modelo y marca. Utilizo una CTE (ranking) para agrupar y obtener esa potencia máxima, y luego aplico la función de ventana ROW_NUMBER() para asignarles un ranking en la salida final.
-- Top 5 autos más potentes por marca o modelo
WITH ranking AS (
SELECT 
    MO.model,
    MA.nombre_marca AS marca,
    MAX(A.potencia_CV) AS potencia_maxima
FROM autos A
JOIN models MO ON A.model_id = MO.model_id
JOIN marcas MA ON A.marca_id = MA.marca_id
GROUP BY MO.model, MA.nombre_marca
ORDER BY potencia_maxima DESC
LIMIT 5
)
SELECT 
	ROW_NUMBER() OVER (ORDER BY potencia_maxima DESC) AS Top,
	model, 
    marca, 
    potencia_maxima
FROM 
	ranking;

📊 2. KPIs sobre comportamiento de los anuncios

OCULTO PROVISIONALMENTE

Duración del anuncio (fechas)

5. Duración promedio del anuncio
Calcula la duración promedio en días de los anuncios, utilizando la función DATEDIFF() entre ultima_vez_visto y fecha_anuncio, con el resultado redondeado a dos decimales.
-- Duración promedio del anuncio
SELECT 
	ROUND(AVG(DATEDIFF(ultima_vez_visto, fecha_anuncio)),2) AS días_promedio_deL_anuncio
FROM anuncios;

6. Duración máxima del anuncio
Obtiene la duración máxima en días registrada para un anuncio, calculada mediante la función DATEDIFF() entre ultima_vez_visto y fecha_anuncio.
-- Duración máxima del anuncio
SELECT 
	MAX(DATEDIFF(ultima_vez_visto, fecha_anuncio)) AS días_maximos_deL_anuncio
FROM anuncios;


7. Tasa de anuncios con daños no reparados
Determina el porcentaje de anuncios con daños no reparados ('ja'). Emplea una agregación condicional (SUM(CASE WHEN ... THEN 1 ELSE 0 END)) dividida por el COUNT(*) total para obtener la proporción.
-- Tasa de anuncios con daños no reparados
SELECT 
	SUM(CASE
		WHEN daño_no_reparado = 'ja' THEN 1 
        ELSE 0 END) * 100.0 / COUNT(*) AS porcentaje_dañados
FROM anuncios;

8. Porcentaje de anuncios con prueba A/B "test" vs "control"
Calcula el porcentaje de anuncios asignados a la categoría "test" respecto al total de anuncios que pertenecen a las categorías "test" o "control", utilizando agregación condicional (SUM(CASE WHEN ...)) para el numerador y el denominador.
-- % de anuncios con prueba A/B "test" vs "control"  'unknown'
SELECT SUM(CASE 
			WHEN prueba_ab = "test" 
				THEN 1 ELSE 0 END)/
	SUM(CASE 
			WHEN prueba_ab = "test" OR prueba_ab = "control"
				THEN 1 ELSE 0 END) 
		AS "% anuncios test"
	FROM anuncios;


Objetivo: obtener métricas cuantitativas precisas mediante agregaciones, funciones de fecha y lógica condicional, específicamente para:

- Evaluar la eficiencia y duración de los anuncios (identificando promedios y máximos).
- Cuantificar el estado del inventario (tasa de vehículos con daños no reparados).
- Medir el rendimiento de las campañas A/B.

Meta final: proveer información exacta para la toma de decisiones sobre el inventario y las estrategias de marketing.

📅 3. KPIs temporales y de tendencias

Objetivo: analizar la evolución temporal de los anuncios de autos. 

- Identificar patrones de registro a nivel anual.
- Comprender tendencias de publicación mes a mes.

Esto nos permite obtener una visión clara de la estacionalidad y el crecimiento del inventario.

Cantidad de anuncios por año de registro

9. Cantidad de anuncios por año de registro
Esta consulta cuantifica el número total de anuncios registrados por año. Utiliza la función YEAR() para extraer el año de la fecha_anuncio y agrupa los resultados para obtener el conteo de anuncios por cada año fiscal.
-- Cantidad de anuncios por año de registro
SELECT 
	YEAR(fecha_anuncio) AS Año,
    COUNT(YEAR(fecha_anuncio)) AS cantidad_anuncios
FROM anuncios
GROUP BY YEAR(fecha_anuncio);



10. Tendencia de nuevos anuncios por mes
Esta consulta analiza la tendencia de nuevos anuncios por mes. Emplea DATE_FORMAT() para extraer el nombre del mes de fecha_anuncio, agrupa los resultados por mes y luego ordena el conteo de anuncios de forma descendente para identificar los meses con mayor volumen de publicación.
-- Tendencia de nuevos anuncios por mes
SELECT 
	DATE_FORMAT(fecha_anuncio, '%M') AS mes,
    COUNT(MONTH(fecha_anuncio)) AS cantidad_anuncios
FROM anuncios
GROUP BY mes
ORDER BY cantidad_anuncios DESC;

📍 4. KPIs geográficos (si usas el código postal)

🔹 Objetivo: Aplicar 1FN, 2FN y 3FN para eliminar redundancias y garantizar integridad:

1FN: Datos atómicos, sin repeticiones.

2FN: Dependencia completa de la clave primaria.

3FN: Eliminar dependencias transitivas.

Resultado: Base de datos optimizada, sin anomalías y fácil de escalar.

Top 5 mayores precios promedios por región

11. Precio promedio mayor por región
Esta consulta identifica las 5 regiones con el precio promedio (AVG) de anuncio más alto (precio_eur). Utiliza una función de ventana ROW_NUMBER() para rankear los resultados por codigo_postal, tras agrupar y ordenar por el precio promedio de forma descendente.
-- Precio promedio mayor por región
SELECT 
	ROW_NUMBER () OVER (ORDER BY AVG(precio_eur) DESC) AS Top, 
	codigo_postal,
    AVG(precio_eur) AS precio_promedio
FROM anuncios
GROUP BY codigo_postal
ORDER BY precio_promedio DESC
LIMIT 5;


Esta consulta es clave para la estrategia de negocio. Nos permite identificar rápidamente las regiones con mayor valor de mercado para anuncios de autos, lo cual es vital para optimizar la asignación de recursos y las campañas de marketing, y entender mejor el posicionamiento geográfico de nuestros productos.

5. Diseño de integridad relacional e indexación

- Se definieron claves foráneas para establecer relaciones entre tablas y garantizar la integridad referencial.

- Se crearon índices para acelerar búsquedas, filtros y joins.

Estas acciones mejoran el rendimiento de las consultas y aseguran una estructura de datos coherente y optimizada.

Creación de índices

-- Se crean los índices para las tablas

CREATE INDEX idx_autos_id ON autos(Id);
CREATE INDEX idx_marca_id ON marcas(marca_id);
CREATE INDEX idx_models_id ON models(model_id);
CREATE INDEX idx_vehicle_type ON vehicle_type(type_id);


-- Precio vs kilometraje por modelo/año.
WITH max_min AS 
(SELECT 
	MAX(kilometraje) AS ma, 
    MIN(kilometraje) AS mi 
    FROM 
		anuncios),
rango_km AS 
(SELECT CASE
		WHEN A.kilometraje 
			BETWEEN MM.mi AND MM.mi + (MM.ma/5)*1 
            THEN CONCAT(MM.mi, ' - ', ROUND(MM.mi + (MM.ma/5)*1, 0))
        WHEN A.kilometraje 
			BETWEEN MM.mi + (MM.ma/5)*1 AND MM.mi + (MM.ma/5)*2 
            THEN CONCAT(ROUND(MM.mi + (MM.ma/5)*1, 0), ' - ', ROUND(MM.mi + (MM.ma/5)*2, 0))
		WHEN A.kilometraje 
			BETWEEN MM.mi + (MM.ma/5)*2 AND MM.mi + (MM.ma/5)*3 
            THEN CONCAT(ROUND(MM.mi + (MM.ma/5)*2, 0), ' - ', ROUND(MM.mi + (MM.ma/5)*3, 0))
		WHEN A.kilometraje 
			BETWEEN MM.mi + (MM.ma/5)*3 AND MM.mi + (MM.ma/5)*4 
            THEN CONCAT(ROUND(MM.mi + (MM.ma/5)*3, 0), ' - ', ROUND(MM.mi + (MM.ma/5)*4, 0))
		ELSE 
			CONCAT(ROUND(MM.mi + (MM.ma/5)*4, 0), ' - ', ROUND(MM.mi + (MM.ma/5)*5, 0))
        END AS rango_kilometraje, 
        precio_eur
	FROM anuncios A
    CROSS JOIN max_min MM
    )
    SELECT 
		rango_kilometraje, 
        AVG(precio_eur) AS precio_promedio
        FROM
			rango_km
		GROUP BY rango_kilometraje
        ORDER BY precio_promedio DESC;