SQL(面试实战06)

最新推荐文章于 2023-07-25 16:56:48 发布
最新推荐文章于 2023-07-25 16:56:48 发布
阅读量687 收藏 2
点赞数
分类专栏： mysql 文章标签： sql 数据库 database
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_44464850/article/details/125237012
版权

1. 某宝店铺动销率与售罄率

11月结束后，小牛同学需要对其在某宝的网店就11月份用户交易情况和产品情况进行分析以更好的经营小店。

已知产品情况表product_tb如下（其中，item_id指某款号的具体货号，style_id指款号，tag_price表示标签价格，inventory指库存量）：

item_idstyle_idtag_priceinventory
A001A10020
A002A12030
A003A20015
B001B13018
B002B15022
B003B12510
B004B15512
C001C26025
C002C28018

11月份销售数据表sales_tb如下（其中，sales_date表示销售日期，user_id指用户编号，item_id指货号，sales_num表示销售数量，sales_price表示结算金额）：

sales_dateuser_iditem_idsales_numsales_price
2021-11-011A001190
2021-11-012A0022220
2021-11-012B0011120
2021-11-023C0012500
2021-11-024B0011120
2021-11-035C0011240
2021-11-036C0021270
2021-11-047A0031180
2021-11-048B0021140
2021-11-049B0011125
2021-11-0510B0031120
2021-11-0510B0041150
2021-11-0510A0031180
2021-11-0611B0031120
2021-11-0610B0041150

请你统计每款的动销率（pin_rate，有销售的SKU数量/在售SKU数量）与售罄率（sell-through_rate，GMV/备货值，备货值=吊牌价*库存数），按style_id升序排序，以上例子的输出结果如下：

style_idpin_rate(%)sell-through_rate(%)
A8.337.79
B14.8111.94
C10.268.75
示例1
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);
输出

A|8.33|7.79
B|14.81|11.94
C|10.26|8.75

题解
select 
style_id,
round(sum(total_num)/(sum(inventory)-sum(total_num))*100,2)'pin_rate(%)',
round(sum(total_price)/sum(tag_price*inventory)*100,2)'sell-through_rate(%)'
from product_tb
join
(
select item_id,sum(sales_num)total_num,sum(sales_price)total_price
from sales_tb
group by item_id
)t_sales
using(item_id)
group by style_id

2. 某宝店铺连续2天及以上购物的用户及其对应的天数

11月结束后，小牛同学需要对其在某宝的网店就11月份用户交易情况和产品情况进行分析以更好的经营小店。

11月份销售数据表sales_tb如下（其中，sales_date表示销售日期，user_id指用户编号，item_id指货号，sales_num表示销售数量，sales_price表示结算金额）：

sales_dateuser_iditem_idsales_numsales_price
2021-11-011A001190
2021-11-012A0022220
2021-11-012B0011120
2021-11-023C0012500
2021-11-024B0011120
2021-11-035C0011240
2021-11-036C0021270
2021-11-047A0031180
2021-11-048B0021140
2021-11-049B0011125
2021-11-0510B0031120
2021-11-0510B0041150
2021-11-0510A0031180
2021-11-0611B0031120
2021-11-0610B0041150

请你统计连续2天及以上在该店铺购物的用户及其对应的次数（若有多个用户，按user_id升序排序），以上例子的输出结果如下：

user_iddays_count
102
示例1
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);
输出

10|2

题解
select user_id,max(rk) days_count
from 
(
select 
	user_id,
	sales_date,
	dense_rank()over(partition by user_id order by sales_date ) rk
from sales_tb
)t
where rk>=2
group by user_id

在线教育行业 (面试题)

3.牛客直播转换率

牛客某页面推出了数据分析系列直播课程介绍。用户可以选择报名任意一场或多场直播课。

已知课程表course_tb如下（其中course_id代表课程编号，course_name表示课程名称，course_datetime代表上课时间）：

course_idcourse_namecourse_datetime
1Python2021-12-1 19:00-21:00
2SQL2021-12-2 19:00-21:00
3R2021-12-3 19:00-21:00

用户行为表behavior_tb如下（其中user_id表示用户编号、if_vw表示是否浏览、if_fav表示是否收藏、if_sign表示是否报名、course_id代表课程编号）：

user_idif_vwif_favif_signcourse_id
1001111
1001112
1001113
1011111
1011112
1011003
1021111
1021112
1021113
1031101
1031002
1031003
1041111
1041112
1041103
1051001
1061001
1071001
1071112
1081113

请你统计每个科目的转换率（sign_rate(%)，转化率=报名人数/浏览人数，结果保留两位小数）。

注：按照course_id升序排序。

course_idcourse_namesign_rate(%)
1Python50.00
2SQL83.33
3R50.00
示例1
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);
输出

1|Python|50.00
2|SQL|83.33
3|R|50.00

题解
select course_id,course_name,round((sum_sign/sum_vw)*100,2) 'sign_rate(%)'
from course_tb
join 
(
    select sum(if_sign)sum_sign,sum(if_vw)sum_vw,course_id
    from behavior_tb
    group by course_id
)behavior_tb
using(course_id)
order by course_id

4.牛客直播开始时各直播间在线人数

牛客某页面推出了数据分析系列直播课程介绍。用户可以选择报名任意一场或多场直播课。

已知课程表course_tb如下（其中course_id代表课程编号，course_name表示课程名称，course_datetime代表上课时间）：

course_idcourse_namecourse_datetime
1Python2021-12-1 19:00-21:00
2SQL2021-12-2 19:00-21:00
3R2021-12-3 19:00-21:00

上课情况表attend_tb如下（其中user_id表示用户编号、course_id代表课程编号、in_datetime表示进入直播间的时间、out_datetime表示离开直播间的时间）：

user_idcourse_idin_datetimeout_datetime
10012021-12-01 19:00:002021-12-01 19:28:00
10012021-12-01 19:30:002021-12-01 19:53:00
10112021-12-01 19:00:002021-12-01 20:55:00
10212021-12-01 19:00:002021-12-01 19:05:00
10412021-12-01 19:00:002021-12-01 20:59:00
10122021-12-02 19:05:002021-12-02 20:58:00
10222021-12-02 18:55:002021-12-02 21:00:00
10422021-12-02 18:57:002021-12-02 20:56:00
10722021-12-02 19:10:002021-12-02 19:18:00
10032021-12-03 19:01:002021-12-03 21:00:00
10232021-12-03 18:58:002021-12-03 19:05:00
10832021-12-03 19:01:002021-12-03 19:56:00

请你统计直播开始时（19：00），各科目的在线人数，以上例子的输出结果为（按照course_id升序排序）：

course_idcourse_nameonline_num
1Python4
2SQL2
3R1
示例1
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');
输出

1|Python|4
2|SQL|2
3|R|1

题解
select course_id,course_name,online_num
from course_tb
join
(
    select course_id,count(course_id) online_num
    from attend_tb
    where date_format(in_datetime,"%H:%i") <="19:00"
    group by course_id
) attend_tb
using(course_id)

5. 牛客直播各科目平均观看时长

牛客某页面推出了数据分析系列直播课程介绍。用户可以选择报名任意一场或多场直播课。

已知课程表course_tb如下（其中course_id代表课程编号，course_name表示课程名称，course_datetime代表上课时间）：

course_idcourse_namecourse_datetime
1Python2021-12-1 19:00-21:00
2SQL2021-12-2 19:00-21:00
3R2021-12-3 19:00-21:00

上课情况表attend_tb如下（其中user_id表示用户编号、course_id代表课程编号、in_datetime表示进入直播间的时间、out_datetime表示离开直播间的时间）：

user_idcourse_idin_datetimeout_datetime
10012021-12-01 19:00:002021-12-01 19:28:00
10012021-12-01 19:30:002021-12-01 19:53:00
10112021-12-01 19:00:002021-12-01 20:55:00
10212021-12-01 19:00:002021-12-01 19:05:00
10412021-12-01 19:00:002021-12-01 20:59:00
10122021-12-02 19:05:002021-12-02 20:58:00
10222021-12-02 18:55:002021-12-02 21:00:00
10422021-12-02 18:57:002021-12-02 20:56:00
10722021-12-02 19:10:002021-12-02 19:18:00
10032021-12-03 19:01:002021-12-03 21:00:00
10232021-12-03 18:58:002021-12-03 19:05:00
10832021-12-03 19:01:002021-12-03 19:56:00

请你统计每个科目的平均观看时长（观看时长定义为离开直播间的时间与进入直播间的时间之差，单位是分钟），输出结果按平均观看时长降序排序，结果保留两位小数。

course_nameavg_Len
SQL91.25
R60.33
Python58.00
示例1
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');
题解
select course_name,round(avg(times),2) avg_Len
from course_tb
join
(
    select course_id,timestampdiff(minute,in_datetime,out_datetime) times
    from attend_tb
)attend_tb
using(course_id)
group by course_name
order by avg_Len desc

6.牛客直播各科目出勤率

牛客某页面推出了数据分析系列直播课程介绍。用户可以选择报名任意一场或多场直播课。

已知课程表course_tb如下（其中course_id代表课程编号，course_name表示课程名称，course_datetime代表上课时间）：

course_idcourse_namecourse_datetime
1Python2021-12-1 19:00-21:00
2SQL2021-12-2 19:00-21:00
3R2021-12-3 19:00-21:00

用户行为表behavior_tb如下（其中user_id表示用户编号、if_vw表示是否浏览、if_fav表示是否收藏、if_sign表示是否报名、course_id代表课程编号）：

user_idif_vwif_favif_signcourse_id
1001111
1001112
1001113
1011111
1011112
1011003
1021111
1021112
1021113
1031101
1031002
1031003
1041111
1041112
1041103
1051001
1061001
1071001
1071112
1081113

上课情况表attend_tb如下（其中user_id表示用户编号、course_id代表课程编号、in_datetime表示进入直播间的时间、out_datetime表示离开直播间的时间）：

user_idcourse_idin_datetimeout_datetime
10012021-12-01 19:00:002021-12-01 19:28:00
10012021-12-01 19:30:002021-12-01 19:53:00
10112021-12-01 19:00:002021-12-01 20:55:00
10212021-12-01 19:00:002021-12-01 19:05:00
10412021-12-01 19:00:002021-12-01 20:59:00
10122021-12-02 19:05:002021-12-02 20:58:00
10222021-12-02 18:55:002021-12-02 21:00:00
10422021-12-02 18:57:002021-12-02 20:56:00
10722021-12-02 19:10:002021-12-02 19:18:00
10032021-12-03 19:01:002021-12-03 21:00:00
10232021-12-03 18:58:002021-12-03 19:05:00
10832021-12-03 19:01:002021-12-03 19:56:00

请你统计每个科目的出勤率（attend_rate(%)，结果保留两位小数），出勤率=出勤（在线时长10分钟及以上）人数 / 报名人数，输出结果按course_id升序排序，以上数据的输出结果如下：

course_idcourse_nameattend_rate(%)
1Python75.00
2SQL60.00
3R66.67
示例1
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');
输出

1|Python|75.00
2|SQL|60.00
3|R|66.67

题解
select course_id,course_name,round((sum(total_sign)/sum_sign)*100,2) 'attend_rate(%)'
from course_tb
join
(
    select course_id,count(distinct user_id) as total_sign
    from attend_tb
    where timestampdiff(minute,in_datetime,out_datetime)>=10
    group by course_id,date(in_datetime)
)attend_tb
using(course_id) 
join 
(
	select course_id,sum(if_sign) sum_sign
	from behavior_tb
	group by course_id
)behavior_tb
using(course_id)
group by course_id,course_name
order by course_id

7.牛客直播各科目同时在线人数

牛客某页面推出了数据分析系列直播课程介绍。用户可以选择报名任意一场或多场直播课。

已知课程表course_tb如下（其中course_id代表课程编号，course_name表示课程名称，course_datetime代表上课时间）：

course_idcourse_namecourse_datetime
1Python2021-12-1 19:00-21:00
2SQL2021-12-2 19:00-21:00
3R2021-12-3 19:00-21:00

上课情况表attend_tb如下（其中user_id表示用户编号、course_id代表课程编号、in_datetime表示进入直播间的时间、out_datetime表示离开直播间的时间）：

user_idcourse_idin_datetimeout_datetime
10012021-12-01 19:00:002021-12-01 19:28:00
10012021-12-01 19:30:002021-12-01 19:53:00
10112021-12-01 19:00:002021-12-01 20:55:00
10212021-12-01 19:00:002021-12-01 19:05:00
10412021-12-01 19:00:002021-12-01 20:59:00
10122021-12-02 19:05:002021-12-02 20:58:00
10222021-12-02 18:55:002021-12-02 21:00:00
10422021-12-02 18:57:002021-12-02 20:56:00
10722021-12-02 19:10:002021-12-02 19:18:00
10032021-12-03 19:01:002021-12-03 21:00:00
10232021-12-03 18:58:002021-12-03 19:05:00
10832021-12-03 19:01:002021-12-03 19:56:00

请你统计每个科目最大同时在线人数（按course_id排序），以上数据的输出结果如下：

course_idcourse_namemax_num
1Python4
2SQL4
3R3
示例1
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|Python|4
2|SQL|4
3|R|3

题解
select course_id,course_name,max_num
from course_tb
join 
(
	select 
	course_id,max(num)max_num
	from 
	(
		select 
		course_id,sum(uv)
        over(partition by course_id,date(time) order by time,uv desc) num
		from 
		(
		select  user_id, course_id,in_datetime time,1 as uv
		from attend_tb
		union all 
		select  user_id, course_id,out_datetime time,-1 as uv
		from attend_tb
		)t_attend_all
	)t_attend_num
	group by course_id
)t
using(course_id)
order by course_id
林园857
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
牛客网SQL-PostgreSQL-SQL32 牛客直播各科目平均观看时长
weixin_42944283的博客
04-01 552
SQL32 牛客直播各科目平均观看时长 时间函数： TO_CHAR(‘2022-04-01 07:08:32’, ‘YYYY-MM-DD HH:mm:ss’) EXTRACT(HOUR from in_datetime::TIMESTAMP） drop table if exists course_tb; CREATE TABLE course_tb( course_id int(10) NOT NULL, course_name char(10) NOT NULL, course_datetime ch
HIVE SQL使用row_number()语法查询每种定义方式下，每种视频类型下对应的总播放时长
Foools的博客
05-08 501
背景 当前有两张表如下： id表示视频id，tag_type表示这种标签是谁定义的，content_tag表示的是视频类型。 vv表示视频播放量，id表示视频id。 需求1：查询每种视频类型下对应的总播放时长top3 SELECT A.content_tag AS content_tag ,SUM(A.vv) AS vv_sum FROM ( SELECT A.id AS id ,A.content_tag AS content_tag
SQL用户观看时长问题分析--HiveSQL面试题19
u011250186的博客
02-23 1067
0 题目 1 数据准备 2 数据分析 4 小结 0 题目 数据如下： date user_id age programid Playtime 20190421 u1 30 a 4 20190421 u1 30 b 10 20190421 u1 30 a 2 20190421 u2 27 c 1 20190422 u3 35 d 3 20190423...
Python实训day10pm【os模块-处理Excel统计学生观看直播时长】
upward
01-17 709
Python实训
hiveSql 各时段观看直播人数
i_mycode的博客
11-14 1187
各时段观看直播人数
牛客题目网上的数据库SQL实战题目，包含详细的解题思路、代码实现和优化建议，适合准备SQL面试和实际开发的学习者
最新发布
05-16
本资源包汇总了牛客题目网上精选的数据库SQL实战题目，旨在帮助学习者提升SQL技能，准备SQL相关面试，并在实际开发中应用这些技能。资源内容详尽，包括每道题目的详细解题思路、代码实现以及优化建议，非常适合SQL...
database-sql数据库基础实战
05-08
数据库SQL实战，把一些工作中遇到的以及在网上找到的面试题进行了sql语句自己编辑以及测试，提高自己对sql语句的理解，尽量少用第三方插件
微软SQL和MySQL数据库实战训练营
11-16
在“微软SQL和MySQL数据库实战训练营”中，学员们将深入学习两种广泛使用的数据库管理系统——微软的SQL Server和开源的MySQL。这两种数据库系统在现代IT环境中占据着至关重要的地位，尤其是在数据存储、管理和分析...
sql 面试题.rar
07-09
这些是SQL面试中常见的主题，深入理解和实践这些知识点，可以为面试和实际工作中的数据库管理提供坚实的基础。通过不断的练习和实战，可以提升SQL技能，成为一名优秀的数据库管理员或开发人员。
数据库 面试题 实战解答
01-21
目录：1、触发器的作用？2、什么是存储过程？用什么来调用？...14、SQL 约束有哪几种？15、数据库的乐观锁和悲观锁是什么？16、事务隔离级别？17、Truncate与delete区别？1，使用 DELETE 语句删除数据时，Or
数据分析（三）：SQL入门到实战
SusicRuan的博客
07-25 202
最近在对以往学到的数据分析知识进行回顾，，如有错误和不恰当的地方，欢迎大佬指正~ 学习过程中主要观看戴师兄的教程，讲得非常好，指路B站：链接: https://www.bilibili.com/video/BV1ZM4y1u7uF?p=1&vd_source=69f057a466718e60e53438e3b563d7f8 本节还补充了部分Mosh老师的SQL进阶内容，讲得非常详细，目前只学到数据分析所用到的SQL查询部分，后续数据开发部分有待学习，感兴趣的朋友可以去看看：SQL进阶教程 | 史上最易懂SQ
大厂SQL题2-多表关联、转化率、打标签
Sun123234的博客
10-04 1312
大厂SQL题2-多表关联、转化率、打标签
10、刷牛客网SQL题（四）
yunweijie_cn的博客
04-04 2343
SQL题题目来源一、SQL1（简单）1.题目内容2.思路分析3.语句实现二、SQL2（简单）1.题目内容2.思路分析3.语句实现 题目来源 例如：随着人工智能的不断发展，机器学习这门技术也越来越重要，很多人都开启了学习机器学习，本文就介绍了机器学习的基础内容。 牛客网SQL大厂真题——某宝店铺分析（电扇模式） 一、SQL1（简单） 1.题目内容 描述 11月结束后，小牛同学需要对其在某宝的网店就11月份用户交易情况和产品情况进行分析以更好的经营小店。 已知产品情况表product_tb如下（其中，ite
SQL技巧：使用AVG()函数计算占比
WHYbeHERE的博客
01-04 3311
在SQL中使用AVG()函数求出占比。
牛客SQL34： 牛客直播各科目同时在线人数（最大值问题）
weixin_46429290的博客
05-06 1721
牛客SQL34： 牛客直播各科目同时在线人数（最大值问题） 内容目录牛客SQL34： 牛客直播各科目同时在线人数（最大值问题）一、描述二、思路解析三、代码四、问题思考 一、描述 牛客某页面推出了数据分析系列直播课程介绍。用户可以选择报名任意一场或多场直播课。 已知课程表course_tb如下（其中course_id代表课程编号，course_name表示课程名称，course_datetime代表上课时间）： course_id course_name course_datetime 1 P
SQL(面试实战05)
weixin_44464850的博客
06-10 1264
1. 工作日各时段叫车量、等待接单时间和调度时间 2. 各城市最大同时等车人数 3. 某宝店铺的SPU数量 4.某宝店铺的实际销售额与客单价 5. 某宝店铺折扣率
牛客网SQL-PostgreSQL-SQL30 牛客直播转换率
weixin_42944283的博客
04-01 737
因为牛客网的SQL题目都是用MySQL，没有PostgreSQL编译环境，以及答案用的函数也不是PG可以运行的，于是有了这一系列的文章。 SQL30 牛客直播转换率 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_t
SQL 求某天各个直播间最大在线人数
Mr.pan felix的专栏
08-05 4030
如下需求： 一张表有如下字段： 1）直播间： live_id 2）用户:userid 3）时间戳:date_stamp 4）登陆类型:entry_type (登入和登出) 求直播间在某一天同一时间点的最大在线用户数？ 逻辑说明： 将直播间每登陆一个记为1，每登出一个记为-1，如此将会登陆和登出出现抵消，然后再根据时间进行排序求和。 WITH live_tab as ( select 1 live_id,'A' userid,to_date('20200801 10:00:00','yyyy
SQL进阶技巧：动态分桶算法应用之用户观看时长问题分析
石榴姐yyds
06-14 2933
0 题目 数据如下： date user_id age programid Playtime 20190421 u1 30 a 4 20190421 u1 31 b 10 20190421 u1 30 a 2 20190421 u2 27 c 1 20190422 u3 35 d 3 20190423 u2 27 a
Java面试实战：SQL查询实例解析
以下是一些实用的SQL面试题，涉及到从名为"employees"、"departments"、"locations"和"jobs"的四个表中进行复杂查询。 1. 查询每个月倒数第二天入职的员工信息 该问题要求找出每个月份入职日期为当月倒数第二天的...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值