Обмен технологиями

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

Срок проведения письменного теста по подбору аутсорсинговой группы анализа данных видеоаккаунта составляет 45 минут.

Вопрос основан на структуре трех таблиц. Напишите конкретный код SQL для решения проблемы (определение категории смешного: классификация видео или классификация создателя видео как «смешное»).

Вопрос 1. Выведите таблицу dws категории комедии для анализа трех последних видеороликов автора: выведите три видеоролика, недавно выпущенные каждым создателем видео, user_id, количество просмотров за последнюю неделю и общее количество лайков. Требуются поля вывода: Создатель видео; 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. Рассчитайте временную метку первого просмотра видео каждым пользователем каждый день. Чем выше скорость работы, тем лучше. Полями вывода являются дата, идентификатор пользователя и временная метка.

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. Выведите таблицу dws для запроса уровня 7-дневного удержания активных пользователей на любую дату в прошлом. Поля вывода: дата, идентификатор пользователя и уровень 7-дневного удержания. Конкретная структура таблицы выглядит следующим образом. Таблица 1. Таблица поведения пользователя: t_user_video_action_d l Раздел: ds (формат ггггММдд) 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;