字节数据平台_数据开发实习一面

请问面试的sql题是怎么给出的？是他自己口述出来还是有题目图片展示？ 然后要用什么形式跟他回答呢？自己打出来答案发给他？

第二道sql题参考答案 SELECT userid, -- 完整观看率 = 至少完整观看1次的视频数 / 总去重视频数 ROUND( SUM(has_complete) / COUNT(DISTINCT video_id) * 100, -- 分母为去重视频数 2 ) AS complete_rate FROM ( -- 子查询：先按用户+视频分组，判断该视频是否至少完整观看过1次 SELECT w.userid, w.video_id, -- 若该视频有1次以上完整观看，标记为1（否则0） MAX( CASE WHEN TIMESTAMPDIFF(SECOND, w.start_time, w.end_time) >= v.duration THEN 1 ELSE 0 END ) AS has_complete FROM watch_records w JOIN users u ON w.userid = u.userid JOIN videos v ON w.video_id = v.video_id WHERE u.gender = 'female' AND w.end_time > w.start_time GROUP BY w.userid, w.video_id -- 按用户+视频去重 ) AS t GROUP BY userid HAVING COUNT(DISTINCT video_id) > 50 -- 总去重视频数超50个 ORDER BY complete_rate DESC LIMIT 10;

第一道sql题参考答案 SELECT userid, COUNT(DISTINCT video_id) AS video_count FROM watch_records WHERE start_time >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) -- 按开始时间筛选最近7天 GROUP BY userid HAVING video_count > 0 ORDER BY video_count DESC LIMIT 5;