首页 > 试题广场 >

统计复旦用户8月练题情况

[编程题]统计复旦用户8月练题情况

热度指数：253826 时间限制：C/C++ 1秒，其他语言2秒空间限制：C/C++ 256M，其他语言512M
算法知识视频讲解

题目：现在运营想要了解复旦大学的每个用户在8月份练习的总题目数和回答正确的题目数情况，请取出相应明细数据，对于在8月份没有练习过的用户，答题数结果返回0.

示例：用户信息表user_profile

id	device_id	gender	age	university	gpa	active_days_within_30
1	2138	male	21	北京大学	3.4	7
2	3214	male		复旦大学	4.0	15
3	6543	female	20	北京大学	3.2	12
4	2315	female	23	浙江大学	3.6	5
5	5432	male	25	山东大学	3.8	20
6	2131	male	28	山东大学	3.3	15
7	4321	female	26	复旦大学	3.6	9

示例：question_practice_detail

id	device_id	question_id	result	date
1	2138	111	wrong	2021-05-03
2	3214	112	wrong	2021-05-09
3	3214	113	wrong	2021-06-15
4	6543	111	right	2021-08-13
5	2315	115	right	2021-08-13
6	2315	116	right	2021-08-14
7	2315	117	wrong	2021-08-15
……

根据示例，你的查询应返回以下结果：

device_id	university	question_cnt	right_question_cnt
3214	复旦大学	3	0
4321	复旦大学	0	0

示例1

输入

drop table if exists `user_profile`;
drop table if  exists `question_practice_detail`;
drop table if  exists `question_detail`;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float,
`active_days_within_30` int ,
`question_cnt` int ,
`answer_cnt` int 
);
CREATE TABLE `question_practice_detail` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`question_id`int NOT NULL,
`result` varchar(32) NOT NULL,
`date` date NOT NULL
);
CREATE TABLE `question_detail` (
`id` int NOT NULL,
`question_id`int NOT NULL,
`difficult_level` varchar(32) NOT NULL
);

INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4,7,2,12);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0,15,5,25);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2,12,3,30);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6,5,1,2);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8,20,15,70);
INSERT INTO user_profile VALUES(6,2131,'male',28,'山东大学',3.3,15,7,13);
INSERT INTO user_profile VALUES(7,4321,'male',28,'复旦大学',3.6,9,6,52);
INSERT INTO question_practice_detail VALUES(1,2138,111,'wrong','2021-05-03');
INSERT INTO question_practice_detail VALUES(2,3214,112,'wrong','2021-05-09');
INSERT INTO question_practice_detail VALUES(3,3214,113,'wrong','2021-06-15');
INSERT INTO question_practice_detail VALUES(4,6543,111,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(5,2315,115,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(6,2315,116,'right','2021-08-14');
INSERT INTO question_practice_detail VALUES(7,2315,117,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(8,3214,112,'wrong','2021-05-09');
INSERT INTO question_practice_detail VALUES(9,3214,113,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(10,6543,111,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(11,2315,115,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(12,2315,116,'right','2021-08-14');
INSERT INTO question_practice_detail VALUES(13,2315,117,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(14,3214,112,'wrong','2021-08-16');
INSERT INTO question_practice_detail VALUES(15,3214,113,'wrong','2021-08-18');
INSERT INTO question_practice_detail VALUES(16,6543,111,'right','2021-08-13');
INSERT INTO question_detail VALUES(1,111,'hard');
INSERT INTO question_detail VALUES(2,112,'medium');
INSERT INTO question_detail VALUES(3,113,'easy');
INSERT INTO question_detail VALUES(4,115,'easy');
INSERT INTO question_detail VALUES(5,116,'medium');
INSERT INTO question_detail VALUES(6,117,'easy');

输出

3214|复旦大学|3|0
4321|复旦大学|0|0

算法知识视频讲解

牛客872544921号

1、用左连接把user和question连接起来，那么我们得到的结果首先就是所有的user的基本信息，如果答题了就会有答题的相关数据，如果没答题那么答题的相关数据为null。

2、COUNT(q.question_id)聚合函数是不计空值的，所以遇到空值默认为0了；利用SUM() 和case when... then去计算答对的题数。

3、筛选条件时要注意月份，只敲MONTH(date) = 8是不够的，这样会遗漏那些没有答题的同学，所以要加上MONTH(date) IS NULL。

4、最后GROUP BY　device_id求出所有复旦大学的同学的情况

SELECT
    u.device_id,
    u.university,
    COUNT(q.question_id) AS question_cnt,
    SUM(CASE WHEN result = 'right' THEN 1
        ELSE 0 END) AS right_question_cnt
FROM user_profile u
LEFT JOIN question_practice_detail q
USING(device_id)
WHERE university = '复旦大学' AND (MONTH(date) = 8&nbs***bsp;MONTH(date) IS NULL)
GROUP BY device_id;

发表于 2021-09-30 00:07:51 回复(48)

sqlserver小白

我是废物

发表于 2022-08-03 09:22:40 回复(14)

Baily1234

SELECT
    u.device_id,
    university,
    SUM(IF(result IS NOT NULL, 1, 0)) AS questino_cnt,
    SUM(IF(result = "right", 1, 0)) AS right_question_cnt
FROM
    user_profile u
    LEFT JOIN question_practice_detail q ON u.device_id = q.device_id
    AND MONTH(q.`date`) = "08"
WHERE 
    university = "复旦大学"
GROUP BY
    u.device_id;

发表于 2021-10-15 16:06:52 回复(21)

上山打老虎12345123

测试的时候是错的，提交的时候是对的。。。

发表于 2021-09-20 17:06:47 回复(17)

Mysql

恶作剧之神阿蒙

select up.device_id,university,
count(upd.device_id) question_cnt,
count(case when result='right' then 1 else null end) right_question_cnt
from user_profile as up left join question_practice_detail as upd  on upd.device_id=up.device_id
where university='复旦大学' and (month(date)='08'&nbs***bsp;month(date) is null)
group by up.device_id

我第一反应是表连接

连接之后跟着筛选就好了

发表于 2021-08-26 10:06:16 回复(15)

Mysql

Qiuxj

SELECT u.device_id, university,
sum(if(result is not null,1,0)) as question_cnt,
sum(if(result='right',1,0)) as right_question_cnt
FROM user_profile as u LEFT JOIN question_practice_detail as q
ON u.device_id=q.device_id
WHERE university='复旦大学' and (month(date)=8 or date is null)
GROUP BY u.device_id;

编辑于 2021-10-11 21:59:09 回复(12)

牛客747227703号

正确答案：

select

u.device_id
,u.university
,sum(case when q.result is null then 0 else 1 end) as question_cnt
,sum(case when q.result='right' then 1 else 0 end) as right_question_cnt
from user_profile as u
left join question_practice_detail as q
on u.device_id =q.device_id
where u.university = '复旦大学'
and (month(q.date) =8 or month(q.date) is null)
group by u.device_id

发表于 2021-08-26 16:18:46 回复(14)

Grayson12

SELECT qpd.device_id, up.university, COUNT(1) AS question_cnt, SUM(IF(result='right',1,0)) AS right_question_cnt
FROM user_profile up
LEFT JOIN question_practice_detail qpd
ON up.device_id = qpd.device_id
WHERE up.university = '复旦大学'
AND qpd.date LIKE '%-08-%'
GROUP BY device_id
UNION
SELECT device_id, university, 0 AS question_cnt, 0 AS right_question_cnt
FROM user_profile up
WHERE up.device_id NOT IN (SELECT DISTINCT device_id FROM question_practice_detail)

AND up.university = '复旦大学'

需要考虑没答题的情况所以使用UNION查出没答题的人

发表于 2021-11-06 18:18:57 回复(5)

Mysql

Phoenix__

select
    u.device_id,
    university,
    count(q.question_id) question_cnt,
    sum(if(q.result='right',1,0)) right_question_cnt
from
    user_profile u left join question_practice_detail q
    on u.device_id = q.device_id and month(`date`) = 8
where
    university = '复旦大学' 
group by
    u.device_id;

发表于 2022-06-03 21:16:17 回复(6)

Mysql

431959437

select u.device_id,university,count(result),count(nullif(result,'wrong'))

from (select * from question_practice_detail where month(date)=08) q right join

(select device_id,university from user_profile where university="复旦大学") u

on u.device_id=q.device_id group by device_id;

# 解题思路是题目表中筛选出8月份的答题记录，用户表 中筛选出复旦大学的记录，
# 然后用右连接筛选出既是复旦又是八月份的记录(原谅我先写了题目表的子查询，不然就左连接了咳咳)
# 利用count(列名)不统计null的数据的特性，以及nullif(expr1,expr2)两参数相等时返回null的特性
# 即可分别计算出 result的条数 以及 非wrong的条数

发表于 2022-01-12 03:29:56 回复(4)

游小哈哈哈哈哈哈

谁能有我写的蠢！哈哈

select 
q.device_id,
u.university,
count(q.device_id) question_cnt,
count(if(q.result='right',1,null)) right_question_cnt
from
user_profile u
left join question_practice_detail q
on u.device_id = q.device_id
where q.date regexp '2021-08' and u.university = '复旦大学'
group by q.device_id
union
select 
u.device_id,
u.university,
count(q.device_id) question_cnt,
count(if(q.result='right',1,null)) right_question_cnt
from
user_profile u
left join question_practice_detail q
on u.device_id = q.device_id
where  q.device_id is null and u.university = '复旦大学'
group by u.device_id

发表于 2022-06-08 10:46:17 回复(2)

Mysql

阿美真

SELECT q.device_id, university, count(question_id) as question_cnt, 
sum( if(result='right',1,0) ) as right_question_cnt
    
    from question_practice_detail q join user_profile u on q.device_id=u.device_id
    where university='复旦大学'
    group by q.device_id

发表于 2021-08-28 15:40:30 回复(4)

Mysql

冷凡社长

select t1.device_id,t1.university,
count(t2.question_id) as question_cnt,
sum(case when result='right' then 1 else 0 end) as right_question_cnt
from user_profile t1
left join question_practice_detail t2
using (device_id)
where university= '复旦大学' and (MONTH(date)=8 or date is null)
group by device_id

重点不要漏掉没有答题的用户

发表于 2022-07-15 17:27:07 回复(0)

Mysql

Stzolo

select a.device_id, a.university, 
sum(if(b.question_id is null ,  0 , 1 )) as question_cnt, 
sum(if(b.result='right' , 1 , 0)) as right_question_cnt
from user_profile a 
left join question_practice_detail b 
on a.device_id = b.device_id and month(b.date)='08'
where university = '复旦大学'
group by a.device_id, a.university

# 思路又臭又长的语句

发表于 2022-04-24 23:50:32 回复(0)

Tina要成为数据分析师呀

最开始写的，没有month(date) is null也通过了，看了其他同学的题解，发现自己忽略了条件，要把8月份没有答题的同学也算上，所以最后加上条件的是

select u.device_id,u.university,
count(q.question_id)as question_cnt,
sum(case when q.result='right' then 1 else 0 end) as right_question_cnt 
from user_profile u
left join question_practice_detail q 
on u.device_id=q.device_id
where ( month(q.date)=08&nbs***bsp;month(q.date) is null)
and u.university='复旦大学'
group by u.device_id

发表于 2021-12-29 22:33:14 回复(3)

流淌

select t1.device_id
,t1.university
,count(t2.device_id) ,
sum(if(t2.result='right',1,0))

from user_profile as t1 
left OUTER join 
(SELECT device_id,result,
SUBSTRING_INDEX(SUBSTRING_INDEX(date,'-',-2),'-',1) as m
FROM question_practice_detail
WHERE SUBSTRING_INDEX(SUBSTRING_INDEX(date,'-',-2),'-',1)='08') as t2

on t1.device_id = t2.device_id
where t1.university='复旦大学' 
group by t1.device_id

发表于 2021-09-23 15:57:20 回复(2)

渊荼

SELECT u.device_id,university,
SUM(IF(MONTH(date)='08',IF(result is not null,1,0),0)) AS question_cnt,
SUM(IF(MONTH(date)='08',IF(result is not null,IF(result='right',1,0),0),0)) AS right_question_cnt
FROM user_profile u
LEFT JOIN question_practice_detail qd
ON u.device_id=qd.device_id
WHERE university='复旦大学'
GROUP BY u.device_id;

发表于 2022-07-29 00:43:07 回复(0)

Mysql

踏实的大白菜说又不是不能用

select u.device_id,university
,sum(case when year(date) = 2021 and month(date) = 08 then 1
else 0 end) `question_cnt`,
sum(case when year(date) = 2021 and month(date) = 08 and q.result = 'right' then 1 else 0 end)`right_question_cnt`
from user_profile u
left join question_practice_detail q
on u.device_id = q.device_id
where university = '复旦大学'
group by u.device_id

发表于 2022-05-19 19:30:04 回复(0)

重铸广师荣光

select t1.device_id,university,
count(t2.question_id),
count(case when result='right' then 1 end)
from
user_profile t1 left join question_practice_detail t2
on t1.device_id = t2.device_id
where (month(date) = 8 or date is null) and university = '复旦大学'
group by t1.device_id

发表于 2022-05-10 20:32:16 回复(0)

戴子安

select
a.device_id
,a.university
,sum(if(b.date is null,0,1)) as question_cnt
,sum(if(result = 'right',1,0)) as right_question_cnt
from user_profile a
left join question_practice_detail b
on a.device_id = b.device_id and (month(b.date) = 08 or b.date is null)
where university = '复旦大学'
group by a.device_id

发表于 2022-04-27 11:56:30 回复(0)