-- -----------------------------------------------------------------------------------------------------------------------
-- ----------- Limpieza de datos  -----------------------------------------------------
-- -----------------------------------------------------------------------------------------------------------------------

# 1. Crear tabla linkedin_ofertas

CREATE TABLE linkedin_ofertas (
  id_oferta INT PRIMARY KEY,
  fecha_actualizacion datetime,
  nombre_empresa varchar(200) ,
  fecha_busqueda_oferta_linkedin datetime ,
  fecha_publicacion_oferta date ,
  ubicacion_oferta varchar(200),
  search_id_oferta int ,
  titulo_oferta varchar(200),
  fecha_actualizacion_sp datetime
)

# 2. Modificar las restricciones de la fecha ejecutuando la siguiente sentencia:

SET @@SESSION.sql_mode='ALLOW_INVALID_DATES';

# Tambien yendo a MYSQL Preferences -> MYSQL
# en SQL_MODE to be used in generated scripts: quitar las opciones de ZERO_DATES

# 3. Definir la query que va a dejar los datos como queremos.

INSERT INTO linkedin_data.linkedin_ofertas

SELECT 
id as id_oferta,
DATE_FORMAT(STR_TO_DATE(_fivetran_synced,"%Y-%m-%d %H:%i:%s"),'%Y-%m-%d %H:%i:%s') as fecha_actualizacion,
company_name as nombre_empresa,
DATE_FORMAT(STR_TO_DATE(date,"%Y-%m-%d %H:%i:%s"),'%Y-%m-%d %H:%i:%s') as fecha_busqueda_oferta_linkedin,
date_published as fecha_publicacion_oferta,
location as ubicacion_oferta,
searches as search_id_oferta,
title as titulo_oferta,
NOW() AS fecha_actualizacion_sp
FROM linkedin_data.raw_linkedin_results
WHERE _fivetran_synced is not null

# 4. Creamos SP con la query

#DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `update_table_linkedin_ofertas`()
BEGIN       
   INSERT INTO linkedin_data.linkedin_ofertas (id_oferta,fecha_actualizacion, nombre_empresa, fecha_busqueda_oferta_linkedin,fecha_publicacion_oferta,pais_oferta,search_id_oferta,titulo_oferta,fecha_actuailzacion_sp)
	SELECT 
		id as id_oferta,
		DATE_FORMAT(STR_TO_DATE(_fivetran_synced,"%Y-%m-%d %H:%i:%s"),'%Y-%m-%d %H:%i:%s') as fecha_actualizacion,
		company_name as nombre_empresa,
		DATE_FORMAT(STR_TO_DATE(date,"%Y-%m-%d %H:%i:%s"),'%Y-%m-%d %H:%i:%s') as fecha_busqueda_oferta_linkedin,
		date_published as fecha_publicacion_oferta,
		location as pais_oferta,
		searches as search_id_oferta,
		title as titulo_oferta
		FROM linkedin_data.raw_linkedin_results 
        where _fivetran_synced is not null AND id not in (SELECT id_oferta FROM linkedin_data.linkedin_ofertas);
END

# 5. Creamos un evento para ejecutar el SP de forma diaria 

CREATE 
EVENT `update_table_linkedin_ofertas`
ON SCHEDULE EVERY 1 DAY 
STARTS TIMESTAMP(NOW() + INTERVAL 1 MINUTE) 
DO CALL update_table_linkedin_ofertas();

# 6. Ver eventos

SHOW EVENTS

# 7.Ver código del evento
SHOW CREATE EVENT update_table_linkedin_ofertas

 # Recursos : <https://dev.mysql.com/doc/refman/8.0/en/alter-event.html>

 ------PARTE II - Crear busquedas

# 1. Crear tabla linkedin_busquedas

CREATE TABLE linkedin_busquedas (
  id_busqueda  INT PRIMARY KEY,
  fecha_busqueda datetime ,
  fecha_actualizacion datetime,
  keyword_busqueda varchar(200) ,
  pais_busqueda varchar(200),
  n_resultados_busqueda int,
  fecha_actualizacion_sp datetime 
)

# 2. Modificar las restricciones de la fecha ejecutuando la siguiente sentencia:

SET @@SESSION.sql_mode='ALLOW_INVALID_DATES';

# Tambien yendo a MYSQL Preferences -> MYSQL
# en SQL_MODE to be used in generated scripts: quitar las opciones de ZERO_DATES

# 3. Definir la query que va a dejar los datos como queremos.

SELECT    
	id as id_busqueda,
	timestamp(STR_TO_DATE(date,"%Y-%m-%d %H:%i:%s")) as fecha_busqueda_1, -- posible solucion
	DATE_FORMAT(STR_TO_DATE(date,"%Y-%m-%d %H:%i:%s"),'%Y-%m-%d %H:%i:%s') as fecha_busqueda,
	DATE_FORMAT(STR_TO_DATE(_fivetran_synced,"%Y-%m-%d %H:%i:%s"),'%Y-%m-%d %H:%i:%s') as fecha_actualizacion,   
	keyword as keyword_busqueda,   location as pais_busqueda, 
   cast(REPLACE(REPLACE(n_results,",",""),"+","") as UNSIGNED) as n_resultados_busqueda  
	FROM linkedin_data.raw_linkedin_searches
      WHERE _fivetran_synced is not null AND id not in (SELECT id_busqueda FROM linkedin_data.linkedin_busquedas);

# 4. Creamos SP con la query

#DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `update_table_linkedin_busquedas`()
BEGIN       
INSERT INTO linkedin_data.linkedin_busquedas (id_busqueda, fecha_busqueda, fecha_actualizacion, keyword_busqueda, pais_busqueda, n_resultados_busqueda)  
	SELECT    
	id as id_busqueda,
	DATE_FORMAT(STR_TO_DATE(date,"%Y-%m-%d %H:%i:%s"),'%Y-%m-%d %H:%i:%s') as fecha_busqueda,
	DATE_FORMAT(STR_TO_DATE(_fivetran_synced,"%Y-%m-%d %H:%i:%s"),'%Y-%m-%d %H:%i:%s') as fecha_actualizacion,   
	keyword as keyword_busqueda,   location as pais_busqueda,   
	cast(REPLACE(REPLACE(n_results,",",""),"+","") as UNSIGNED) as n_resultados_busqueda   
	FROM linkedin_data.raw_linkedin_searches         
	WHERE _fivetran_synced is not null AND id not in (SELECT id_busqueda FROM linkedin_data.linkedin_busquedas)

END

# 5. Creamos un evento para ejecutar el SP

# CREAMOS UN EJECUTADOR DEL SP CON EVENTOS
CREATE 
EVENT `update_table_linkedin_busquedas`
ON SCHEDULE EVERY 1 DAY 
STARTS TIMESTAMP(NOW() + INTERVAL 1 MINUTE) 
DO CALL update_table_linkedin_busquedas();

# 6. Ver eventos
SHOW EVENTS

# 7. Ver el codigo de ese evento que creamos
# Ver código del evento
SHOW CREATE EVENT update_table_linkedin_busquedas

 # Recursos : <https://dev.mysql.com/doc/refman/8.0/en/alter-event.html>

-- -----------------------------------------------------------------------------------------------------------------------
-- -----------  Análisis exploratorio - validacion de datos  -----------------------------------------------------
-- -----------------------------------------------------------------------------------------------------------------------

# Para validar datos siempre debemos contrarrestrarlos con. la fuente original.
# Para eso vamos a linkedin y chequeamos

# 1. Chequeo que podemos hacer es ver cantidad de ofertas por día si tiene un sentido
SELECT
fecha_publicacion_oferta,
count(*)
FROM linkedin_data.linkedin_ofertas r
GROUP BY fecha_publicacion_oferta
ORDER BY fecha_publicacion_oferta DESC

-- -----------------------------------------------------------------------------------------------------------------------
-- -----------  Análisis Linkedin data  -----------------------------------------------------
-- -----------------------------------------------------------------------------------------------------------------------
# 1. ¿Cúales son las empresas con mayor cantidad de ofertas?

SELECT
	nombre_empresa,
	count(*) as ofertas
FROM linkedin_data.linkedin_ofertas
group by nombre_empresa
ORDER BY count(*) desc

# 2. ¿Que cantidad de ofertas tenemos en la tabla por ubicación?

SELECT 
	ubicacion_oferta,
	count(*) AS ofertas -- Cada registro de la tabla de resultados es una oferta de trabajo
FROM linkedin_data.linkedin_ofertas
group by ubicacion_oferta
order by count(*) desc

# 3. ¿Cúal es la media de días de publicación de las ofertas por país?

SELECT
pais_busqueda,
ROUND(AVG(DATEDIFF(DATE(b.fecha_busqueda),DATE(o.fecha_publicacion_oferta))),0) AS dias_desde_publicado
FROM linkedin_data.linkedin_ofertas o
LEFT JOIN linkedin_data.linkedin_busquedas b on b.id_busqueda = o.search_id_oferta
GROUP BY pais_busqueda
ORDER BY dias_desde_publicado desc

# 4. ¿Que cantidad de ofertas tenemos por día publicados?

SELECT
fecha_publicacion_oferta,
count(*)
FROM linkedin_data.linkedin_ofertas o
GROUP BY fecha_publicacion_oferta
ORDER BY fecha_publicacion_oferta DESC

# 5. ¿Cúales son los top 10 títulos de roles que se usan para publicar ofertas?

SELECT
titulo_oferta,
count(*) as titulo_veces
FROM linkedin_data.linkedin_ofertas 
group by titulo_oferta
order by count(*) desc
limit 10

# 6. Cúales con las 5 ubicaciones con mayor cantidad de ofertas activas

SELECT 
ubicacion_oferta,
count(*) AS ofertas_activas
FROM (
		SELECT *,
	   DATEDIFF(DATE(CURRENT_DATE()),DATE(fecha_actualizacion)) as oferta,
		CASE WHEN DATEDIFF(DATE(CURRENT_DATE()),DATE(fecha_actualizacion))>0 THEN 0 ELSE 1 END AS  oferta_Activa
		FROM linkedin_data.linkedin_ofertas
		where titulo_oferta is not null 
		Order by fecha_actualizacion desc ) ofertas_activas
WHERE oferta_Activa=1
group by ubicacion_oferta
order by count(*) desc

# 7. ¿Cuantas ofertas de trabajo hay combinando keyowrd con título oferta?
# ¿Puedes devolver la cantidad agregando por ambos campos?

SELECT 
keyword_busqueda,
titulo_oferta,
count(*) as total_ofertas
FROM linkedin_data.linkedin_ofertas o
LEFT JOIN linkedin_data.linkedin_busquedas b on o.search_id_oferta = b.id_busqueda
GROUP BY 
keyword_busqueda,
titulo_oferta
ORDER BY total_ofertas desc

# 8. ¿Cuantos puestos tenemos como junior, puedes traer la cantidad por título
# de oferta?

SELECT 
titulo_oferta,
count(*) as total_ofertas
FROM linkedin_data.linkedin_ofertas o
WHERE (titulo_oferta lIKE '%junior%' OR titulo_oferta LIKE '%Jr%')
GROUP BY 
titulo_oferta
ORDER BY total_ofertas desc

# 9. ¿ Puedes ahora devolver la cantidad de ofertas con el título junior,
# pero por país? 

SELECT 
pais_busqueda,
count(*) as total_ofertas
FROM linkedin_data.linkedin_ofertas o
LEFT JOIN  linkedin_data.linkedin_busquedas b on b.id_busqueda=search_id_oferta
WHERE (titulo_oferta lIKE '%junior%' OR titulo_oferta LIKE '%Jr%')
GROUP BY 
pais_busqueda
ORDER BY total_ofertas desc

# 10. Podemos saber la cantidad de ofertas publicadas por mes y keyword?
# ¿ Que meses son mas top y con que keywords?

SELECT 
MONTHNAME(fecha_publicacion_oferta) as mes_publicacion_oferta,
keyword_busqueda,
count(*)
FROM linkedin_data.linkedin_ofertas o
INNER JOIN linkedin_data.linkedin_busquedas b on o.search_id_oferta = b.id_busqueda
GROUP BY mes_publicacion_oferta,keyword_busqueda
ORDER BY mes_publicacion_oferta,count(*) DESC