Technologieaustausch

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

Das Zeitlimit für den schriftlichen Test für die Rekrutierung von Video-Account-Datenanalyse-Gruppen-Outsourcing beträgt 45 Minuten.

Die Frage basiert auf der Struktur von 3 Tabellen. Schreiben Sie den spezifischen SQL-Code, um das Problem zu lösen (Definition der lustigen Kategorie: Videoklassifizierung oder Klassifizierung des Videoerstellers als „lustig“) l

Frage 1: Geben Sie eine DWS-Tabelle für Comedy-Kategorien aus, um die letzten 3 Videos des Autors zu analysieren: Geben Sie die 3 kürzlich von jedem Videoersteller veröffentlichten Benutzer-IDs, die Bekanntheit in der letzten Woche und die Gesamtzahl der Likes aus. Ausgabefelder sind erforderlich: Videoersteller user_id, Anzahl der Expositionsbenutzer, Anzahl der Likes-Benutzer, Anzahl der Expositionen, Anzahl der 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;

Frage 2: Zahlenergebnis ausgeben: Wenn die Videobeschreibung in dem am 20221103 erstellten Video die beiden Schlüsselwörter „lustige Witze“ und „Talkshow“ enthält, handelt es sich um „lustigen Inhalt“. Wenn sie diese beiden nicht enthält, lautet das Schlüsselwort „nicht lustiger Inhalt“, und die endgültige Ausgabe ist 20221103 tägliche Anzahl lustiger und nicht lustiger Videos, wobei Duplikate entfernt wurden.

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

Frage 3: Berechnen Sie jeden Tag den Zeitstempel der ersten Videobelichtung jedes Benutzers. Je höher die Laufgeschwindigkeit, desto besser. Die Ausgabefelder sind Datum, Benutzer-ID und Zeitstempel.

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;

Frage 4: Geben Sie eine DWS-Tabelle aus, um die 7-Tage-Aufbewahrungsrate der exponierten aktiven Benutzer zu einem beliebigen Zeitpunkt in der Vergangenheit abzufragen. Die Ausgabefelder sind Datum, Benutzer-ID und 7-Tage-Aufbewahrungsrate. Die spezifische Tabellenstruktur ist wie folgt: Tabelle 1 Benutzerverhaltenstabelle: t_user_video_action_d l Partition: ds (Format yyyyMMdd) lPrimärschlüssel: user_id, video_id lBedeutung: Aggregation aller Verhaltensweisen eines Benutzers in einem Video, tägliches Inkrement Feldname Feldbedeutung Typ

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;