HiveSQL大厂面试 平均播放进度大于60%的视频类别

题目说明

练习题目来自牛客网sq在线编程 大厂面试题https://www.nowcoder.com/practice/c60242566ad94bc29959de0cdc6d95ef?tpId=268&tqId=2285039&ru=/exam/oj&qru=/ta/sql-factory-interview/question-ranking&sourceUrl=%2Fexam%2Foj%3Ftab%3DSQL%25E7%25AF%2587%26topicId%3D268

测试用例

create database nk_test;  //创建数据库
use nk_test;    //进入数据库

// 创建表
DROP TABLE IF EXISTS tb_user_video_log;
DROP TABLE IF EXISTS tb_video_info;
CREATE TABLE tb_user_video_log (
    id INT,
    uid INT,
    video_id INT,
    start_time timestamp,
    end_time timestamp,
    if_follow TINYINT,
    if_like TINYINT,
    if_retweet TINYINT,
    comment_id INT
);

CREATE TABLE tb_video_info (
    id INT,
    video_id INT,
    author INT,
    tag string,
    duration INT,
    release_time timestamp
);

// 插入数据
INSERT INTO tb_user_video_log(id, uid, video_id, start_time, end_time, if_follow, if_like, if_retweet, comment_id) VALUES
  (1, 101, 2001, '2021-10-01 10:00:00', '2021-10-01 10:00:30', 0, 1, 1, null),
  (2, 102, 2001, '2021-10-01 10:00:00', '2021-10-01 10:00:24', 0, 0, 1, null),
  (3, 103, 2001, '2021-10-01 11:00:00', '2021-10-01 11:00:34', 0, 1, 0, 1732526),
  (4, 101, 2002, '2021-09-01 10:00:00', '2021-09-01 10:00:42&