Partage de technologie

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

La durée limite de l'épreuve écrite pour le recrutement en sous-traitance du groupe d'analyse des données de compte vidéo est de 45 minutes.

La question est basée sur la structure de 3 tableaux. Écrivez le code SQL spécifique pour résoudre le problème (définition de la catégorie drôle : classification des vidéos ou classification des créateurs de vidéos comme « drôles »).

Question 1 : Produisez un tableau DWS de catégorie de comédie pour analyser les 3 vidéos récentes de l'auteur : affichez les 3 vidéos récemment publiées par chaque créateur de vidéo user_id, l'exposition de la semaine dernière et le nombre total de likes. Les champs de sortie sont obligatoires : Créateur de vidéo ; user_id, nombre d'utilisateurs exposés, nombre d'utilisateurs likes, nombre d'expositions, nombre de likes

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;

Question 2 : Afficher un résultat numérique : Dans la vidéo créée le 20221103, si la description de la vidéo contient les deux mots-clés « blagues drôles » et « talk-show », il s'agit de « contenu drôle ». Si elle ne contient pas ces deux mots-clés, le mot-clé est. "contenu non drôle", et le résultat final est un nombre quotidien de 20221103 de vidéos drôles et non drôles avec les doublons supprimés.

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’ );

Question 3 : Calculez l'horodatage de la première exposition vidéo de chaque utilisateur chaque jour. Plus la vitesse d'exécution est rapide, mieux c'est. Les champs de sortie sont la date, l'identifiant de l'utilisateur et l'horodatage.

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;

Question 4 : Générez une table dws pour interroger le taux de rétention sur 7 jours des utilisateurs actifs exposés à n'importe quelle date dans le passé. Les champs de sortie sont la date, l'ID utilisateur et le taux de rétention sur 7 jours. La structure spécifique de la table est la suivante : Tableau 1 Tableau des comportements utilisateur : t_user_video_action_d l Partition : ds ( Format aaaaMMjj) lClé primaire : user_id, video_id lSignification : Agrégation de tous les comportements d'un utilisateur sur une vidéo, incrément quotidien Nom du champ Signification du champ type

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;