馃幆 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;