기술나눔

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

영상계좌 데이터 분석그룹 아웃소싱 모집 필기시험 제한시간은 45분입니다.

문제는 3개의 테이블 구조를 기반으로 문제를 해결하기 위한 특정 SQL 코드를 작성합니다. (웃긴 카테고리 정의: 비디오 분류 또는 비디오 제작자 분류를 "웃긴"로 분류) l

질문 1: 작성자의 최근 동영상 3개를 분석하기 위해 코미디 카테고리 dws 테이블을 출력합니다. 각 동영상 제작자 user_id가 최근 공개한 3개의 동영상, 지난 주 노출 및 총 좋아요 수 출력 필드가 필요합니다. 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에 생성된 영상에서 영상 설명에 "재미있는 농담"과 "토크쇼"라는 두 개의 키워드가 포함되어 있으면 이 두 가지 키워드가 포함되어 있지 않으면 "재미있는 콘텐츠"입니다. "재미있지 않은 콘텐츠"이며, 최종 출력은 중복 항목이 제거된 일일 재미 있고 재미없는 동영상 수 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;