sql面试题——手写sql练习案例（一）

1.求：找出所有科目成绩都大于某一学科平均成绩的学生

uid	subject_id	score
1001	01	90
1001	02	90
1001	03	90
1002	01	85
1002	02	85
1002	03	70
1003	01	70
1003	02	70
1003	03	85

先创建表

create table score(
    uid string,
    subject_id string,
    score int)
row format delimited fields terminated by '\t'; 

 1.1 求出每个学科平均成绩

select
    uid,
    score,
    avg(score) over(partition by subject_id) avg_score
from
    score;t1

1.2 根据是否大于平均成绩记录flag，大于则记为0，否则记为1

select
    uid,
    if(score>avg_score,0,1) flag
from
    t1;t2

1.3 根据学生的id进行分组统计flag的和，和为0则是所有学科都大于平均成绩

select
    uid
from
    t2
group by
    uid
having
    sum(flag)=0;

1.4 最终sql

select
    uid
from
    (select
    uid,
    if(score>avg_score,0,1) flag
from
    (select
    uid,
    score,
    avg(score) over(partition by subject_id) avg_score
from
    score)t1)t2
group by
    uid
having
    sum(flag)=0;

2. 要求使用SQL统计出每个用户的累积访问次数

数据：

用户id（userId）	月份（visitDate）	计数（visitCount）
u01	2020-10-28	5
u02	2020-9-27	6
u03	2020-10-16	8
u04	2020-10-13	3
u01	2020-10-24	6
u01	2020-10-09	8
u02	2020-10-25	6
u01	2020-9-18	4

创建表

create table visit
(userId string,
visitDate string,
visitCount int) 
row format delimited fields terminated by "\t";

2.1 计算每人单月访问量

select
    userId,
    visitDate,
    sum(visitCount) mn_count
from
    visit
group by
    userId,visitDate;t1

2.2 按月累计访问量

select
    userId,
    visitDate,
    mn_count,
    sum(mn_count) over(partition by userId order by visitDate)
from t1;

2.3 最终sql

select
     userId,
     visitDate,
     mn_count,
     sum(mn_count) over(partition by userId order by visitDate)
from
  (select
     userId,
     visitDate,
     sum(visitCount) mn_count
from visit
group by userId,visitDate)t1;