技術共有

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

動画アカウントデータ分析グループ外注採用の筆記試験の制限時間は45分です。

問題は 3 つのテーブルの構造に基づいており、問題を解決するための特定の SQL コードを記述します (面白いカテゴリの定義: 動画分類または動画クリエイターの分類を「面白い」とします)。

質問 1: 作者の最近の 3 つのビデオを分析するためにコメディ カテゴリの DWS テーブルを出力します。各ビデオ作成者の user_id、過去 1 週間の露出、いいねの総数の出力フィールドが必要です。ビデオ作成者。 user_id、露出ユーザー数、いいねユーザー数、露出数、いいね数

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;

質問 2: 数字の結果を出力します: 20221103 に作成されたビデオでは、ビデオの説明に「面白いジョーク」と「トークショー」という 2 つのキーワードが含まれていれば、この 2 つが含まれていない場合は「面白いコンテンツ」になります。 「面白くないコンテンツ」、最終的な出力は重複を削除した面白い動画と面白くない動画の毎日の数 20221103 です。

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

質問 3: 毎日、各ユーザーの最初のビデオ露出のタイムスタンプを計算します。出力フィールドは日付、ユーザー ID、およびタイムスタンプです。

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;

質問 4: 過去の任意の日付で公開されたアクティブ ユーザーの 7 日間の保持率をクエリするための dws テーブルを出力します。出力フィールドは日付、ユーザー ID、および 7 日間の保持率です。具体的なテーブル構造は次のとおりです。表 1 ユーザー動作テーブル: t_user_video_action_d l パーティション: ds (形式 yyyyMMdd) l主キー: user_id、video_id l 意味: ビデオ上のユーザーのすべての動作の集計、日単位の増分 フィールド名 フィールドの意味の種類

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;