Compartir tecnología

한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina

El tiempo límite para la prueba escrita para la contratación de subcontratación de grupos de análisis de datos de cuentas de vídeo es de 45 minutos.

La pregunta se basa en la estructura de 3 tablas. Escriba el código SQL específico para resolver el problema (definición de categoría divertida: clasificación de video o clasificación del creador de video como "divertida").

Pregunta 1: Genere una tabla dws de categoría de comedia para analizar los 3 videos recientes del autor: genere los 3 videos publicados recientemente por cada creador de video user_id, la exposición de la semana pasada y el número total de me gusta en los campos de salida: Creador de video; user_id, número de usuarios expuestos, número de usuarios que me gusta, número de exposiciones, número de me gusta

SELECT
	vu.video_user_id AS user_id,
	count( DISTINCT uva.video_id ) AS video_count,
	sum( CASE WHEN uva.action_type = 1 THEN 1 ELSE 0 END ) AS exposure_user_count,
	sum( CASE WHEN uva.action_type = 2 THEN 1 ELSE 0 END ) AS like_user_count,
	sum( CASE WHEN uva.action_type = 1 THEN 1 ELSE 0 END ) AS exposeure_count,
	sum( CASE WHEN uva.action_type = 2 THEN 1 ELSE 0 END ) AS like_count 
FROM
	t_user_video_action_d uva
	JOIN t_video_d v ON uva.video_id = v.video_id
	JOIN t_video_user_d vu ON v.video_user_id = vu.video_user_id 
WHERE
	v.video_type = ‘搞笑’ 
	AND uva.ds >= to_date ( to_char ( SYSDATE,’ YYYYMMDD’ ) - 7,’ YYYYMMDD’ ) 
GROUP BY
	vu.video_user_id 
ORDER BY
	vu.video_user_id;

Pregunta 2: genere un resultado numérico: en el video creado el 20221103, si la descripción del video contiene las dos palabras clave "chistes" y "programa de entrevistas", es "contenido divertido". Si no contiene estas dos, la palabra clave es. "contenido no divertido", y el resultado final es 20221103 números diarios de videos divertidos y no divertidos con duplicados eliminados.

SELECT
	count( DISTINCT CASE WHEN v.video_description LIKE ‘ %搞笑段子%’ OR v.video_description LIKE ‘ %脱口秀%’ THEN video_id ELSE NULL END ) AS funny_count,
	Count( DISTINCT CASE WHEN v.video_description NOT LIKE ‘ %搞笑段子%’ AND v.video_description NOT LIKE ‘ %脱口秀%’ THEN v.video_id ELSE NULL END ) AS not_funny_count form t_video_d v 
WHERE
	v.ds = to_date ( ‘ 20221103’,’ YYYYMMDD’ );

Pregunta 3: Calcule la marca de tiempo de la primera exposición de video de cada usuario todos los días. Cuanto más rápida sea la velocidad de ejecución, mejor. Los campos de salida son fecha, identificación de usuario y marca de tiempo.

SELECT
	uva.ds AS date,
	uva.user_id,
	min( uva.TIMESTAMP ) AS first_exposure_timestamp form t_user_video_action_d uva 
WHERE
	uva.action_type = 1 
GROUP BY
	uva.user_id,
	uva.ds;

Pregunta 4: Genere una tabla dws para consultar la tasa de retención de 7 días de los usuarios activos expuestos en cualquier fecha del pasado. Los campos de salida son fecha, ID de usuario y tasa de retención de 7 días. La estructura de la tabla específica es la siguiente. Tabla 1 Tabla de comportamiento del usuario: t_user_video_action_d l Partición: ds (Formato aaaaMMdd) lClave principal: user_id, video_id lSignificado: agregación de todos los comportamientos de un usuario en un video, incremento diario Nombre del campo Tipo de significado del campo

SELECT
	uvu.ds AS date,
	uvu.video_user_id AS user_id,
CASE
	
	WHEN count( DISTINCT first_action.video_user_id ) = 0 THEN
	0 ELSE sum( CASE WHEN to_char ( uvu.ds,’ YYYYMMDD’ ) BETWEEN first_action.first_ds AND first_action.first_ds + 6 THEN 1 ELSE 0 END ) 
	END / count( distion first_action.video_user_id ) AS retention_rate Form t_video_user_d uvu
	JOIN ( SELECT user_id, video_id, ds AS first_ds form t_user_video_action_d WHERE action_type = 1 GROUP BY user_id, video_id, ds ORDER BY ds ASC ) first_action ON uvu.video_user_id = first_action.user_id 
GROUP BY
	uvu.video_user_id,
uvu.ds;