1.回答字数大于等于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);
-- 回答字数大于等于100字的认为是高质量回答,请你统计某乎问答高质量的回答中用户属于1-2级、3-4级、5-6级的数量分别是多少,按数量降序排列
select case
when author_level in (1,2) then '1-2级'
when author_level in (3,4) then '3-4级'
when author_level in (5,6) then '5-6级'
else '' end as level_cut,
count(issue_id) num
from answer_tb a
join author_tb t on a.author_id = t.author_id
where a.char_len >= 100
group by level_cut
order by num desc
;
2.请你统计11月份单日回答问题数大于等于3个的所有用户信息(author_date表示回答日期、author_id表示创作者id,answer_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);
-- 请你统计11月份单日回答问题数大于等于3个的所有用户信息
-- (author_date表示回答日期、author_id表示创作者id,answer_cnt表示回答问题个数
select answer_date,author_id,count(issue_id) as answer_cnt
from answer_tb
where month(answer_date) = '11'
group by answer_date,author_id
having count(issue_id) >= 3
order by answer_date,author_id
;
3.请你统计回答过教育类问题的用户里有多少用户回答过职场类问题
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);
-- 请你统计回答过教育类问题的用户里有多少用户回答过职场类问题
select issue_id from issue_tb where issue_type = 'Education';
select issue_id from issue_tb where issue_type = 'Career';
with t1 as ( select distinct author_id
from answer_tb
where issue_id in (select issue_id from issue_tb where issue_type = 'Education') ),
t2 as ( select distinct author_id
from answer_tb
where issue_id in (select issue_id from issue_tb where issue_type = 'Career') )
select count(1) as num
from t1
join t2 on t1.author_id = t2.author_id
;
4.请你统计最大连续回答问题的天数大于等于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);
-- 请你统计最大连续回答问题的天数大于等于3天的用户及其等级(若有多条符合条件的数据,按author_id升序排序)
with t1 as (select author_id, count(distinct answer_date) as days_cnt
from answer_tb
where author_id in (select distinct a.author_id
from answer_tb a
join answer_tb t
on a.answer_date = t.answer_date + 1 and a.author_id = t.author_id
where t.author_id is not null
)
group by author_id)
select ar.author_id,author_level,days_cnt
from t1
join author_tb ar on t1.author_id = ar.author_id
group by ar.author_id, author_level
;
5.请你统计每个科目的转换率(sign_rate(%),转化率=报名人数/浏览人数,结果保留两位小数)。 -- 注:按照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);
-- 请你统计每个科目的转换率(sign_rate(%),转化率=报名人数/浏览人数,结果保留两位小数)。
-- 注:按照course_id升序排序。
select t1.course_id, course_name,
(round((count(case when if_sign = 1 then 1 else null end) /
count(case when if_vw = 1 then 1 else null end))*100, 2)) as "sign_rate(%)"
from behavior_tb t1
join course_tb ct on t1.course_id = ct.course_id
group by ct.course_id,ct.course_name
order by ct.course_id
;