常熟网站建设专业的公司,wordpress 目录扫描,怎么做淘客手机网站,迅 网站 模板文章目录 一、某音短视频1.各个视频的平均完播率2.平均播放进度大于60%的视频类别3.每类视频近一个月的转发量/率4.每个创作者每月的涨粉率及截止当前的总粉丝量5.国庆期间每类视频点赞量和转发量6.近一个月发布的视频中热度最高的top3视频 二、用户增长场景#xff08;某度信… 文章目录 一、某音短视频1.各个视频的平均完播率2.平均播放进度大于60%的视频类别3.每类视频近一个月的转发量/率4.每个创作者每月的涨粉率及截止当前的总粉丝量5.国庆期间每类视频点赞量和转发量6.近一个月发布的视频中热度最高的top3视频 二、用户增长场景某度信息流1.2021年11月每天的人均浏览文章时长2.每篇文章同一时刻最大在看人数3.2021年11月每天新用户的次日留存率4.统计活跃间隔对用户分级结果5.每天的日活数及新用户占比6.连续签到领金币 三、电商场景某东商城1.计算商城中2021年每月的GMV2.统计2021年10月每个退货率不大于0.5的商品各项指标3.某店铺的各商品毛利率及店铺整体毛利率4.零食类商品中复购率top3高的商品5.10月的新户客单价和获客成本6.店铺901国庆期间的7日动销率和滞销率 四、出行场景某滴打车1.2021年国庆在北京接单3次及以上的司机统计信息2.有取消订单记录的司机平均评分3.每个城市中评分最高的司机信息4.国庆期间近7日日均取消订单量5.工作日各时段叫车量、等待接单时间和调度时间6.各城市最大同时等车人数 五、某宝店铺分析电商模式1.某宝店铺的SPU数量2.某宝店铺的实际销售额与客单价3.某宝店铺折扣率4.某宝店铺动销率与售罄率5.某宝店铺连续2天及以上购物的用户及其对应的天数 六、牛客直播课分析在线教育行业1.牛客直播转换率2.牛客直播开始时各直播间在线人数3.牛客直播各科目平均观看时长4.牛客直播各科目出勤率5.牛客直播各科目同时在线人数 七、某乎问答内容行业1.某乎问答11月份日人均回答量2.某乎问答高质量的回答中用户属于各级别的数量3.某乎问答单日回答问题数大于等于3个的所有用户4.某乎问答回答过教育类问题的用户里有多少用户回答5.某乎问答最大连续回答问题天数大于等于3天的用户 一、某音短视频
1.各个视频的平均完播率
题目计算2021年里有播放记录的每个视频的完播率(结果保留三位小数)并按完播率降序排序
--输入
DROP TABLE IF EXISTS tb_user_video_log, tb_video_info;
CREATE TABLE tb_user_video_log (id INT PRIMARY KEY AUTO_INCREMENT COMMENT 自增ID,uid INT NOT NULL COMMENT 用户ID,video_id INT NOT NULL COMMENT 视频ID,start_time datetime COMMENT 开始观看时间,end_time datetime COMMENT 结束观看时间,if_follow TINYINT COMMENT 是否关注,if_like TINYINT COMMENT 是否点赞,if_retweet TINYINT COMMENT 是否转发,comment_id INT COMMENT 评论ID
) CHARACTER SET utf8 COLLATE utf8_bin;CREATE TABLE tb_video_info (id INT PRIMARY KEY AUTO_INCREMENT COMMENT 自增ID,video_id INT UNIQUE NOT NULL COMMENT 视频ID,author INT NOT NULL COMMENT 创作者ID,tag VARCHAR(16) NOT NULL COMMENT 类别标签,duration INT NOT NULL COMMENT 视频时长(秒数),release_time datetime NOT NULL COMMENT 发布时间
)CHARACTER SET utf8 COLLATE utf8_bin;INSERT INTO tb_user_video_log(uid, video_id, start_time, end_time, if_follow, if_like, if_retweet, comment_id) VALUES(101, 2001, 2021-10-01 10:00:00, 2021-10-01 10:00:30, 0, 1, 1, null),(102, 2001, 2021-10-01 10:00:00, 2021-10-01 10:00:24, 0, 0, 1, null),(103, 2001, 2021-10-01 11:00:00, 2021-10-01 11:00:34, 0, 1, 0, 1732526),(101, 2002, 2021-09-01 10:00:00, 2021-09-01 10:00:42, 1, 0, 1, null),(102, 2002, 2021-10-01 11:00:00, 2021-10-01 11:00:30, 1, 0, 1, null);INSERT INTO tb_video_info(video_id, author, tag, duration, release_time) VALUES(2001, 901, 影视, 30, 2021-01-01 7:00:00),(2002, 901, 美食, 60, 2021-01-01 7:00:00),(2003, 902, 旅游, 90, 2021-01-01 7:00:00);selecta.video_id,round(avg(if(TIMESTAMPDIFF(second,a.start_time,a.end_time)b.duration,1,0)),3) as avg_comp_play_rate
from tb_user_video_log a
join tb_video_info b
on a.video_idb.video_id
where year(start_time)2021
group by a.video_id
order by avg_comp_play_rate desc2.平均播放进度大于60%的视频类别
题目计算各类视频的平均播放进度将进度大于60%的类别输出。
--输入
DROP TABLE IF EXISTS tb_user_video_log, tb_video_info;
CREATE TABLE tb_user_video_log (id INT PRIMARY KEY AUTO_INCREMENT COMMENT 自增ID,uid INT NOT NULL COMMENT 用户ID,video_id INT NOT NULL COMMENT 视频ID,start_time datetime COMMENT 开始观看时间,end_time datetime COMMENT 结束观看时间,if_follow TINYINT COMMENT 是否关注,if_like TINYINT COMMENT 是否点赞,if_retweet TINYINT COMMENT 是否转发,comment_id INT COMMENT 评论ID
) CHARACTER SET utf8 COLLATE utf8_bin;CREATE TABLE tb_video_info (id INT PRIMARY KEY AUTO_INCREMENT COMMENT 自增ID,video_id INT UNIQUE NOT NULL COMMENT 视频ID,author INT NOT NULL COMMENT 创作者ID,tag VARCHAR(16) NOT NULL COMMENT 类别标签,duration INT NOT NULL COMMENT 视频时长(秒数),release_time datetime NOT NULL COMMENT 发布时间
)CHARACTER SET utf8 COLLATE utf8_bin;INSERT INTO tb_user_video_log(uid, video_id, start_time, end_time, if_follow, if_like, if_retweet, comment_id) VALUES(101, 2001, 2021-10-01 10:00:00, 2021-10-01 10:00:30, 0, 1, 1, null),(102, 2001, 2021-10-01 10:00:00, 2021-10-01 10:00:24, 0, 0, 1, null),(103, 2001, 2021-10-01 11:00:00, 2021-10-01 11:00:34, 0, 1, 0, 1732526),(101, 2002, 2021-09-01 10:00:00, 2021-09-01 10:00:42, 1, 0, 1, null),(102, 2002, 2021-10-01 11:00:00, 2021-10-01 11:00:30, 1, 0, 1, null);INSERT INTO tb_video_info(video_id, author, tag, duration, release_time) VALUES(2001, 901, 影视, 30, 2021-01-01 7:00:00),(2002, 901, 美食, 60, 2021-01-01 7:00:00),(2003, 902, 旅游, 90, 2021-01-01 7:00:00);select aa.tag,concat(avg_play_progress,%) as avg_play_progress
from(select b.tag,round(avg(if(TIMESTAMPDIFF(second,a.start_time,a.end_time)b.duration,100,TIMESTAMPDIFF(second,a.start_time,a.end_time)/b.duration*100)),2) as avg_play_progressfrom tb_user_video_log ajoin tb_video_info bon a.video_idb.video_idgroup by b.tag
) aa
where aa.avg_play_progress60
order by avg_play_progress desc3.每类视频近一个月的转发量/率
题目统计在有用户互动的最近一个月按包含当天在内的近30天算比如10月31日的近30天为10.2~10.31之间的数据中每类视频的转发量和转发率保留3位小数。
--输入
DROP TABLE IF EXISTS tb_user_video_log, tb_video_info;
CREATE TABLE tb_user_video_log (id INT PRIMARY KEY AUTO_INCREMENT COMMENT 自增ID,uid INT NOT NULL COMMENT 用户ID,video_id INT NOT NULL COMMENT 视频ID,start_time datetime COMMENT 开始观看时间,end_time datetime COMMENT 结束观看时间,if_follow TINYINT COMMENT 是否关注,if_like TINYINT COMMENT 是否点赞,if_retweet TINYINT COMMENT 是否转发,comment_id INT COMMENT 评论ID
) CHARACTER SET utf8 COLLATE utf8_bin;CREATE TABLE tb_video_info (id INT PRIMARY KEY AUTO_INCREMENT COMMENT 自增ID,video_id INT UNIQUE NOT NULL COMMENT 视频ID,author INT NOT NULL COMMENT 创作者ID,tag VARCHAR(16) NOT NULL COMMENT 类别标签,duration INT NOT NULL COMMENT 视频时长(秒数),release_time datetime NOT NULL COMMENT 发布时间
)CHARACTER SET utf8 COLLATE utf8_bin;INSERT INTO tb_user_video_log(uid, video_id, start_time, end_time, if_follow, if_like, if_retweet, comment_id) VALUES(101, 2001, 2021-10-01 10:00:00, 2021-10-01 10:00:30, 0, 1, 1, null),(102, 2001, 2021-10-01 10:00:00, 2021-10-01 10:00:24, 0, 0, 1, null),(103, 2001, 2021-10-01 11:00:00, 2021-10-01 11:00:34, 0, 1, 0, 1732526),(101, 2002, 2021-09-01 10:00:00, 2021-09-01 10:00:42, 1, 0, 1, null),(102, 2002, 2021-10-01 11:00:00, 2021-10-01 11:00:30, 1, 0, 1, null);INSERT INTO tb_video_info(video_id, author, tag, duration, release_time) VALUES(2001, 901, 影视, 30, 2021-01-01 7:00:00),(2002, 901, 美食, 60, 2021-01-01 7:00:00),(2003, 902, 旅游, 90, 2021-01-01 7:00:00);select aa.tag,if_retweet_cnt as retweet_cut ,round(if_retweet_cnt/play_cnt,3) as retweet_rate
from(select b.tag,sum(a.if_retweet) as if_retweet_cnt,count(a.start_time) as play_cntfrom tb_user_video_log ajoin tb_video_info bon a.video_idb.video_idwhere date(a.start_time)(select DATE_SUB(date(max(start_time)),INTERVAL 30 day) from tb_user_video_log)group by b.tag
) aa
order by retweet_rate desc4.每个创作者每月的涨粉率及截止当前的总粉丝量
题目计算2021年里每个创作者每月的涨粉率及截止当月的总粉丝量。
--输入
DROP TABLE IF EXISTS tb_user_video_log, tb_video_info;
CREATE TABLE tb_user_video_log (id INT PRIMARY KEY AUTO_INCREMENT COMMENT 自增ID,uid INT NOT NULL COMMENT 用户ID,video_id INT NOT NULL COMMENT 视频ID,start_time datetime COMMENT 开始观看时间,end_time datetime COMMENT 结束观看时间,if_follow TINYINT COMMENT 是否关注,if_like TINYINT COMMENT 是否点赞,if_retweet TINYINT COMMENT 是否转发,comment_id INT COMMENT 评论ID
) CHARACTER SET utf8 COLLATE utf8_bin;CREATE TABLE tb_video_info (id INT PRIMARY KEY AUTO_INCREMENT COMMENT 自增ID,video_id INT UNIQUE NOT NULL COMMENT 视频ID,author INT NOT NULL COMMENT 创作者ID,tag VARCHAR(16) NOT NULL COMMENT 类别标签,duration INT NOT NULL COMMENT 视频时长(秒数),release_time datetime NOT NULL COMMENT 发布时间
)CHARACTER SET utf8 COLLATE utf8_bin;INSERT INTO tb_user_video_log(uid, video_id, start_time, end_time, if_follow, if_like, if_retweet, comment_id) VALUES(101, 2001, 2021-10-01 10:00:00, 2021-10-01 10:00:30, 0, 1, 1, null),(102, 2001, 2021-10-01 10:00:00, 2021-10-01 10:00:24, 0, 0, 1, null),(103, 2001, 2021-10-01 11:00:00, 2021-10-01 11:00:34, 0, 1, 0, 1732526),(101, 2002, 2021-09-01 10:00:00, 2021-09-01 10:00:42, 1, 0, 1, null),(102, 2002, 2021-10-01 11:00:00, 2021-10-01 11:00:30, 1, 0, 1, null);INSERT INTO tb_video_info(video_id, author, tag, duration, release_time) VALUES(2001, 901, 影视, 30, 2021-01-01 7:00:00),(2002, 901, 美食, 60, 2021-01-01 7:00:00),(2003, 902, 旅游, 90, 2021-01-01 7:00:00);select aa.author,aa.month,round(aa.fans_cnt/aa.play_cnt,3) as fans_growth_rate,sum(aa.fans_cnt) over(partition by aa.author order by aa.month) as total_fans
from(select b.author,DATE_FORMAT(a.start_time,%Y-%m) as month,sum(if(if_follow2,-1,if_follow)) as fans_cnt,count(a.start_time) as play_cntfrom tb_user_video_log ajoin tb_video_info bon a.video_idb.video_idwhere year(a.start_time)2021group by b.author,DATE_FORMAT(a.start_time,%Y-%m)
) aa
order by aa.author,total_fans 5.国庆期间每类视频点赞量和转发量
题目统计2021年国庆头3天每类视频每天的近一周总点赞量和一周内最大单天转发量结果按视频类别降序、日期升序排序。假设数据库中数据足够多至少每个类别下国庆头3天及之前一周的每天都有播放记录。
--输入
DROP TABLE IF EXISTS tb_user_video_log, tb_video_info;
CREATE TABLE tb_user_video_log (id INT PRIMARY KEY AUTO_INCREMENT COMMENT 自增ID,uid INT NOT NULL COMMENT 用户ID,video_id INT NOT NULL COMMENT 视频ID,start_time datetime COMMENT 开始观看时间,end_time datetime COMMENT 结束观看时间,if_follow TINYINT COMMENT 是否关注,if_like TINYINT COMMENT 是否点赞,if_retweet TINYINT COMMENT 是否转发,comment_id INT COMMENT 评论ID
) CHARACTER SET utf8 COLLATE utf8_bin;CREATE TABLE tb_video_info (id INT PRIMARY KEY AUTO_INCREMENT COMMENT 自增ID,video_id INT UNIQUE NOT NULL COMMENT 视频ID,author INT NOT NULL COMMENT 创作者ID,tag VARCHAR(16) NOT NULL COMMENT 类别标签,duration INT NOT NULL COMMENT 视频时长(秒数),release_time datetime NOT NULL COMMENT 发布时间
)CHARACTER SET utf8 COLLATE utf8_bin;INSERT INTO tb_user_video_log(uid, video_id, start_time, end_time, if_follow, if_like, if_retweet, comment_id) VALUES(101, 2001, 2021-09-24 10:00:00, 2021-09-24 10:00:20, 1, 1, 0, null),(105, 2002, 2021-09-25 11:00:00, 2021-09-25 11:00:30, 0, 0, 1, null),(102, 2002, 2021-09-25 11:00:00, 2021-09-25 11:00:30, 1, 1, 1, null),(101, 2002, 2021-09-26 11:00:00, 2021-09-26 11:00:30, 1, 0, 1, null),(101, 2002, 2021-09-27 11:00:00, 2021-09-27 11:00:30, 1, 1, 0, null),(102, 2002, 2021-09-28 11:00:00, 2021-09-28 11:00:30, 1, 0, 1, null),(103, 2002, 2021-09-29 11:00:00, 2021-09-29 11:00:30, 1, 0, 1, null),(102, 2002, 2021-09-30 11:00:00, 2021-09-30 11:00:30, 1, 1, 1, null),(101, 2001, 2021-10-01 10:00:00, 2021-10-01 10:00:20, 1, 1, 0, null),(102, 2001, 2021-10-01 10:00:00, 2021-10-01 10:00:15, 0, 0, 1, null),(103, 2001, 2021-10-01 11:00:50, 2021-10-01 11:01:15, 1, 1, 0, 1732526),(106, 2002, 2021-10-02 10:59:05, 2021-10-02 11:00:05, 2, 0, 1, null),(107, 2002, 2021-10-02 10:59:05, 2021-10-02 11:00:05, 1, 0, 1, null),(108, 2002, 2021-10-02 10:59:05, 2021-10-02 11:00:05, 1, 1, 1, null),(109, 2002, 2021-10-03 10:59:05, 2021-10-03 11:00:05, 0, 1, 0, null);INSERT INTO tb_video_info(video_id, author, tag, duration, release_time) VALUES(2001, 901, 旅游, 30, 2020-01-01 7:00:00),(2002, 901, 旅游, 60, 2021-01-01 7:00:00),(2003, 902, 影视, 90, 2020-01-01 7:00:00),(2004, 902, 美女, 90, 2020-01-01 8:00:00);select a3.tag,a3.dt,a3.sum_like_cnt_7d,a3.max_retweet_cnt_7d
from(select aa.tag,aa.dt,sum(aa.if_like_cnt) over(partition by aa.tag order by aa.dt desc rows between current row and 6 following) as sum_like_cnt_7d,max(aa.if_retweet_cnt) over(partition by aa.tag order by aa.dt desc rows between current row and 6 following) as max_retweet_cnt_7dfrom(select b.tag,date(a.start_time) as dt,sum(a.if_like) as if_like_cnt,sum(a.if_retweet) as if_retweet_cntfrom tb_user_video_log ajoin tb_video_info bon a.video_idb.video_idwhere date(a.start_time) BETWEEN 2021-09-25 AND 2021-10-03group by b.tag,date(a.start_time)) aa
) a3
where a3.dt BETWEEN 2021-10-01 and 2021-10-03
order by a3.tag desc,a3.dt 6.近一个月发布的视频中热度最高的top3视频
题目找出近一个月发布的视频中热度最高的top3视频。
--输入
DROP TABLE IF EXISTS tb_user_video_log, tb_video_info;
CREATE TABLE tb_user_video_log (id INT PRIMARY KEY AUTO_INCREMENT COMMENT 自增ID,uid INT NOT NULL COMMENT 用户ID,video_id INT NOT NULL COMMENT 视频ID,start_time datetime COMMENT 开始观看时间,end_time datetime COMMENT 结束观看时间,if_follow TINYINT COMMENT 是否关注,if_like TINYINT COMMENT 是否点赞,if_retweet TINYINT COMMENT 是否转发,comment_id INT COMMENT 评论ID
) CHARACTER SET utf8 COLLATE utf8_bin;CREATE TABLE tb_video_info (id INT PRIMARY KEY AUTO_INCREMENT COMMENT 自增ID,video_id INT UNIQUE NOT NULL COMMENT 视频ID,author INT NOT NULL COMMENT 创作者ID,tag VARCHAR(16) NOT NULL COMMENT 类别标签,duration INT NOT NULL COMMENT 视频时长(秒数),release_time datetime NOT NULL COMMENT 发布时间
)CHARACTER SET utf8 COLLATE utf8_bin;INSERT INTO tb_user_video_log(uid, video_id, start_time, end_time, if_follow, if_like, if_retweet, comment_id) VALUES(101, 2001, 2021-09-24 10:00:00, 2021-09-24 10:00:30, 1, 1, 1, null),(101, 2001, 2021-10-01 10:00:00, 2021-10-01 10:00:31, 1, 1, 0, null),(102, 2001, 2021-10-01 10:00:00, 2021-10-01 10:00:35, 0, 0, 1, null),(103, 2001, 2021-10-03 11:00:50, 2021-10-03 11:01:35, 1, 1, 0, 1732526),(106, 2002, 2021-10-02 10:59:05, 2021-10-02 11:00:04, 2, 0, 1, null),(107, 2002, 2021-10-02 10:59:05, 2021-10-02 11:00:06, 1, 0, 0, null),(108, 2002, 2021-10-02 10:59:05, 2021-10-02 11:00:05, 1, 1, 1, null),(109, 2002, 2021-10-03 10:59:05, 2021-10-03 11:00:01, 0, 1, 0, null),(105, 2002, 2021-09-25 11:00:00, 2021-09-25 11:00:30, 1, 0, 1, null),(101, 2003, 2021-09-26 11:00:00, 2021-09-26 11:00:30, 1, 0, 0, null),(101, 2003, 2021-09-30 11:00:00, 2021-09-30 11:00:30, 1, 1, 0, null);INSERT INTO tb_video_info(video_id, author, tag, duration, release_time) VALUES(2001, 901, 旅游, 30, 2021-09-05 7:00:00),(2002, 901, 旅游, 60, 2021-09-05 7:00:00),(2003, 902, 影视, 90, 2021-09-05 7:00:00),(2004, 902, 影视, 90, 2021-09-05 8:00:00);select aa.video_id,round((100*com_play_rate5*like_cnt3*comment_cnt2*retweet_cnt)/(TIMESTAMPDIFF(day,rec_paly_date,cur_date)1),0) as hot_index
from(selecta.video_id,avg(if(TIMESTAMPDIFF(second,a.start_time,a.end_time)b.duration,1,0)) as com_play_rate,sum(a.if_like) as like_cnt,count(a.comment_id) as comment_cnt,sum(a.if_retweet) as retweet_cnt,max(date(a.end_time)) as rec_paly_date,max(date(b.release_time)) as rec_release_date,max(cur_date) as cur_datefrom tb_user_video_log ajoin tb_video_info bon a.video_idb.video_idleft join (select max(date(start_time)) as cur_date from tb_user_video_log)c on 1group by a.video_idhaving TIMESTAMPDIFF(day,rec_release_date,cur_date)30
) aa
order by hot_index desc
limit 3二、用户增长场景某度信息流
1.2021年11月每天的人均浏览文章时长
题目统计2021年11月每天的人均浏览文章时长秒数结果保留1位小数并按时长由短到长排序。
--输入
DROP TABLE IF EXISTS tb_user_log;
CREATE TABLE tb_user_log (id INT PRIMARY KEY AUTO_INCREMENT COMMENT 自增ID,uid INT NOT NULL COMMENT 用户ID,artical_id INT NOT NULL COMMENT 视频ID,in_time datetime COMMENT 进入时间,out_time datetime COMMENT 离开时间,sign_in TINYINT DEFAULT 0 COMMENT 是否签到
) CHARACTER SET utf8 COLLATE utf8_bin;INSERT INTO tb_user_log(uid, artical_id, in_time, out_time, sign_in) VALUES(101, 9001, 2021-11-01 10:00:00, 2021-11-01 10:00:31, 0),(102, 9001, 2021-11-01 10:00:00, 2021-11-01 10:00:24, 0),(102, 9002, 2021-11-01 11:00:00, 2021-11-01 11:00:11, 0),(101, 9001, 2021-11-02 10:00:00, 2021-11-02 10:00:50, 0),(102, 9002, 2021-11-02 11:00:01, 2021-11-02 11:00:24, 0);select date(in_time) as dt,round(sum(TIMESTAMPDIFF(second,in_time,out_time))/count(distinct uid),1) as avg_viiew_len_sec
from tb_user_log
where artical_id 0 and DATE_FORMAT(in_time,%Y-%m)2021-11
group by date(in_time)
order by avg_viiew_len_sec2.每篇文章同一时刻最大在看人数
题目统计每篇文章同一时刻最大在看人数如果同一时刻有进入也有离开时先记录用户数增加再记录减少结果按最大人数降序。
--输入
DROP TABLE IF EXISTS tb_user_log;
CREATE TABLE tb_user_log (id INT PRIMARY KEY AUTO_INCREMENT COMMENT 自增ID,uid INT NOT NULL COMMENT 用户ID,artical_id INT NOT NULL COMMENT 视频ID,in_time datetime COMMENT 进入时间,out_time datetime COMMENT 离开时间,sign_in TINYINT DEFAULT 0 COMMENT 是否签到
) CHARACTER SET utf8 COLLATE utf8_bin;INSERT INTO tb_user_log(uid, artical_id, in_time, out_time, sign_in) VALUES(101, 9001, 2021-11-01 10:00:00, 2021-11-01 10:00:11, 0),(102, 9001, 2021-11-01 10:00:09, 2021-11-01 10:00:38, 0),(103, 9001, 2021-11-01 10:00:28, 2021-11-01 10:00:58, 0),(104, 9002, 2021-11-01 11:00:45, 2021-11-01 11:01:11, 0),(105, 9001, 2021-11-01 10:00:51, 2021-11-01 10:00:59, 0),(106, 9002, 2021-11-01 11:00:55, 2021-11-01 11:01:24, 0),(107, 9001, 2021-11-01 10:00:01, 2021-11-01 10:01:50, 0);select a2.artical_id,max(a2.sum_diff) as max_uv
from (select a.artical_id,a.dt,sum(diff) over(partition by a.artical_id order by a.dt,a.diff desc) as sum_difffrom(select artical_id,in_time as dt,1 as difffrom tb_user_logwhere artical_id0union allselect artical_id,out_time as dt,-1 as difffrom tb_user_logwhere artical_id0) a
) a2
group by a2.artical_id
order by max_uv desc3.2021年11月每天新用户的次日留存率
题目统计2021年11月每天新用户的次日留存率保留2位小数
--输入
DROP TABLE IF EXISTS tb_user_log;
CREATE TABLE tb_user_log (id INT PRIMARY KEY AUTO_INCREMENT COMMENT 自增ID,uid INT NOT NULL COMMENT 用户ID,artical_id INT NOT NULL COMMENT 视频ID,in_time datetime COMMENT 进入时间,out_time datetime COMMENT 离开时间,sign_in TINYINT DEFAULT 0 COMMENT 是否签到
) CHARACTER SET utf8 COLLATE utf8_bin;INSERT INTO tb_user_log(uid, artical_id, in_time, out_time, sign_in) VALUES(101, 0, 2021-11-01 10:00:00, 2021-11-01 10:00:42, 1),(102, 9001, 2021-11-01 10:00:00, 2021-11-01 10:00:09, 0),(103, 9001, 2021-11-01 10:00:01, 2021-11-01 10:01:50, 0),(101, 9002, 2021-11-02 10:00:09, 2021-11-02 10:00:28, 0),(103, 9002, 2021-11-02 10:00:51, 2021-11-02 10:00:59, 0),(104, 9001, 2021-11-02 10:00:28, 2021-11-02 10:00:50, 0),(101, 9003, 2021-11-03 11:00:55, 2021-11-03 11:01:24, 0),(104, 9003, 2021-11-03 11:00:45, 2021-11-03 11:00:55, 0),(105, 9003, 2021-11-03 11:00:53, 2021-11-03 11:00:59, 0),(101, 9002, 2021-11-04 11:00:55, 2021-11-04 11:00:59, 0);select a.min_in_date as dt,round(count(b.uid)/count(a.uid),2) as uv_rate
from(-- 每天新用户表selectuid,min(date(in_time)) as min_in_datefrom tb_user_loggroup by uid
) a left join(-- 用户活跃表select uid,date(in_time) as datefrom tb_user_logunionselect uid,date(out_time) as datefrom tb_user_log
) b on a.uidb.uid and min_in_date DATE_SUB(date,INTERVAL 1 day)
where DATE_FORMAT(a.min_in_date,%Y-%m)2021-11
group by a.min_in_date
order by dt4.统计活跃间隔对用户分级结果
题目统计活跃间隔对用户分级后各活跃等级用户占比结果保留两位小数且按占比降序排序。
--输入
DROP TABLE IF EXISTS tb_user_log;
CREATE TABLE tb_user_log (id INT PRIMARY KEY AUTO_INCREMENT COMMENT 自增ID,uid INT NOT NULL COMMENT 用户ID,artical_id INT NOT NULL COMMENT 视频ID,in_time datetime COMMENT 进入时间,out_time datetime COMMENT 离开时间,sign_in TINYINT DEFAULT 0 COMMENT 是否签到
) CHARACTER SET utf8 COLLATE utf8_bin;INSERT INTO tb_user_log(uid, artical_id, in_time, out_time, sign_in) VALUES(109, 9001, 2021-08-31 10:00:00, 2021-08-31 10:00:09, 0),(109, 9002, 2021-11-04 11:00:55, 2021-11-04 11:00:59, 0),(108, 9001, 2021-09-01 10:00:01, 2021-09-01 10:01:50, 0),(108, 9001, 2021-11-03 10:00:01, 2021-11-03 10:01:50, 0),(104, 9001, 2021-11-02 10:00:28, 2021-11-02 10:00:50, 0),(104, 9003, 2021-09-03 11:00:45, 2021-09-03 11:00:55, 0),(105, 9003, 2021-11-03 11:00:53, 2021-11-03 11:00:59, 0),(102, 9001, 2021-10-30 10:00:00, 2021-10-30 10:00:09, 0),(103, 9001, 2021-10-21 10:00:00, 2021-10-21 10:00:09, 0),(101, 0, 2021-10-01 10:00:00, 2021-10-01 10:00:42, 1);select a3.user_grade,round(count(a3.uid)/max(a3.user_cnt),2) as ratio
from(select a2.uid,a2.user_cnt,case when last_dt_diff30 then 流失用户when last_dt_diff7 then 沉睡用户when first_dt_diff7 then 新晋用户else 忠实用户 end as user_grade-- when last_dt_diff7 then 忠实用户-- else null end as user_gradefrom(select a.uid ,TIMESTAMPDIFF(day,first_dt,cur_dt) as first_dt_diff -- 最早活跃日期间隔,TIMESTAMPDIFF(day,last_dt,cur_dt) as last_dt_diff -- 最晚活跃日期间隔,b.user_cntfrom(select uid,min(date(in_time)) as first_dt -- 最早活跃日期,max(date(out_time)) as last_dt -- 最晚活跃日期from tb_user_loggroup by uid) a left join(select max(date(out_time)) as cur_dt -- 当前日期,count(distinct uid) as user_cnt -- 所有用户数from tb_user_log) b on 1 ) a2
) a3
group by a3.user_grade
order by ratio desc5.每天的日活数及新用户占比
题目统计每天的日活数及新用户占比
--输入
DROP TABLE IF EXISTS tb_user_log;
CREATE TABLE tb_user_log (id INT PRIMARY KEY AUTO_INCREMENT COMMENT 自增ID,uid INT NOT NULL COMMENT 用户ID,artical_id INT NOT NULL COMMENT 视频ID,in_time datetime COMMENT 进入时间,out_time datetime COMMENT 离开时间,sign_in TINYINT DEFAULT 0 COMMENT 是否签到
) CHARACTER SET utf8 COLLATE utf8_bin;INSERT INTO tb_user_log(uid, artical_id, in_time, out_time, sign_in) VALUES(101, 9001, 2021-10-31 10:00:00, 2021-10-31 10:00:09, 0),(102, 9001, 2021-10-31 10:00:00, 2021-10-31 10:00:09, 0),(101, 0, 2021-11-01 10:00:00, 2021-11-01 10:00:42, 1),(102, 9001, 2021-11-01 10:00:00, 2021-11-01 10:00:09, 0),(108, 9001, 2021-11-01 10:00:01, 2021-11-01 10:01:50, 0),(108, 9001, 2021-11-02 10:00:01, 2021-11-02 10:01:50, 0),(104, 9001, 2021-11-02 10:00:28, 2021-11-02 10:00:50, 0),(106, 9001, 2021-11-02 10:00:28, 2021-11-02 10:00:50, 0),(108, 9001, 2021-11-03 10:00:01, 2021-11-03 10:01:50, 0),(109, 9002, 2021-11-03 11:00:55, 2021-11-03 11:00:59, 0),(104, 9003, 2021-11-03 11:00:45, 2021-11-03 11:00:55, 0),(105, 9003, 2021-11-03 11:00:53, 2021-11-03 11:00:59, 0),(106, 9003, 2021-11-03 11:00:45, 2021-11-03 11:00:55, 0);select a2.dt -- 当天,a2.dau as dau -- 日活数,round(ifnull(b2.uv_new_daily,0)/a2.dau,2) as uv_new_ratio -- 新用户占比
from(select b.dt ,count(distinct b.uid) as dau -- 当天活跃用户数from(-- 用户活跃表select uid,date(in_time) as dt -- 用户活跃当天from tb_user_logunion select uid,date(out_time) as dtfrom tb_user_log) bgroup by b.dt
) a2 left join(select a.dt ,count(distinct a.uid) as uv_new_daily -- 当天新用户数from(-- 当天新用户表select uid,min(date(in_time)) as dt -- 出现新用户当天 from tb_user_log group by uid ) a group by a.dt
) b2 on b2.dta2.dt
order by a2.dt6.连续签到领金币
题目计算每个用户2021年7月以来每月获得的金币数该活动到10月底结束11月1日开始的签到不再获得金币。结果按月份、ID升序排序。
--输入
DROP TABLE IF EXISTS tb_user_log;
CREATE TABLE tb_user_log (id INT PRIMARY KEY AUTO_INCREMENT COMMENT 自增ID,uid INT NOT NULL COMMENT 用户ID,artical_id INT NOT NULL COMMENT 视频ID,in_time datetime COMMENT 进入时间,out_time datetime COMMENT 离开时间,sign_in TINYINT DEFAULT 0 COMMENT 是否签到
) CHARACTER SET utf8 COLLATE utf8_bin;INSERT INTO tb_user_log(uid, artical_id, in_time, out_time, sign_in) VALUES(101, 0, 2021-07-07 10:00:00, 2021-07-07 10:00:09, 1),(101, 0, 2021-07-08 10:00:00, 2021-07-08 10:00:09, 1),(101, 0, 2021-07-09 10:00:00, 2021-07-09 10:00:42, 1),(101, 0, 2021-07-10 10:00:00, 2021-07-10 10:00:09, 1),(101, 0, 2021-07-11 23:59:55, 2021-07-11 23:59:59, 1),(101, 0, 2021-07-12 10:00:28, 2021-07-12 10:00:50, 1),(101, 0, 2021-07-13 10:00:28, 2021-07-13 10:00:50, 1),(102, 0, 2021-10-01 10:00:28, 2021-10-01 10:00:50, 1),(102, 0, 2021-10-02 10:00:01, 2021-10-02 10:01:50, 1),(102, 0, 2021-10-03 11:00:55, 2021-10-03 11:00:59, 1),(102, 0, 2021-10-04 11:00:45, 2021-10-04 11:00:55, 0),(102, 0, 2021-10-05 11:00:53, 2021-10-05 11:00:59, 1),(102, 0, 2021-10-06 11:00:45, 2021-10-06 11:00:55, 1);select c.uid, DATE_FORMAT(c.sign_dt, %Y%m) as month,sum(case when c.sign_idx0 then 7 when c.sign_idx3 then 3 else 1 end) as coin
from (select b.uid, b.sign_dt,(ROW_NUMBER() over(wd_uid_dt) ) % 7 as sign_idxfrom (select a.uid, -- 用户 a.sign_dt, -- 签到日期a.rn, -- 每次签到序号DATE_SUB(a.sign_dt, INTERVAL a.rn DAY) as base_dt -- 首次签到日期from (select DISTINCT uid, -- 用户DATE(in_time) as sign_dt, -- 签到日期DENSE_RANK() over(partition by uid order by DATE(in_time)) as rn -- 每次签到序号from tb_user_logwhere artical_id 0 and sign_in 1 and DATE(in_time) 2021-07-07 and DATE(in_time) 2021-10-31 ) a) bwindow wd_uid_dt as (partition by b.uid, b.base_dt order by b.sign_dt)
) c
group by c.uid, DATE_FORMAT(c.sign_dt, %Y%m)
order by DATE_FORMAT(c.sign_dt, %Y%m), c.uid;三、电商场景某东商城
1.计算商城中2021年每月的GMV
题目请计算商城中2021年每月的GMV输出GMV大于10w的每月GMV值保留到整数。
--输入
DROP TABLE IF EXISTS tb_order_overall;
CREATE TABLE tb_order_overall (id INT PRIMARY KEY AUTO_INCREMENT COMMENT 自增ID,order_id INT NOT NULL COMMENT 订单号,uid INT NOT NULL COMMENT 用户ID,event_time datetime COMMENT 下单时间,total_amount DECIMAL NOT NULL COMMENT 订单总金额,total_cnt INT NOT NULL COMMENT 订单商品总件数,status TINYINT NOT NULL COMMENT 订单状态
) CHARACTER SET utf8 COLLATE utf8_bin;INSERT INTO tb_order_overall(order_id, uid, event_time, total_amount, total_cnt, status) VALUES(301001, 101, 2021-10-01 10:00:00, 15900, 2, 1),(301002, 101, 2021-10-01 11:00:00, 15900, 2, 1),(301003, 102, 2021-10-02 10:00:00, 34500, 8, 0),(301004, 103, 2021-10-12 10:00:00, 43500, 9, 1),(301005, 105, 2021-11-01 10:00:00, 31900, 7, 1),(301006, 102, 2021-11-02 10:00:00, 24500, 6, 1),(391007, 102, 2021-11-03 10:00:00, -24500, 6, 2),(301008, 104, 2021-11-04 10:00:00, 55500, 12, 0);select DATE_FORMAT(event_time,%Y-%m) as month,round(sum(if(status!2,total_amount,0)),0) as GMV
from tb_order_overall
where year(event_time)2021
group by month
having GMV100000
order by GMV2.统计2021年10月每个退货率不大于0.5的商品各项指标
题目请统计2021年10月每个有展示记录的退货率不大于0.5的商品各项指标。
--输入
DROP TABLE IF EXISTS tb_user_event;
CREATE TABLE tb_user_event (id INT PRIMARY KEY AUTO_INCREMENT COMMENT 自增ID,uid INT NOT NULL COMMENT 用户ID,product_id INT NOT NULL COMMENT 商品ID,event_time datetime COMMENT 行为时间,if_click TINYINT COMMENT 是否点击,if_cart TINYINT COMMENT 是否加购物车,if_payment TINYINT COMMENT 是否付款,if_refund TINYINT COMMENT 是否退货退款
) CHARACTER SET utf8 COLLATE utf8_bin;INSERT INTO tb_user_event(uid, product_id, event_time, if_click, if_cart, if_payment, if_refund) VALUES(101, 8001, 2021-10-01 10:00:00, 0, 0, 0, 0),(102, 8001, 2021-10-01 10:00:00, 1, 0, 0, 0),(103, 8001, 2021-10-01 10:00:00, 1, 1, 0, 0),(104, 8001, 2021-10-02 10:00:00, 1, 1, 1, 0),(105, 8001, 2021-10-02 10:00:00, 1, 1, 1, 0),(101, 8002, 2021-10-03 10:00:00, 1, 1, 1, 0),(109, 8001, 2021-10-04 10:00:00, 1, 1, 1, 1);select a.product_id,round(click_cnt/play_cnt,3) as ctr,round(if(click_cnt0,cart_cnt/click_cnt,0),3) as cart_rate,round(if(cart_cnt0,payment_cnt/cart_cnt,0),3) as payment_rate,round(if(payment_cnt0,refund_cnt/payment_cnt,0),3) as refund_rate
from(select product_id,sum(if_click) as click_cnt,count(1) as play_cnt,sum(if_cart) as cart_cnt,sum(if_payment) as payment_cnt,sum(if_refund) as refund_cntfrom tb_user_eventwhere date_format(event_time,%Y-%m)2021-10 group by product_id
) a
where payment_cnt0 or round(if(payment_cnt0,refund_cnt/payment_cnt,0),3)0.5
order by a.product_id 3.某店铺的各商品毛利率及店铺整体毛利率
题目请计算2021年10月以来店铺901中商品毛利率大于24.9%的商品信息及店铺整体毛利率。
--输入
DROP TABLE IF EXISTS tb_order_overall;
CREATE TABLE tb_order_overall (id INT PRIMARY KEY AUTO_INCREMENT COMMENT 自增ID,order_id INT NOT NULL COMMENT 订单号,uid INT NOT NULL COMMENT 用户ID,event_time datetime COMMENT 下单时间,total_amount DECIMAL NOT NULL COMMENT 订单总金额,total_cnt INT NOT NULL COMMENT 订单商品总件数,status TINYINT NOT NULL COMMENT 订单状态
) CHARACTER SET utf8 COLLATE utf8_bin;INSERT INTO tb_order_overall(order_id, uid, event_time, total_amount, total_cnt, status) VALUES(301001, 101, 2021-10-01 10:00:00, 30000, 3, 1),(301002, 102, 2021-10-01 11:00:00, 23900, 2, 1),(301003, 103, 2021-10-02 10:00:00, 31000, 2, 1);DROP TABLE IF EXISTS tb_product_info;
CREATE TABLE tb_product_info (id INT PRIMARY KEY AUTO_INCREMENT COMMENT 自增ID,product_id INT NOT NULL COMMENT 商品ID,shop_id INT NOT NULL COMMENT 店铺ID,tag VARCHAR(12) COMMENT 商品类别标签,in_price DECIMAL NOT NULL COMMENT 进货价格,quantity INT NOT NULL COMMENT 进货数量,release_time datetime COMMENT 上架时间
) CHARACTER SET utf8 COLLATE utf8_bin;DROP TABLE IF EXISTS tb_order_detail;
CREATE TABLE tb_order_detail (id INT PRIMARY KEY AUTO_INCREMENT COMMENT 自增ID,order_id INT NOT NULL COMMENT 订单号,product_id INT NOT NULL COMMENT 商品ID,price DECIMAL NOT NULL COMMENT 商品单价,cnt INT NOT NULL COMMENT 下单数量
) CHARACTER SET utf8 COLLATE utf8_bin;INSERT INTO tb_product_info(product_id, shop_id, tag, in_price, quantity, release_time) VALUES(8001, 901, 家电, 6000, 100, 2020-01-01 10:00:00),(8002, 902, 家电, 12000, 50, 2020-01-01 10:00:00),(8003, 901, 3C数码, 12000, 50, 2020-01-01 10:00:00);INSERT INTO tb_order_detail(order_id, product_id, price, cnt) VALUES(301001, 8001, 8500, 2),(301001, 8002, 15000, 1),(301002, 8001, 8500, 1),(301002, 8002, 16000, 1),(301003, 8002, 14000, 1),(301003, 8003, 18000, 1);select a3.product_id,concat(a3.profit_rate,%) as profit_rate
from(selectifnull(a2.product_id,店铺汇总) as product_id,round((1-sum(a2.in_price*a2.cnt)/sum(a2.price*a2.cnt))*100,1) as profit_ratefrom(selectc.product_id,c.price,c.cnt,a.in_pricefrom tb_order_detail c left join tb_product_info a on a.product_idc.product_idleft join tb_order_overall b on b.order_idc.order_idwhere date(b.event_time)2021-10-01 and a.shop_id901 and b.status1) a2 group by a2.product_idwith ROLLUPhaving profit_rate24.9 or product_id is nullorder by a2.product_id
) a3 4.零食类商品中复购率top3高的商品
题目请统计零食类商品中复购率top3高的商品。
--输入
DROP TABLE IF EXISTS tb_order_overall;
CREATE TABLE tb_order_overall (id INT PRIMARY KEY AUTO_INCREMENT COMMENT 自增ID,order_id INT NOT NULL COMMENT 订单号,uid INT NOT NULL COMMENT 用户ID,event_time datetime COMMENT 下单时间,total_amount DECIMAL NOT NULL COMMENT 订单总金额,total_cnt INT NOT NULL COMMENT 订单商品总件数,status TINYINT NOT NULL COMMENT 订单状态
) CHARACTER SET utf8 COLLATE utf8_bin;DROP TABLE IF EXISTS tb_product_info;
CREATE TABLE tb_product_info (id INT PRIMARY KEY AUTO_INCREMENT COMMENT 自增ID,product_id INT NOT NULL COMMENT 商品ID,shop_id INT NOT NULL COMMENT 店铺ID,tag VARCHAR(12) COMMENT 商品类别标签,in_price DECIMAL NOT NULL COMMENT 进货价格,quantity INT NOT NULL COMMENT 进货数量,release_time datetime COMMENT 上架时间
) CHARACTER SET utf8 COLLATE utf8_bin;DROP TABLE IF EXISTS tb_order_detail;
CREATE TABLE tb_order_detail (id INT PRIMARY KEY AUTO_INCREMENT COMMENT 自增ID,order_id INT NOT NULL COMMENT 订单号,product_id INT NOT NULL COMMENT 商品ID,price DECIMAL NOT NULL COMMENT 商品单价,cnt INT NOT NULL COMMENT 下单数量
) CHARACTER SET utf8 COLLATE utf8_bin;INSERT INTO tb_product_info(product_id, shop_id, tag, in_price, quantity, release_time) VALUES(8001, 901, 零食, 60, 1000, 2020-01-01 10:00:00),(8002, 901, 零食, 140, 500, 2020-01-01 10:00:00),(8003, 901, 零食, 160, 500, 2020-01-01 10:00:00);INSERT INTO tb_order_overall(order_id, uid, event_time, total_amount, total_cnt, status) VALUES(301001, 101, 2021-09-30 10:00:00, 140, 1, 1),(301002, 102, 2021-10-01 11:00:00, 235, 2, 1),(301011, 102, 2021-10-31 11:00:00, 250, 2, 1),(301003, 101, 2021-11-02 10:00:00, 300, 2, 1),(301013, 105, 2021-11-02 10:00:00, 300, 2, 1),(301005, 104, 2021-11-03 10:00:00, 170, 1, 1);INSERT INTO tb_order_detail(order_id, product_id, price, cnt) VALUES(301001, 8002, 150, 1),(301011, 8003, 200, 1),(301011, 8001, 80, 1),(301002, 8001, 85, 1),(301002, 8003, 180, 1),(301003, 8002, 140, 1),(301003, 8003, 180, 1),(301013, 8002, 140, 2),(301005, 8003, 180, 1);select a2.product_id,round(sum(a2.repurchase)/count(a2.repurchase),3) as repurchase_rate
from(select a.product_id,b.uid,if(count(b.event_time)1,1,0) as repurchasefrom tb_order_overall bjoin tb_order_detail c on b.order_idc.order_idjoin tb_product_info a on a.product_idc.product_idwhere a.tag零食 and date(b.event_time) (select DATE_SUB(max(date(event_time)),INTERVAL 90 day) from tb_order_overall)group by a.product_id,b.uid
) a2
group by a2.product_id
order by repurchase_rate desc,a2.product_id
limit 3;5.10月的新户客单价和获客成本
题目请计算2021年10月商城里所有新用户的首单平均交易金额客单价和平均获客成本保留一位小数。
--输入
DROP TABLE IF EXISTS tb_order_overall;
CREATE TABLE tb_order_overall (id INT PRIMARY KEY AUTO_INCREMENT COMMENT 自增ID,order_id INT NOT NULL COMMENT 订单号,uid INT NOT NULL COMMENT 用户ID,event_time datetime COMMENT 下单时间,total_amount DECIMAL NOT NULL COMMENT 订单总金额,total_cnt INT NOT NULL COMMENT 订单商品总件数,status TINYINT NOT NULL COMMENT 订单状态
) CHARACTER SET utf8 COLLATE utf8_bin;DROP TABLE IF EXISTS tb_product_info;
CREATE TABLE tb_product_info (id INT PRIMARY KEY AUTO_INCREMENT COMMENT 自增ID,product_id INT NOT NULL COMMENT 商品ID,shop_id INT NOT NULL COMMENT 店铺ID,tag VARCHAR(12) COMMENT 商品类别标签,in_price DECIMAL NOT NULL COMMENT 进货价格,quantity INT NOT NULL COMMENT 进货数量,release_time datetime COMMENT 上架时间
) CHARACTER SET utf8 COLLATE utf8_bin;DROP TABLE IF EXISTS tb_order_detail;
CREATE TABLE tb_order_detail (id INT PRIMARY KEY AUTO_INCREMENT COMMENT 自增ID,order_id INT NOT NULL COMMENT 订单号,product_id INT NOT NULL COMMENT 商品ID,price DECIMAL NOT NULL COMMENT 商品单价,cnt INT NOT NULL COMMENT 下单数量
) CHARACTER SET utf8 COLLATE utf8_bin;INSERT INTO tb_product_info(product_id, shop_id, tag, in_price, quantity, release_time) VALUES(8001, 901, 日用, 60, 1000, 2020-01-01 10:00:00),(8002, 901, 零食, 140, 500, 2020-01-01 10:00:00),(8003, 901, 零食, 160, 500, 2020-01-01 10:00:00),(8004, 902, 零食, 130, 500, 2020-01-01 10:00:00);INSERT INTO tb_order_overall(order_id, uid, event_time, total_amount, total_cnt, status) VALUES(301002, 102, 2021-10-01 11:00:00, 235, 2, 1),(301003, 101, 2021-10-02 10:00:00, 300, 2, 1),(301005, 104, 2021-10-03 10:00:00, 160, 1, 1);INSERT INTO tb_order_detail(order_id, product_id, price, cnt) VALUES(301002, 8001, 85, 1),(301002, 8003, 180, 1),(301003, 8004, 140, 1),(301003, 8003, 180, 1),(301005, 8003, 180, 1);select round(avg(total_amount), 1) as avg_amount,round(avg(raw_amount-total_amount), 1) as avg_cost
from (select uid, total_amount, raw_amountfrom(select distinct uid,first_value(event_time) over(wd_uid_first) as event_time,first_value(order_id) over(wd_uid_first) as order_id,first_value(total_amount) over(wd_uid_first) as total_amountfrom tb_order_overallwindow wd_uid_first as (partition by uid order by event_time))a join (select order_id, sum(price * cnt) as raw_amountfrom tb_order_detailgroup by order_id)b on a.order_idb.order_idwhere date_format(event_time, %Y-%m) 2021-10
)c6.店铺901国庆期间的7日动销率和滞销率
题目请计算店铺901在2021年国庆头3天的7日动销率和滞销率结果保留3位小数按日期升序排序。
--输入
DROP TABLE IF EXISTS tb_order_overall;
CREATE TABLE tb_order_overall (id INT PRIMARY KEY AUTO_INCREMENT COMMENT 自增ID,order_id INT NOT NULL COMMENT 订单号,uid INT NOT NULL COMMENT 用户ID,event_time datetime COMMENT 下单时间,total_amount DECIMAL NOT NULL COMMENT 订单总金额,total_cnt INT NOT NULL COMMENT 订单商品总件数,status TINYINT NOT NULL COMMENT 订单状态
) CHARACTER SET utf8 COLLATE utf8_bin;DROP TABLE IF EXISTS tb_product_info;
CREATE TABLE tb_product_info (id INT PRIMARY KEY AUTO_INCREMENT COMMENT 自增ID,product_id INT NOT NULL COMMENT 商品ID,shop_id INT NOT NULL COMMENT 店铺ID,tag VARCHAR(12) COMMENT 商品类别标签,in_price DECIMAL NOT NULL COMMENT 进货价格,quantity INT NOT NULL COMMENT 进货数量,release_time datetime COMMENT 上架时间
) CHARACTER SET utf8 COLLATE utf8_bin;DROP TABLE IF EXISTS tb_order_detail;
CREATE TABLE tb_order_detail (id INT PRIMARY KEY AUTO_INCREMENT COMMENT 自增ID,order_id INT NOT NULL COMMENT 订单号,product_id INT NOT NULL COMMENT 商品ID,price DECIMAL NOT NULL COMMENT 商品单价,cnt INT NOT NULL COMMENT 下单数量
) CHARACTER SET utf8 COLLATE utf8_bin;INSERT INTO tb_product_info(product_id, shop_id, tag, in_price, quantity, release_time) VALUES(8001, 901, 日用, 60, 1000, 2020-01-01 10:00:00),(8002, 901, 零食, 140, 500, 2020-01-01 10:00:00),(8003, 901, 零食, 160, 500, 2020-01-01 10:00:00);INSERT INTO tb_order_overall(order_id, uid, event_time, total_amount, total_cnt, status) VALUES(301004, 102, 2021-09-30 10:00:00, 170, 1, 1),(301005, 104, 2021-10-01 10:00:00, 160, 1, 1),(301003, 101, 2021-10-02 10:00:00, 300, 2, 1),(301002, 102, 2021-10-03 11:00:00, 235, 2, 1);INSERT INTO tb_order_detail(order_id, product_id, price, cnt) VALUES(301004, 8002, 180, 1),(301005, 8002, 170, 1),(301002, 8001, 85, 1),(301002, 8003, 180, 1),(301003, 8002, 150, 1),(301003, 8003, 180, 1);select dt, sale_rate, 1 - sale_rate as unsale_rate
from (select dt, round(min(sale_pid_cnt) / count(all_pid), 3) as sale_ratefrom (-- 国庆期间店铺901截止每天的近7天有销量的商品数select dt, count(distinct if(shop_id!901, null, product_id)) as sale_pid_cntfrom (select distinct date(event_time) as dtfrom tb_order_overallwhere date(event_time) between 2021-10-01 and 2021-10-03) as t_datesleft join (select distinct date(event_time) as event_dt, product_idfrom tb_order_overalljoin tb_order_detail using(order_id)) as t_dt_pid on datediff(dt,event_dt) between 0 and 6left join tb_product_info using(product_id)group by dt) as t_dt_901_pid_cntleft join (-- 店铺901每个商品上架日期select date(release_time) as release_dt, product_id as all_pidfrom tb_product_infowhere shop_id901) as t_release_dt on dt release_dt -- 当天店铺901已上架在售的商品group by dt
) as t_dt_sr;四、出行场景某滴打车
1.2021年国庆在北京接单3次及以上的司机统计信息
题目请统计2021年国庆7天期间在北京市接单至少3次的司机的平均接单数和平均兼职收入暂不考虑平台佣金直接计算完成的订单费用总额结果保留3位小数。
DROP TABLE IF EXISTS tb_get_car_record,tb_get_car_order;
CREATE TABLE tb_get_car_record (id INT PRIMARY KEY AUTO_INCREMENT COMMENT 自增ID,uid INT NOT NULL COMMENT 用户ID,city VARCHAR(10) NOT NULL COMMENT 城市,event_time datetime COMMENT 打车时间,end_time datetime COMMENT 打车结束时间,order_id INT COMMENT 订单号
) CHARACTER SET utf8 COLLATE utf8_bin;CREATE TABLE tb_get_car_order (id INT PRIMARY KEY AUTO_INCREMENT COMMENT 自增ID,order_id INT NOT NULL COMMENT 订单号,uid INT NOT NULL COMMENT 用户ID,driver_id INT NOT NULL COMMENT 司机ID,order_time datetime COMMENT 接单时间,start_time datetime COMMENT 开始计费的上车时间,finish_time datetime COMMENT 订单结束时间,mileage DOUBLE COMMENT 行驶里程数,fare DOUBLE COMMENT 费用,grade TINYINT COMMENT 评分
) CHARACTER SET utf8 COLLATE utf8_bin;INSERT INTO tb_get_car_record(uid, city, event_time, end_time, order_id) VALUES(101, 北京, 2021-10-01 07:00:00, 2021-10-01 07:02:00, null),(102, 北京, 2021-10-01 09:00:30, 2021-10-01 09:01:00, 9001),(101, 北京, 2021-10-01 08:28:10, 2021-10-01 08:30:00, 9002),(103, 北京, 2021-10-02 07:59:00, 2021-10-02 08:01:00, 9003),(104, 北京, 2021-10-03 07:59:20, 2021-10-03 08:01:00, 9004),(105, 北京, 2021-10-01 08:00:00, 2021-10-01 08:02:10, 9005),(106, 北京, 2021-10-01 17:58:00, 2021-10-01 18:01:00, 9006),(107, 北京, 2021-10-02 11:00:00, 2021-10-02 11:01:00, 9007),(108, 北京, 2021-10-02 21:00:00, 2021-10-02 21:01:00, 9008) ;INSERT INTO tb_get_car_order(order_id, uid, driver_id, order_time, start_time, finish_time, mileage, fare, grade) VALUES(9002, 101, 201, 2021-10-01 08:30:00, null, 2021-10-01 08:31:00, null, null, null),(9001, 102, 202, 2021-10-01 09:01:00, 2021-10-01 09:06:00, 2021-10-01 09:31:00, 10.0, 41.5, 5),(9003, 103, 202, 2021-10-02 08:01:00, 2021-10-02 08:15:00, 2021-10-02 08:31:00, 11.0, 41.5, 4),(9004, 104, 202, 2021-10-03 08:01:00, 2021-10-03 08:13:00, 2021-10-03 08:31:00, 7.5, 22, 4),(9005, 105, 203, 2021-10-01 08:02:10, 2021-10-01 08:18:00, 2021-10-01 08:31:00, 15.0, 44, 5),(9006, 106, 203, 2021-10-01 18:01:00, 2021-10-01 18:09:00, 2021-10-01 18:31:00, 8.0, 25, 5),(9007, 107, 203, 2021-10-02 11:01:00, 2021-10-02 11:07:00, 2021-10-02 11:31:00, 9.9, 30, 5),(9008, 108, 203, 2021-10-02 21:01:00, 2021-10-02 21:10:00, 2021-10-02 21:31:00, 13.2, 38, 4);2.有取消订单记录的司机平均评分
题目请找到2021年10月有过取消订单记录的司机计算他们每人全部已完成的有评分订单的平均评分及总体平均评分保留1位小数。先按driver_id升序输出再输出总体情况。
--输入
DROP TABLE IF EXISTS tb_get_car_record,tb_get_car_order;
CREATE TABLE tb_get_car_record (id INT PRIMARY KEY AUTO_INCREMENT COMMENT 自增ID,uid INT NOT NULL COMMENT 用户ID,city VARCHAR(10) NOT NULL COMMENT 城市,event_time datetime COMMENT 打车时间,end_time datetime COMMENT 打车结束时间,order_id INT COMMENT 订单号
) CHARACTER SET utf8 COLLATE utf8_bin;CREATE TABLE tb_get_car_order (id INT PRIMARY KEY AUTO_INCREMENT COMMENT 自增ID,order_id INT NOT NULL COMMENT 订单号,uid INT NOT NULL COMMENT 用户ID,driver_id INT NOT NULL COMMENT 司机ID,order_time datetime COMMENT 接单时间,start_time datetime COMMENT 开始计费的上车时间,finish_time datetime COMMENT 订单结束时间,mileage FLOAT COMMENT 行驶里程数,fare FLOAT COMMENT 费用,grade TINYINT COMMENT 评分
) CHARACTER SET utf8 COLLATE utf8_bin;INSERT INTO tb_get_car_record(uid, city, event_time, end_time, order_id) VALUES(101, 北京, 2021-10-01 07:00:00, 2021-10-01 07:02:00, null),(102, 北京, 2021-10-01 09:00:30, 2021-10-01 09:01:00, 9001),(101, 北京, 2021-10-01 08:28:10, 2021-10-01 08:30:00, 9002),(103, 北京, 2021-10-02 07:59:00, 2021-10-02 08:01:00, 9003),(104, 北京, 2021-10-03 07:59:20, 2021-10-03 08:01:00, 9004),(105, 北京, 2021-10-01 08:00:00, 2021-10-01 08:02:10, 9005),(106, 北京, 2021-10-01 17:58:00, 2021-10-01 18:01:00, 9006),(107, 北京, 2021-10-02 11:00:00, 2021-10-02 11:01:00, 9007),(108, 北京, 2021-10-02 21:00:00, 2021-10-02 21:01:00, 9008),(109, 北京, 2021-10-08 18:00:00, 2021-10-08 18:01:00, 9009);INSERT INTO tb_get_car_order(order_id, uid, driver_id, order_time, start_time, finish_time, mileage, fare, grade) VALUES(9002, 101, 202, 2021-10-01 08:30:00, null, 2021-10-01 08:31:00, null, null, null),(9001, 102, 202, 2021-10-01 09:01:00, 2021-10-01 09:06:00, 2021-10-01 09:31:00, 10.0, 41.5, 5),(9003, 103, 202, 2021-10-02 08:01:00, 2021-10-02 08:15:00, 2021-10-02 08:31:00, 11.0, 41.5, 4),(9004, 104, 202, 2021-10-03 08:01:00, 2021-10-03 08:13:00, 2021-10-03 08:31:00, 7.5, 22, 4),(9005, 105, 203, 2021-10-01 08:02:10, null, 2021-10-01 08:31:00, null, null, null),(9006, 106, 203, 2021-10-01 18:01:00, 2021-10-01 18:09:00, 2021-10-01 18:31:00, 8.0, 25.5, 5),(9007, 107, 203, 2021-10-02 11:01:00, 2021-10-02 11:07:00, 2021-10-02 11:31:00, 9.9, 30, 5),(9008, 108, 203, 2021-10-02 21:01:00, 2021-10-02 21:10:00, 2021-10-02 21:31:00, 13.2, 38, 4),(9009, 109, 203, 2021-10-08 18:01:00, 2021-10-08 18:11:50, 2021-10-08 18:51:00, 13, 40, 5);3.每个城市中评分最高的司机信息
题目请统计每个城市中评分最高的司机平均评分、日均接单量和日均行驶里程数。
--输入
DROP TABLE IF EXISTS tb_get_car_record,tb_get_car_order;
CREATE TABLE tb_get_car_record (id INT PRIMARY KEY AUTO_INCREMENT COMMENT 自增ID,uid INT NOT NULL COMMENT 用户ID,city VARCHAR(10) NOT NULL COMMENT 城市,event_time datetime COMMENT 打车时间,end_time datetime COMMENT 打车结束时间,order_id INT COMMENT 订单号
) CHARACTER SET utf8 COLLATE utf8_bin;CREATE TABLE tb_get_car_order (id INT PRIMARY KEY AUTO_INCREMENT COMMENT 自增ID,order_id INT NOT NULL COMMENT 订单号,uid INT NOT NULL COMMENT 用户ID,driver_id INT NOT NULL COMMENT 司机ID,order_time datetime COMMENT 接单时间,start_time datetime COMMENT 开始计费的上车时间,finish_time datetime COMMENT 订单结束时间,mileage FLOAT COMMENT 行驶里程数,fare FLOAT COMMENT 费用,grade TINYINT COMMENT 评分
) CHARACTER SET utf8 COLLATE utf8_bin;INSERT INTO tb_get_car_record(uid, city, event_time, end_time, order_id) VALUES(101, 北京, 2021-10-01 07:00:00, 2021-10-01 07:02:00, null),(102, 北京, 2021-10-01 09:00:30, 2021-10-01 09:01:00, 9001),(101, 北京, 2021-10-01 08:28:10, 2021-10-01 08:30:00, 9002),(103, 北京, 2021-10-02 07:59:00, 2021-10-02 08:01:00, 9003),(104, 北京, 2021-10-03 07:59:20, 2021-10-03 08:01:00, 9004),(105, 北京, 2021-10-01 08:00:00, 2021-10-01 08:02:10, 9005),(106, 北京, 2021-10-01 17:58:00, 2021-10-01 18:01:00, 9006),(107, 北京, 2021-10-02 11:00:00, 2021-10-02 11:01:00, 9007),(108, 北京, 2021-10-02 21:00:00, 2021-10-02 21:01:00, 9008),(109, 北京, 2021-10-08 18:00:00, 2021-10-08 18:01:00, 9009);INSERT INTO tb_get_car_order(order_id, uid, driver_id, order_time, start_time, finish_time, mileage, fare, grade) VALUES(9002, 101, 202, 2021-10-01 08:30:00, null, 2021-10-01 08:31:00, null, null, null),(9001, 102, 202, 2021-10-01 09:01:00, 2021-10-01 09:06:00, 2021-10-01 09:31:00, 10.0, 41.5, 5),(9003, 103, 202, 2021-10-02 08:01:00, 2021-10-02 08:15:00, 2021-10-02 08:31:00, 11.0, 41.5, 4),(9004, 104, 202, 2021-10-03 08:01:00, 2021-10-03 08:13:00, 2021-10-03 08:31:00, 7.5, 22, 4),(9005, 105, 203, 2021-10-01 08:02:10, null, 2021-10-01 08:31:00, null, null, null),(9006, 106, 203, 2021-10-01 18:01:00, 2021-10-01 18:09:00, 2021-10-01 18:31:00, 8.0, 25.5, 5),(9007, 107, 203, 2021-10-02 11:01:00, 2021-10-02 11:07:00, 2021-10-02 11:31:00, 9.9, 30, 5),(9008, 108, 203, 2021-10-02 21:01:00, 2021-10-02 21:10:00, 2021-10-02 21:31:00, 13.2, 38, 4),(9009, 109, 203, 2021-10-08 18:01:00, 2021-10-08 18:11:50, 2021-10-08 18:51:00, 13, 40, 5);4.国庆期间近7日日均取消订单量
题目请统计国庆头3天里每天的近7日日均订单完成量和日均订单取消量按日期升序排序。结果保留2位小数。
--输入
DROP TABLE IF EXISTS tb_get_car_record,tb_get_car_order;
CREATE TABLE tb_get_car_record (id INT PRIMARY KEY AUTO_INCREMENT COMMENT 自增ID,uid INT NOT NULL COMMENT 用户ID,city VARCHAR(10) NOT NULL COMMENT 城市,event_time datetime COMMENT 打车时间,end_time datetime COMMENT 打车结束时间,order_id INT COMMENT 订单号
) CHARACTER SET utf8 COLLATE utf8_bin;CREATE TABLE tb_get_car_order (id INT PRIMARY KEY AUTO_INCREMENT COMMENT 自增ID,order_id INT NOT NULL COMMENT 订单号,uid INT NOT NULL COMMENT 用户ID,driver_id INT NOT NULL COMMENT 司机ID,order_time datetime COMMENT 接单时间,start_time datetime COMMENT 开始计费的上车时间,finish_time datetime COMMENT 订单结束时间,mileage FLOAT COMMENT 行驶里程数,fare FLOAT COMMENT 费用,grade TINYINT COMMENT 评分
) CHARACTER SET utf8 COLLATE utf8_bin;INSERT INTO tb_get_car_record(uid, city, event_time, end_time, order_id) VALUES(101, 北京, 2021-09-25 08:28:10, 2021-09-25 08:30:00, 9011),(102, 北京, 2021-09-25 09:00:30, 2021-09-25 09:01:00, 9012),(103, 北京, 2021-09-26 07:59:00, 2021-09-26 08:01:00, 9013),(104, 北京, 2021-09-26 07:59:00, 2021-09-26 08:01:00, 9023),(104, 北京, 2021-09-27 07:59:20, 2021-09-27 08:01:00, 9014),(105, 北京, 2021-09-28 08:00:00, 2021-09-28 08:02:10, 9015),(106, 北京, 2021-09-29 17:58:00, 2021-09-29 18:01:00, 9016),(107, 北京, 2021-09-30 11:00:00, 2021-09-30 11:01:00, 9017),(108, 北京, 2021-09-30 21:00:00, 2021-09-30 21:01:00, 9018),(102, 北京, 2021-10-01 09:00:30, 2021-10-01 09:01:00, 9002),(106, 北京, 2021-10-01 17:58:00, 2021-10-01 18:01:00, 9006),(101, 北京, 2021-10-02 08:28:10, 2021-10-02 08:30:00, 9001),(107, 北京, 2021-10-02 11:00:00, 2021-10-02 11:01:00, 9007),(108, 北京, 2021-10-02 21:00:00, 2021-10-02 21:01:00, 9008),(103, 北京, 2021-10-02 07:59:00, 2021-10-02 08:01:00, 9003),(104, 北京, 2021-10-03 07:59:20, 2021-10-03 08:01:00, 9004),(109, 北京, 2021-10-03 18:00:00, 2021-10-03 18:01:00, 9009);INSERT INTO tb_get_car_order(order_id, uid, driver_id, order_time, start_time, finish_time, mileage, fare, grade) VALUES(9011, 101, 211, 2021-09-25 08:30:00, 2021-09-25 08:31:00, 2021-09-25 08:54:00, 10, 35, 5),(9012, 102, 211, 2021-09-25 09:01:00, 2021-09-25 09:01:50, 2021-09-25 09:28:00, 11, 32, 5),(9013, 103, 212, 2021-09-26 08:01:00, 2021-09-26 08:03:00, 2021-09-26 08:27:00, 12, 31, 4),(9023, 104, 213, 2021-09-26 08:01:00, null, 2021-09-26 08:27:00, null, null, null),(9014, 104, 212, 2021-09-27 08:01:00, 2021-09-27 08:04:00, 2021-09-27 08:21:00, 11, 31, 5),(9015, 105, 212, 2021-09-28 08:02:10, 2021-09-28 08:04:10, 2021-09-28 08:25:10, 12, 31, 4),(9016, 106, 213, 2021-09-29 18:01:00, 2021-09-29 18:02:10, 2021-09-29 18:23:00, 11, 39, 4),(9017, 107, 213, 2021-09-30 11:01:00, 2021-09-30 11:01:40, 2021-09-30 11:31:00, 11, 38, 5),(9018, 108, 214, 2021-09-30 21:01:00, 2021-09-30 21:02:50, 2021-09-30 21:21:00, 14, 38, 5),(9002, 102, 202, 2021-10-01 09:01:00, 2021-10-01 09:06:00, 2021-10-01 09:31:00, 10.0, 41.5, 5),(9006, 106, 203, 2021-10-01 18:01:00, 2021-10-01 18:09:00, 2021-10-01 18:31:00, 8.0, 25.5, 4),(9001, 101, 202, 2021-10-02 08:30:00, null, 2021-10-02 08:31:00, null, null, null),(9007, 107, 203, 2021-10-02 11:01:00, 2021-10-02 11:07:00, 2021-10-02 11:31:00, 9.9, 30, 5),(9008, 108, 204, 2021-10-02 21:01:00, 2021-10-02 21:10:00, 2021-10-02 21:31:00, 13.2, 38, 4),(9003, 103, 202, 2021-10-02 08:01:00, 2021-10-02 08:15:00, 2021-10-02 08:31:00, 11.0, 41.5, 4),(9004, 104, 202, 2021-10-03 08:01:00, 2021-10-03 08:13:00, 2021-10-03 08:31:00, 7.5, 22, 4),(9009, 109, 204, 2021-10-03 18:01:00, null, 2021-10-03 18:51:00, null, null, null);5.工作日各时段叫车量、等待接单时间和调度时间
题目统计周一到周五各时段的叫车量、平均等待接单时间和平均调度时间。全部以event_time-开始打车时间为时段划分依据平均等待接单时间和平均调度时间均保留1位小数平均调度时间仅计算完成了的订单结果按叫车量升序排序。
--输入
DROP TABLE IF EXISTS tb_get_car_record,tb_get_car_order;
CREATE TABLE tb_get_car_record (id INT PRIMARY KEY AUTO_INCREMENT COMMENT 自增ID,uid INT NOT NULL COMMENT 用户ID,city VARCHAR(10) NOT NULL COMMENT 城市,event_time datetime COMMENT 打车时间,end_time datetime COMMENT 打车结束时间,order_id INT COMMENT 订单号
) CHARACTER SET utf8 COLLATE utf8_bin;CREATE TABLE tb_get_car_order (id INT PRIMARY KEY AUTO_INCREMENT COMMENT 自增ID,order_id INT NOT NULL COMMENT 订单号,uid INT NOT NULL COMMENT 用户ID,driver_id INT NOT NULL COMMENT 司机ID,order_time datetime COMMENT 接单时间,start_time datetime COMMENT 开始计费的上车时间,finish_time datetime COMMENT 订单结束时间,mileage FLOAT COMMENT 行驶里程数,fare FLOAT COMMENT 费用,grade TINYINT COMMENT 评分
) CHARACTER SET utf8 COLLATE utf8_bin;INSERT INTO tb_get_car_record(uid, city, event_time, end_time, order_id) VALUES(107, 北京, 2021-09-20 11:00:00, 2021-09-20 11:00:30, 9017),(108, 北京, 2021-09-20 21:00:00, 2021-09-20 21:00:40, 9008),(108, 北京, 2021-09-20 18:59:30, 2021-09-20 19:01:00, 9018),(102, 北京, 2021-09-21 08:59:00, 2021-09-21 09:01:00, 9002),(106, 北京, 2021-09-21 17:58:00, 2021-09-21 18:01:00, 9006),(103, 北京, 2021-09-22 07:58:00, 2021-09-22 08:01:00, 9003),(104, 北京, 2021-09-23 07:59:00, 2021-09-23 08:01:00, 9004),(103, 北京, 2021-09-24 19:59:20, 2021-09-24 20:01:00, 9019),(101, 北京, 2021-09-24 08:28:10, 2021-09-24 08:30:00, 9011);INSERT INTO tb_get_car_order(order_id, uid, driver_id, order_time, start_time, finish_time, mileage, fare, grade) VALUES(9017, 107, 213, 2021-09-20 11:00:30, 2021-09-20 11:02:10, 2021-09-20 11:31:00, 11, 38, 5),(9008, 108, 204, 2021-09-20 21:00:40, 2021-09-20 21:03:00, 2021-09-20 21:31:00, 13.2, 38, 4),(9018, 108, 214, 2021-09-20 19:01:00, 2021-09-20 19:04:50, 2021-09-20 19:21:00, 14, 38, 5),(9002, 102, 202, 2021-09-21 09:01:00, 2021-09-21 09:06:00, 2021-09-21 09:31:00, 10.0, 41.5, 5),(9006, 106, 203, 2021-09-21 18:01:00, 2021-09-21 18:09:00, 2021-09-21 18:31:00, 8.0, 25.5, 4),(9007, 107, 203, 2021-09-22 11:01:00, 2021-09-22 11:07:00, 2021-09-22 11:31:00, 9.9, 30, 5),(9003, 103, 202, 2021-09-22 08:01:00, 2021-09-22 08:15:00, 2021-09-22 08:31:00, 11.0, 41.5, 4),(9004, 104, 202, 2021-09-23 08:01:00, 2021-09-23 08:13:00, 2021-09-23 08:31:00, 7.5, 22, 4),(9005, 105, 202, 2021-09-23 10:01:00, 2021-09-23 10:13:00, 2021-09-23 10:31:00, 9, 29, 5),(9019, 103, 202, 2021-09-24 20:01:00, 2021-09-24 20:11:00, 2021-09-24 20:51:00, 10, 39, 4),(9011, 101, 211, 2021-09-24 08:30:00, 2021-09-24 08:31:00, 2021-09-24 08:54:00, 10, 35, 5);6.各城市最大同时等车人数
题目请统计各个城市在2021年10月期间单日中最大的同时等车人数。
DROP TABLE IF EXISTS tb_get_car_record,tb_get_car_order;
CREATE TABLE tb_get_car_record (id INT PRIMARY KEY AUTO_INCREMENT COMMENT 自增ID,uid INT NOT NULL COMMENT 用户ID,city VARCHAR(10) NOT NULL COMMENT 城市,event_time datetime COMMENT 打车时间,end_time datetime COMMENT 打车结束时间,order_id INT COMMENT 订单号
) CHARACTER SET utf8 COLLATE utf8_bin;CREATE TABLE tb_get_car_order (id INT PRIMARY KEY AUTO_INCREMENT COMMENT 自增ID,order_id INT NOT NULL COMMENT 订单号,uid INT NOT NULL COMMENT 用户ID,driver_id INT NOT NULL COMMENT 司机ID,order_time datetime COMMENT 接单时间,start_time datetime COMMENT 开始计费的上车时间,finish_time datetime COMMENT 订单结束时间,mileage FLOAT COMMENT 行驶里程数,fare FLOAT COMMENT 费用,grade TINYINT COMMENT 评分
) CHARACTER SET utf8 COLLATE utf8_bin;INSERT INTO tb_get_car_record(uid, city, event_time, end_time, order_id) VALUES(108, 北京, 2021-10-20 08:00:00, 2021-10-20 08:00:40, 9008),(108, 北京, 2021-10-20 08:00:10, 2021-10-20 08:00:45, 9018),(102, 北京, 2021-10-20 08:00:30, 2021-10-20 08:00:50, 9002),(106, 北京, 2021-10-20 08:05:41, 2021-10-20 08:06:00, 9006),(103, 北京, 2021-10-20 08:05:50, 2021-10-20 08:07:10, 9003),(104, 北京, 2021-10-20 08:01:01, 2021-10-20 08:01:20, 9004),(103, 北京, 2021-10-20 08:01:15, 2021-10-20 08:01:30, 9019),(101, 北京, 2021-10-20 08:28:10, 2021-10-20 08:30:00, 9011);INSERT INTO tb_get_car_order(order_id, uid, driver_id, order_time, start_time, finish_time, mileage, fare, grade) VALUES(9008, 108, 204, 2021-10-20 08:00:40, 2021-10-20 08:03:00, 2021-10-20 08:31:00, 13.2, 38, 4),(9018, 108, 214, 2021-10-20 08:00:45, 2021-10-20 08:04:50, 2021-10-20 08:21:00, 14, 38, 5),(9002, 102, 202, 2021-10-20 08:00:50, 2021-10-20 08:06:00, 2021-10-20 08:31:00, 10.0, 41.5, 5),(9006, 106, 203, 2021-10-20 08:06:00, 2021-10-20 08:09:00, 2021-10-20 08:31:00, 8.0, 25.5, 4),(9003, 103, 202, 2021-10-20 08:07:10, 2021-10-20 08:15:00, 2021-10-20 08:31:00, 11.0, 41.5, 4),(9004, 104, 202, 2021-10-20 08:01:20, 2021-10-20 08:13:00, 2021-10-20 08:31:00, 7.5, 22, 4),(9019, 103, 202, 2021-10-20 08:01:30, 2021-10-20 08:11:00, 2021-10-20 08:51:00, 10, 39, 4),(9011, 101, 211, 2021-10-20 08:30:00, 2021-10-20 08:31:00, 2021-10-20 08:54:00, 10, 35, 5);五、某宝店铺分析电商模式
1.某宝店铺的SPU数量
题目11月结束后小牛同学需要对其在某宝的网店就11月份用户交易情况和产品情况进行分析以更好的经营小店。请你统计每款的SPU货号数量并按SPU数量降序排序
--输入
drop table if exists product_tb;
CREATE TABLE product_tb(
item_id char(10) NOT NULL,
style_id char(10) NOT NULL,
tag_price int(10) NOT NULL,
inventory int(10) NOT NULL
);
INSERT INTO product_tb VALUES(A001, A, 100, 20);
INSERT INTO product_tb VALUES(A002, A, 120, 30);
INSERT INTO product_tb VALUES(A003, A, 200, 15);
INSERT INTO product_tb VALUES(B001, B, 130, 18);
INSERT INTO product_tb VALUES(B002, B, 150, 22);
INSERT INTO product_tb VALUES(B003, B, 125, 10);
INSERT INTO product_tb VALUES(B004, B, 155, 12);
INSERT INTO product_tb VALUES(C001, C, 260, 25);
INSERT INTO product_tb VALUES(C002, C, 280, 18);2.某宝店铺的实际销售额与客单价
题目11月结束后小牛同学需要对其在某宝的网店就11月份用户交易情况和产品情况进行分析以更好的经营小店。请你统计实际总销售额与客单价人均付费总收入/总用户数结果保留两位小数
--输入
drop table if exists sales_tb;
CREATE TABLE sales_tb(
sales_date date NOT NULL,
user_id int(10) NOT NULL,
item_id char(10) NOT NULL,
sales_num int(10) NOT NULL,
sales_price int(10) NOT NULL
);INSERT INTO sales_tb VALUES(2021-11-1, 1, A001, 1, 90);
INSERT INTO sales_tb VALUES(2021-11-1, 2, A002, 2, 220);
INSERT INTO sales_tb VALUES(2021-11-1, 2, B001, 1, 120);
INSERT INTO sales_tb VALUES(2021-11-2, 3, C001, 2, 500);
INSERT INTO sales_tb VALUES(2021-11-2, 4, B001, 1, 120);
INSERT INTO sales_tb VALUES(2021-11-3, 5, C001, 1, 240);
INSERT INTO sales_tb VALUES(2021-11-3, 6, C002, 1, 270);
INSERT INTO sales_tb VALUES(2021-11-4, 7, A003, 1, 180);
INSERT INTO sales_tb VALUES(2021-11-4, 8, B002, 1, 140);
INSERT INTO sales_tb VALUES(2021-11-4, 9, B001, 1, 125);
INSERT INTO sales_tb VALUES(2021-11-5, 10, B003, 1, 120);
INSERT INTO sales_tb VALUES(2021-11-5, 10, B004, 1, 150);
INSERT INTO sales_tb VALUES(2021-11-5, 10, A003, 1, 180);
INSERT INTO sales_tb VALUES(2021-11-6, 11, B003, 1, 120);
INSERT INTO sales_tb VALUES(2021-11-6, 10, B004, 1, 150);3.某宝店铺折扣率
题目11月结束后小牛同学需要对其在某宝的网店就11月份用户交易情况和产品情况进行分析以更好的经营小店。请你统计折扣率GMV/吊牌金额GMV指的是成交金额
--输入
drop table if exists product_tb;
CREATE TABLE product_tb(
item_id char(10) NOT NULL,
style_id char(10) NOT NULL,
tag_price int(10) NOT NULL,
inventory int(10) NOT NULL
);
INSERT INTO product_tb VALUES(A001, A, 100, 20);
INSERT INTO product_tb VALUES(A002, A, 120, 30);
INSERT INTO product_tb VALUES(A003, A, 200, 15);
INSERT INTO product_tb VALUES(B001, B, 130, 18);
INSERT INTO product_tb VALUES(B002, B, 150, 22);
INSERT INTO product_tb VALUES(B003, B, 125, 10);
INSERT INTO product_tb VALUES(B004, B, 155, 12);
INSERT INTO product_tb VALUES(C001, C, 260, 25);
INSERT INTO product_tb VALUES(C002, C, 280, 18);drop table if exists sales_tb;
CREATE TABLE sales_tb(
sales_date date NOT NULL,
user_id int(10) NOT NULL,
item_id char(10) NOT NULL,
sales_num int(10) NOT NULL,
sales_price int(10) NOT NULL
);INSERT INTO sales_tb VALUES(2021-11-1, 1, A001, 1, 90);
INSERT INTO sales_tb VALUES(2021-11-1, 2, A002, 2, 220);
INSERT INTO sales_tb VALUES(2021-11-1, 2, B001, 1, 120);
INSERT INTO sales_tb VALUES(2021-11-2, 3, C001, 2, 500);
INSERT INTO sales_tb VALUES(2021-11-2, 4, B001, 1, 120);
INSERT INTO sales_tb VALUES(2021-11-3, 5, C001, 1, 240);
INSERT INTO sales_tb VALUES(2021-11-3, 6, C002, 1, 270);
INSERT INTO sales_tb VALUES(2021-11-4, 7, A003, 1, 180);
INSERT INTO sales_tb VALUES(2021-11-4, 8, B002, 1, 140);
INSERT INTO sales_tb VALUES(2021-11-4, 9, B001, 1, 125);
INSERT INTO sales_tb VALUES(2021-11-5, 10, B003, 1, 120);
INSERT INTO sales_tb VALUES(2021-11-5, 10, B004, 1, 150);
INSERT INTO sales_tb VALUES(2021-11-5, 10, A003, 1, 180);
INSERT INTO sales_tb VALUES(2021-11-6, 11, B003, 1, 120);
INSERT INTO sales_tb VALUES(2021-11-6, 10, B004, 1, 150);4.某宝店铺动销率与售罄率
题目11月结束后小牛同学需要对其在某宝的网店就11月份用户交易情况和产品情况进行分析以更好的经营小店。请你统计每款的动销率pin_rate有销售的SKU数量/在售SKU数量与售罄率sell-through_rateGMV/备货值备货值吊牌价*库存数按style_id升序排序。
--输入
drop table if exists product_tb;
CREATE TABLE product_tb(
item_id char(10) NOT NULL,
style_id char(10) NOT NULL,
tag_price int(10) NOT NULL,
inventory int(10) NOT NULL
);
INSERT INTO product_tb VALUES(A001, A, 100, 20);
INSERT INTO product_tb VALUES(A002, A, 120, 30);
INSERT INTO product_tb VALUES(A003, A, 200, 15);
INSERT INTO product_tb VALUES(B001, B, 130, 18);
INSERT INTO product_tb VALUES(B002, B, 150, 22);
INSERT INTO product_tb VALUES(B003, B, 125, 10);
INSERT INTO product_tb VALUES(B004, B, 155, 12);
INSERT INTO product_tb VALUES(C001, C, 260, 25);
INSERT INTO product_tb VALUES(C002, C, 280, 18);drop table if exists sales_tb;
CREATE TABLE sales_tb(
sales_date date NOT NULL,
user_id int(10) NOT NULL,
item_id char(10) NOT NULL,
sales_num int(10) NOT NULL,
sales_price int(10) NOT NULL
);INSERT INTO sales_tb VALUES(2021-11-1, 1, A001, 1, 90);
INSERT INTO sales_tb VALUES(2021-11-1, 2, A002, 2, 220);
INSERT INTO sales_tb VALUES(2021-11-1, 2, B001, 1, 120);
INSERT INTO sales_tb VALUES(2021-11-2, 3, C001, 2, 500);
INSERT INTO sales_tb VALUES(2021-11-2, 4, B001, 1, 120);
INSERT INTO sales_tb VALUES(2021-11-3, 5, C001, 1, 240);
INSERT INTO sales_tb VALUES(2021-11-3, 6, C002, 1, 270);
INSERT INTO sales_tb VALUES(2021-11-4, 7, A003, 1, 180);
INSERT INTO sales_tb VALUES(2021-11-4, 8, B002, 1, 140);
INSERT INTO sales_tb VALUES(2021-11-4, 9, B001, 1, 125);
INSERT INTO sales_tb VALUES(2021-11-5, 10, B003, 1, 120);
INSERT INTO sales_tb VALUES(2021-11-5, 10, B004, 1, 150);
INSERT INTO sales_tb VALUES(2021-11-5, 10, A003, 1, 180);
INSERT INTO sales_tb VALUES(2021-11-6, 11, B003, 1, 120);
INSERT INTO sales_tb VALUES(2021-11-6, 10, B004, 1, 150);5.某宝店铺连续2天及以上购物的用户及其对应的天数
题目11月结束后小牛同学需要对其在某宝的网店就11月份用户交易情况和产品情况进行分析以更好的经营小店。请你统计连续2天及以上在该店铺购物的用户及其对应的次数若有多个用户按user_id升序排序。
--输入
drop table if exists sales_tb;
CREATE TABLE sales_tb(
sales_date date NOT NULL,
user_id int(10) NOT NULL,
item_id char(10) NOT NULL,
sales_num int(10) NOT NULL,
sales_price int(10) NOT NULL
);INSERT INTO sales_tb VALUES(2021-11-1, 1, A001, 1, 90);
INSERT INTO sales_tb VALUES(2021-11-1, 2, A002, 2, 220);
INSERT INTO sales_tb VALUES(2021-11-1, 2, B001, 1, 120);
INSERT INTO sales_tb VALUES(2021-11-2, 3, C001, 2, 500);
INSERT INTO sales_tb VALUES(2021-11-2, 4, B001, 1, 120);
INSERT INTO sales_tb VALUES(2021-11-3, 5, C001, 1, 240);
INSERT INTO sales_tb VALUES(2021-11-3, 6, C002, 1, 270);
INSERT INTO sales_tb VALUES(2021-11-4, 7, A003, 1, 180);
INSERT INTO sales_tb VALUES(2021-11-4, 8, B002, 1, 140);
INSERT INTO sales_tb VALUES(2021-11-4, 9, B001, 1, 125);
INSERT INTO sales_tb VALUES(2021-11-5, 10, B003, 1, 120);
INSERT INTO sales_tb VALUES(2021-11-5, 10, B004, 1, 150);
INSERT INTO sales_tb VALUES(2021-11-5, 10, A003, 1, 180);
INSERT INTO sales_tb VALUES(2021-11-6, 11, B003, 1, 120);
INSERT INTO sales_tb VALUES(2021-11-6, 10, B004, 1, 150);六、牛客直播课分析在线教育行业
1.牛客直播转换率
题目牛客某页面推出了数据分析系列直播课程介绍。用户可以选择报名任意一场或多场直播课。请你统计每个科目的转换率sign_rate(%)转化率报名人数/浏览人数结果保留两位小数。
--输入
drop table if exists course_tb;
CREATE TABLE course_tb(
course_id int(10) NOT NULL,
course_name char(10) NOT NULL,
course_datetime char(30) NOT NULL);INSERT INTO course_tb VALUES(1, Python, 2021-12-1 19:00-21:00);
INSERT INTO course_tb VALUES(2, SQL, 2021-12-2 19:00-21:00);
INSERT INTO course_tb VALUES(3, R, 2021-12-3 19:00-21:00);drop table if exists behavior_tb;
CREATE TABLE behavior_tb(
user_id int(10) NOT NULL,
if_vw int(10) NOT NULL,
if_fav int(10) NOT NULL,
if_sign int(10) NOT NULL,
course_id int(10) NOT NULL);INSERT INTO behavior_tb VALUES(100, 1, 1, 1, 1);
INSERT INTO behavior_tb VALUES(100, 1, 1, 1, 2);
INSERT INTO behavior_tb VALUES(100, 1, 1, 1, 3);
INSERT INTO behavior_tb VALUES(101, 1, 1, 1, 1);
INSERT INTO behavior_tb VALUES(101, 1, 1, 1, 2);
INSERT INTO behavior_tb VALUES(101, 1, 0, 0, 3);
INSERT INTO behavior_tb VALUES(102, 1, 1, 1, 1);
INSERT INTO behavior_tb VALUES(102, 1, 1, 1, 2);
INSERT INTO behavior_tb VALUES(102, 1, 1, 1, 3);
INSERT INTO behavior_tb VALUES(103, 1, 1, 0, 1);
INSERT INTO behavior_tb VALUES(103, 1, 0, 0, 2);
INSERT INTO behavior_tb VALUES(103, 1, 0, 0, 3);
INSERT INTO behavior_tb VALUES(104, 1, 1, 1, 1);
INSERT INTO behavior_tb VALUES(104, 1, 1, 1, 2);
INSERT INTO behavior_tb VALUES(104, 1, 1, 0, 3);
INSERT INTO behavior_tb VALUES(105, 1, 0, 0, 1);
INSERT INTO behavior_tb VALUES(106, 1, 0, 0, 1);
INSERT INTO behavior_tb VALUES(107, 1, 0, 0, 1);
INSERT INTO behavior_tb VALUES(107, 1, 1, 1, 2);
INSERT INTO behavior_tb VALUES(108, 1, 1, 1, 3);2.牛客直播开始时各直播间在线人数
题目牛客某页面推出了数据分析系列直播课程介绍。用户可以选择报名任意一场或多场直播课。请你统计直播开始时1900各科目的在线人数以上例子的输出结果为按照course_id升序排序。
--输入
CREATE TABLE course_tb(
course_id int(10) NOT NULL,
course_name char(10) NOT NULL,
course_datetime char(30) NOT NULL);
INSERT INTO course_tb VALUES(1, Python, 2021-12-1 19:00-21:00);
INSERT INTO course_tb VALUES(2, SQL, 2021-12-2 19:00-21:00);
INSERT INTO course_tb VALUES(3, R, 2021-12-3 19:00-21:00);CREATE TABLE attend_tb(
user_id int(10) NOT NULL,
course_id int(10) NOT NULL,
in_datetime datetime NOT NULL,
out_datetime datetime NOT NULL
);
INSERT INTO attend_tb VALUES(100, 1, 2021-12-1 19:00:00, 2021-12-1 19:28:00);
INSERT INTO attend_tb VALUES(100, 1, 2021-12-1 19:30:00, 2021-12-1 19:53:00);
INSERT INTO attend_tb VALUES(101, 1, 2021-12-1 19:00:00, 2021-12-1 20:55:00);
INSERT INTO attend_tb VALUES(102, 1, 2021-12-1 19:00:00, 2021-12-1 19:05:00);
INSERT INTO attend_tb VALUES(104, 1, 2021-12-1 19:00:00, 2021-12-1 20:59:00);
INSERT INTO attend_tb VALUES(101, 2, 2021-12-2 19:05:00, 2021-12-2 20:58:00);
INSERT INTO attend_tb VALUES(102, 2, 2021-12-2 18:55:00, 2021-12-2 21:00:00);
INSERT INTO attend_tb VALUES(104, 2, 2021-12-2 18:57:00, 2021-12-2 20:56:00);
INSERT INTO attend_tb VALUES(107, 2, 2021-12-2 19:10:00, 2021-12-2 19:18:00);
INSERT INTO attend_tb VALUES(100, 3, 2021-12-3 19:01:00, 2021-12-3 21:00:00);
INSERT INTO attend_tb VALUES(102, 3, 2021-12-3 18:58:00, 2021-12-3 19:05:00);
INSERT INTO attend_tb VALUES(108, 3, 2021-12-3 19:01:00, 2021-12-3 19:56:00);3.牛客直播各科目平均观看时长
题目牛客某页面推出了数据分析系列直播课程介绍。用户可以选择报名任意一场或多场直播课。请你统计每个科目的平均观看时长观看时长定义为离开直播间的时间与进入直播间的时间之差单位是分钟输出结果按平均观看时长降序排序结果保留两位小数。
--输入
drop table if exists course_tb;
CREATE TABLE course_tb(
course_id int(10) NOT NULL,
course_name char(10) NOT NULL,
course_datetime char(30) NOT NULL);INSERT INTO course_tb VALUES(1, Python, 2021-12-1 19:00-21:00);
INSERT INTO course_tb VALUES(2, SQL, 2021-12-2 19:00-21:00);
INSERT INTO course_tb VALUES(3, R, 2021-12-3 19:00-21:00);drop table if exists attend_tb;
CREATE TABLE attend_tb(
user_id int(10) NOT NULL,
course_id int(10) NOT NULL,
in_datetime datetime NOT NULL,
out_datetime datetime NOT NULL
);
INSERT INTO attend_tb VALUES(100, 1, 2021-12-1 19:00:00, 2021-12-1 19:28:00);
INSERT INTO attend_tb VALUES(100, 1, 2021-12-1 19:30:00, 2021-12-1 19:53:00);
INSERT INTO attend_tb VALUES(101, 1, 2021-12-1 19:00:00, 2021-12-1 20:55:00);
INSERT INTO attend_tb VALUES(102, 1, 2021-12-1 19:00:00, 2021-12-1 19:05:00);
INSERT INTO attend_tb VALUES(104, 1, 2021-12-1 19:00:00, 2021-12-1 20:59:00);
INSERT INTO attend_tb VALUES(101, 2, 2021-12-2 19:05:00, 2021-12-2 20:58:00);
INSERT INTO attend_tb VALUES(102, 2, 2021-12-2 18:55:00, 2021-12-2 21:00:00);
INSERT INTO attend_tb VALUES(104, 2, 2021-12-2 18:57:00, 2021-12-2 20:56:00);
INSERT INTO attend_tb VALUES(107, 2, 2021-12-2 19:10:00, 2021-12-2 19:18:00);
INSERT INTO attend_tb VALUES(100, 3, 2021-12-3 19:01:00, 2021-12-3 21:00:00);
INSERT INTO attend_tb VALUES(102, 3, 2021-12-3 18:58:00, 2021-12-3 19:05:00);
INSERT INTO attend_tb VALUES(108, 3, 2021-12-3 19:01:00, 2021-12-3 19:56:00);4.牛客直播各科目出勤率
题目牛客某页面推出了数据分析系列直播课程介绍。用户可以选择报名任意一场或多场直播课。请你统计每个科目的出勤率attend_rate(%)结果保留两位小数出勤率出勤在线时长10分钟及以上人数 / 报名人数输出结果按course_id升序排序以上数据的输出结果如下。
--输入
drop table if exists course_tb;
CREATE TABLE course_tb(
course_id int(10) NOT NULL,
course_name char(10) NOT NULL,
course_datetime char(30) NOT NULL);INSERT INTO course_tb VALUES(1, Python, 2021-12-1 19:00-21:00);
INSERT INTO course_tb VALUES(2, SQL, 2021-12-2 19:00-21:00);
INSERT INTO course_tb VALUES(3, R, 2021-12-3 19:00-21:00);drop table if exists behavior_tb;
CREATE TABLE behavior_tb(
user_id int(10) NOT NULL,
if_vw int(10) NOT NULL,
if_fav int(10) NOT NULL,
if_sign int(10) NOT NULL,
course_id int(10) NOT NULL);INSERT INTO behavior_tb VALUES(100, 1, 1, 1, 1);
INSERT INTO behavior_tb VALUES(100, 1, 1, 1, 2);
INSERT INTO behavior_tb VALUES(100, 1, 1, 1, 3);
INSERT INTO behavior_tb VALUES(101, 1, 1, 1, 1);
INSERT INTO behavior_tb VALUES(101, 1, 1, 1, 2);
INSERT INTO behavior_tb VALUES(101, 1, 0, 0, 3);
INSERT INTO behavior_tb VALUES(102, 1, 1, 1, 1);
INSERT INTO behavior_tb VALUES(102, 1, 1, 1, 2);
INSERT INTO behavior_tb VALUES(102, 1, 1, 1, 3);
INSERT INTO behavior_tb VALUES(103, 1, 1, 0, 1);
INSERT INTO behavior_tb VALUES(103, 1, 0, 0, 2);
INSERT INTO behavior_tb VALUES(103, 1, 0, 0, 3);
INSERT INTO behavior_tb VALUES(104, 1, 1, 1, 1);
INSERT INTO behavior_tb VALUES(104, 1, 1, 1, 2);
INSERT INTO behavior_tb VALUES(104, 1, 1, 0, 3);
INSERT INTO behavior_tb VALUES(105, 1, 0, 0, 1);
INSERT INTO behavior_tb VALUES(106, 1, 0, 0, 1);
INSERT INTO behavior_tb VALUES(107, 1, 0, 0, 1);
INSERT INTO behavior_tb VALUES(107, 1, 1, 1, 2);
INSERT INTO behavior_tb VALUES(108, 1, 1, 1, 3);drop table if exists attend_tb;
CREATE TABLE attend_tb(
user_id int(10) NOT NULL,
course_id int(10) NOT NULL,
in_datetime datetime NOT NULL,
out_datetime datetime NOT NULL
);
INSERT INTO attend_tb VALUES(100, 1, 2021-12-1 19:00:00, 2021-12-1 19:28:00);
INSERT INTO attend_tb VALUES(100, 1, 2021-12-1 19:30:00, 2021-12-1 19:53:00);
INSERT INTO attend_tb VALUES(101, 1, 2021-12-1 19:00:00, 2021-12-1 20:55:00);
INSERT INTO attend_tb VALUES(102, 1, 2021-12-1 19:00:00, 2021-12-1 19:05:00);
INSERT INTO attend_tb VALUES(104, 1, 2021-12-1 19:00:00, 2021-12-1 20:59:00);
INSERT INTO attend_tb VALUES(101, 2, 2021-12-2 19:05:00, 2021-12-2 20:58:00);
INSERT INTO attend_tb VALUES(102, 2, 2021-12-2 18:55:00, 2021-12-2 21:00:00);
INSERT INTO attend_tb VALUES(104, 2, 2021-12-2 18:57:00, 2021-12-2 20:56:00);
INSERT INTO attend_tb VALUES(107, 2, 2021-12-2 19:10:00, 2021-12-2 19:18:00);
INSERT INTO attend_tb VALUES(100, 3, 2021-12-3 19:01:00, 2021-12-3 21:00:00);
INSERT INTO attend_tb VALUES(102, 3, 2021-12-3 18:58:00, 2021-12-3 19:05:00);
INSERT INTO attend_tb VALUES(108, 3, 2021-12-3 19:01:00, 2021-12-3 19:56:00);5.牛客直播各科目同时在线人数
题目牛客某页面推出了数据分析系列直播课程介绍。用户可以选择报名任意一场或多场直播课。请你统计每个科目最大同时在线人数按course_id排序。
--输入
drop table if exists course_tb;
CREATE TABLE course_tb(
course_id int(10) NOT NULL,
course_name char(10) NOT NULL,
course_datetime char(30) NOT NULL);
INSERT INTO course_tb VALUES(1, Python, 2021-12-1 19:00-21:00);
INSERT INTO course_tb VALUES(2, SQL, 2021-12-2 19:00-21:00);
INSERT INTO course_tb VALUES(3, R, 2021-12-3 19:00-21:00);drop table if exists attend_tb;
CREATE TABLE attend_tb(
user_id int(10) NOT NULL,
course_id int(10) NOT NULL,
in_datetime datetime NOT NULL,
out_datetime datetime NOT NULL
);
INSERT INTO attend_tb VALUES(100, 1, 2021-12-1 19:00:00, 2021-12-1 19:28:00);
INSERT INTO attend_tb VALUES(100, 1, 2021-12-1 19:30:00, 2021-12-1 19:53:00);
INSERT INTO attend_tb VALUES(101, 1, 2021-12-1 19:00:00, 2021-12-1 20:55:00);
INSERT INTO attend_tb VALUES(102, 1, 2021-12-1 19:00:00, 2021-12-1 19:05:00);
INSERT INTO attend_tb VALUES(104, 1, 2021-12-1 19:00:00, 2021-12-1 20:59:00);
INSERT INTO attend_tb VALUES(101, 2, 2021-12-2 19:05:00, 2021-12-2 20:58:00);
INSERT INTO attend_tb VALUES(102, 2, 2021-12-2 18:55:00, 2021-12-2 21:00:00);
INSERT INTO attend_tb VALUES(104, 2, 2021-12-2 18:57:00, 2021-12-2 20:56:00);
INSERT INTO attend_tb VALUES(107, 2, 2021-12-2 19:10:00, 2021-12-2 19:18:00);
INSERT INTO attend_tb VALUES(100, 3, 2021-12-3 19:01:00, 2021-12-3 21:00:00);
INSERT INTO attend_tb VALUES(102, 3, 2021-12-3 18:58:00, 2021-12-3 19:05:00);
INSERT INTO attend_tb VALUES(108, 3, 2021-12-3 19:01:00, 2021-12-3 19:56:00);七、某乎问答内容行业
1.某乎问答11月份日人均回答量
题目请你统计11月份日人均回答量回答问题数量/答题人数按回答日期排序结果保留两位小数
--输入
drop table if exists answer_tb;
CREATE TABLE answer_tb(
answer_date date NOT NULL,
author_id int(10) NOT NULL,
issue_id char(10) NOT NULL,
char_len int(10) NOT NULL);
INSERT INTO answer_tb VALUES(2021-11-1, 101, E001 ,150);
INSERT INTO answer_tb VALUES(2021-11-1, 101, E002, 200);
INSERT INTO answer_tb VALUES(2021-11-1,102, C003 ,50);
INSERT INTO answer_tb VALUES(2021-11-1 ,103, P001, 35);
INSERT INTO answer_tb VALUES(2021-11-1, 104, C003, 120);
INSERT INTO answer_tb VALUES(2021-11-1 ,105, P001, 125);
INSERT INTO answer_tb VALUES(2021-11-1 , 102, P002, 105);
INSERT INTO answer_tb VALUES(2021-11-2, 101, P001 ,201);
INSERT INTO answer_tb VALUES(2021-11-2, 110, C002, 200);
INSERT INTO answer_tb VALUES(2021-11-2, 110, C001, 225);
INSERT INTO answer_tb VALUES(2021-11-2 , 110, C002, 220);
INSERT INTO answer_tb VALUES(2021-11-3, 101, C002, 180);
INSERT INTO answer_tb VALUES(2021-11-4 ,109, E003, 130);
INSERT INTO answer_tb VALUES(2021-11-4, 109, E001,123);
INSERT INTO answer_tb VALUES(2021-11-5, 108, C001,160);
INSERT INTO answer_tb VALUES(2021-11-5, 108, C002, 120);
INSERT INTO answer_tb VALUES(2021-11-5, 110, P001, 180);
INSERT INTO answer_tb VALUES(2021-11-5 , 106, P002 , 45);
INSERT INTO answer_tb VALUES(2021-11-5 , 107, E003, 56);2.某乎问答高质量的回答中用户属于各级别的数量
题目回答字数大于等于100字的认为是高质量回答请你统计某乎问答高质量的回答中用户属于1-2级、3-4级、5-6级的数量分别是多少按数量降序排列。
--输入
drop table if exists author_tb;
CREATE TABLE author_tb(
author_id int(10) NOT NULL,
author_level int(10) NOT NULL,
sex char(10) NOT NULL);
INSERT INTO author_tb VALUES(101 , 6, m);
INSERT INTO author_tb VALUES(102 , 1, f);
INSERT INTO author_tb VALUES(103 , 1, m);
INSERT INTO author_tb VALUES(104 , 3, m);
INSERT INTO author_tb VALUES(105 , 4, f);
INSERT INTO author_tb VALUES(106 , 2, f);
INSERT INTO author_tb VALUES(107 , 2, m);
INSERT INTO author_tb VALUES(108 , 5, f);
INSERT INTO author_tb VALUES(109 , 6, f);
INSERT INTO author_tb VALUES(110 , 5, m);drop table if exists answer_tb;
CREATE TABLE answer_tb(
answer_date date NOT NULL,
author_id int(10) NOT NULL,
issue_id char(10) NOT NULL,
char_len int(10) NOT NULL);
INSERT INTO answer_tb VALUES(2021-11-1, 101, E001 ,150);
INSERT INTO answer_tb VALUES(2021-11-1, 101, E002, 200);
INSERT INTO answer_tb VALUES(2021-11-1,102, C003 ,50);
INSERT INTO answer_tb VALUES(2021-11-1 ,103, P001, 35);
INSERT INTO answer_tb VALUES(2021-11-1, 104, C003, 120);
INSERT INTO answer_tb VALUES(2021-11-1 ,105, P001, 125);
INSERT INTO answer_tb VALUES(2021-11-1 , 102, P002, 105);
INSERT INTO answer_tb VALUES(2021-11-2, 101, P001 ,201);
INSERT INTO answer_tb VALUES(2021-11-2, 110, C002, 200);
INSERT INTO answer_tb VALUES(2021-11-2, 110, C001, 225);
INSERT INTO answer_tb VALUES(2021-11-2 , 110, C002, 220);
INSERT INTO answer_tb VALUES(2021-11-3, 101, C002, 180);
INSERT INTO answer_tb VALUES(2021-11-4 ,109, E003, 130);
INSERT INTO answer_tb VALUES(2021-11-4, 109, E001,123);
INSERT INTO answer_tb VALUES(2021-11-5, 108, C001,160);
INSERT INTO answer_tb VALUES(2021-11-5, 108, C002, 120);
INSERT INTO answer_tb VALUES(2021-11-5, 110, P001, 180);
INSERT INTO answer_tb VALUES(2021-11-5 , 106, P002 , 45);
INSERT INTO answer_tb VALUES(2021-11-5 , 107, E003, 56);3.某乎问答单日回答问题数大于等于3个的所有用户
题目请你统计11月份单日回答问题数大于等于3个的所有用户信息author_date表示回答日期、author_id表示创作者idanswer_cnt表示回答问题个数。
--输入
drop table if exists answer_tb;
CREATE TABLE answer_tb(
answer_date date NOT NULL,
author_id int(10) NOT NULL,
issue_id char(10) NOT NULL,
char_len int(10) NOT NULL);
INSERT INTO answer_tb VALUES(2021-11-1, 101, E001 ,150);
INSERT INTO answer_tb VALUES(2021-11-1, 101, E002, 200);
INSERT INTO answer_tb VALUES(2021-11-1,102, C003 ,50);
INSERT INTO answer_tb VALUES(2021-11-1 ,103, P001, 35);
INSERT INTO answer_tb VALUES(2021-11-1, 104, C003, 120);
INSERT INTO answer_tb VALUES(2021-11-1 ,105, P001, 125);
INSERT INTO answer_tb VALUES(2021-11-1 , 102, P002, 105);
INSERT INTO answer_tb VALUES(2021-11-2, 101, P001 ,201);
INSERT INTO answer_tb VALUES(2021-11-2, 110, C002, 200);
INSERT INTO answer_tb VALUES(2021-11-2, 110, C001, 225);
INSERT INTO answer_tb VALUES(2021-11-2 , 110, C002, 220);
INSERT INTO answer_tb VALUES(2021-11-3, 101, C002, 180);
INSERT INTO answer_tb VALUES(2021-11-4 ,109, E003, 130);
INSERT INTO answer_tb VALUES(2021-11-4, 109, E001,123);
INSERT INTO answer_tb VALUES(2021-11-5, 108, C001,160);
INSERT INTO answer_tb VALUES(2021-11-5, 108, C002, 120);
INSERT INTO answer_tb VALUES(2021-11-5, 110, P001, 180);
INSERT INTO answer_tb VALUES(2021-11-5 , 106, P002 , 45);
INSERT INTO answer_tb VALUES(2021-11-5 , 107, E003, 56);4.某乎问答回答过教育类问题的用户里有多少用户回答
题目请你统计回答过教育类问题的用户里有多少用户回答过职场类问题。
--输入
drop table if exists issue_tb;
CREATE TABLE issue_tb(
issue_id char(10) NOT NULL,
issue_type char(10) NOT NULL);
INSERT INTO issue_tb VALUES(E001 ,Education);
INSERT INTO issue_tb VALUES(E002 ,Education);
INSERT INTO issue_tb VALUES(E003 ,Education);
INSERT INTO issue_tb VALUES(C001, Career);
INSERT INTO issue_tb VALUES(C002, Career);
INSERT INTO issue_tb VALUES(C003, Career);
INSERT INTO issue_tb VALUES(C004, Career);
INSERT INTO issue_tb VALUES(P001 ,Psychology);
INSERT INTO issue_tb VALUES(P002 ,Psychology);drop table if exists answer_tb;
CREATE TABLE answer_tb(
answer_date date NOT NULL,
author_id int(10) NOT NULL,
issue_id char(10) NOT NULL,
char_len int(10) NOT NULL);
INSERT INTO answer_tb VALUES(2021-11-1, 101, E001 ,150);
INSERT INTO answer_tb VALUES(2021-11-1, 101, E002, 200);
INSERT INTO answer_tb VALUES(2021-11-1,102, C003 ,50);
INSERT INTO answer_tb VALUES(2021-11-1 ,103, P001, 35);
INSERT INTO answer_tb VALUES(2021-11-1, 104, C003, 120);
INSERT INTO answer_tb VALUES(2021-11-1 ,105, P001, 125);
INSERT INTO answer_tb VALUES(2021-11-1 , 102, P002, 105);
INSERT INTO answer_tb VALUES(2021-11-2, 101, P001 ,201);
INSERT INTO answer_tb VALUES(2021-11-2, 110, C002, 200);
INSERT INTO answer_tb VALUES(2021-11-2, 110, C001, 225);
INSERT INTO answer_tb VALUES(2021-11-2 , 110, C002, 220);
INSERT INTO answer_tb VALUES(2021-11-3, 101, C002, 180);
INSERT INTO answer_tb VALUES(2021-11-4 ,109, E003, 130);
INSERT INTO answer_tb VALUES(2021-11-4, 109, E001,123);
INSERT INTO answer_tb VALUES(2021-11-5, 108, C001,160);
INSERT INTO answer_tb VALUES(2021-11-5, 108, C002, 120);
INSERT INTO answer_tb VALUES(2021-11-5, 110, P001, 180);
INSERT INTO answer_tb VALUES(2021-11-5 , 106, P002 , 45);
INSERT INTO answer_tb VALUES(2021-11-5 , 107, E003, 56);5.某乎问答最大连续回答问题天数大于等于3天的用户
题目请你统计最大连续回答问题的天数大于等于3天的用户及其等级若有多条符合条件的数据按author_id升序排序。
--输入
drop table if exists author_tb;
CREATE TABLE author_tb(
author_id int(10) NOT NULL,
author_level int(10) NOT NULL,
sex char(10) NOT NULL);
INSERT INTO author_tb VALUES(101 , 6, m);
INSERT INTO author_tb VALUES(102 , 1, f);
INSERT INTO author_tb VALUES(103 , 1, m);
INSERT INTO author_tb VALUES(104 , 3, m);
INSERT INTO author_tb VALUES(105 , 4, f);
INSERT INTO author_tb VALUES(106 , 2, f);
INSERT INTO author_tb VALUES(107 , 2, m);
INSERT INTO author_tb VALUES(108 , 5, f);
INSERT INTO author_tb VALUES(109 , 6, f);
INSERT INTO author_tb VALUES(110 , 5, m);drop table if exists answer_tb;
CREATE TABLE answer_tb(
answer_date date NOT NULL,
author_id int(10) NOT NULL,
issue_id char(10) NOT NULL,
char_len int(10) NOT NULL);
INSERT INTO answer_tb VALUES(2021-11-1, 101, E001 ,150);
INSERT INTO answer_tb VALUES(2021-11-1, 101, E002, 200);
INSERT INTO answer_tb VALUES(2021-11-1,102, C003 ,50);
INSERT INTO answer_tb VALUES(2021-11-1 ,103, P001, 35);
INSERT INTO answer_tb VALUES(2021-11-1, 104, C003, 120);
INSERT INTO answer_tb VALUES(2021-11-1 ,105, P001, 125);
INSERT INTO answer_tb VALUES(2021-11-1 , 102, P002, 105);
INSERT INTO answer_tb VALUES(2021-11-2, 101, P001 ,201);
INSERT INTO answer_tb VALUES(2021-11-2, 110, C002, 200);
INSERT INTO answer_tb VALUES(2021-11-2, 110, C001, 225);
INSERT INTO answer_tb VALUES(2021-11-2 , 110, C002, 220);
INSERT INTO answer_tb VALUES(2021-11-3, 101, C002, 180);
INSERT INTO answer_tb VALUES(2021-11-4 ,109, E003, 130);
INSERT INTO answer_tb VALUES(2021-11-4, 109, E001,123);
INSERT INTO answer_tb VALUES(2021-11-5, 108, C001,160);
INSERT INTO answer_tb VALUES(2021-11-5, 108, C002, 120);
INSERT INTO answer_tb VALUES(2021-11-5, 110, P001, 180);
INSERT INTO answer_tb VALUES(2021-11-5 , 106, P002 , 45);
INSERT INTO answer_tb VALUES(2021-11-5 , 107, E003, 56);
