自己的日志记录
后续慢慢更新

原帖：数据分析SQL面试题目9套汇总
https://www.jianshu.com/p/0f165dcf9525?utm_campaign=haruki&utm_content=note&utm_medium=reader_share&utm_source=weixin

面试题目二
1.题目如下

image

第二题

select userid, substring_index(GROUP_CONCAT(changjing ORDER BY inttime ),',',2) 
as id from
datafrog_test1
group by userid

用类似的上面的方法。。强行答了一道leetcode上的一道题= =。。。

image.png
image.png

select  na as Deparyment,substring_index(GROUP_CONCAT(name ORDER BY salary ),',',3) as Name from
(select * from employe a join 
(select id as di,name as na  from department)b
on a.department = b.di)p
group by na
order by salary desc

结果如图

image.png

这可以说是非常尴尬了
慢慢改

第二题

原帖有具体教程和方法和数据下载

select 
    t.*,
    concat(次日留存/活跃用户数*100,'%')次日留存率,
    concat(三日留存/活跃用户数*100,'%')三日留存率,
    concat(七日留存/活跃用户数*100,'%')七日留存率
    from
(select
    a.day1,
    count(distinct a.uid) as '活跃用户数',
    count(distinct case when day2 - day1 = 1 then a.uid end)次日留存,
    count(distinct case when day2 - day1 = 3 then a.uid end)三日留存,
    count(distinct case when day2 - day1 = 7 then a.uid end)七日留存
from
(select uid,date_format(dayno,'%Y%m%d') as day1 from userinfo where app_name = '相机')a
join
(select uid,date_format(dayno,'%Y%m%d') as day2 from userinfo where app_name = '相机')b
on a.uid = b.uid
group by a.day1)t

第三题
列转行

select id,teacher_id,
(case when week_day = 1 then "Yes" else " " end) "mon",
(case when week_day = 2 then "Yes" else " " end) "tue",
(case when week_day = 3 then "Yes" else " " end) "thi",
(case when week_day = 4 then "Yes" else " " end) "thu",
(case when week_day = 5 then "Yes" else " " end) "fri"
 from course;

但也可以尝试SQL五十练习第十七题的方法
感觉可以 失败了 还没想好提炼的规则，先放着

select 
    a.id,
    a.teacher_id,
    (select id from course where teacher_id = a.teacher_id and id = '01')  as mon,
    (select id from course where teacher_id = a.teacher_id and id = '02')  as tue,
    (select id from course where teacher_id = a.teacher_id and id = '03')  as thi,
    (select id from course where teacher_id = a.teacher_id and id = '04')  as thu,
    (select id from course where teacher_id = a.teacher_id and id = '05')  as fri
from course a

image.png

SQL五十練習

SQL50练习-csdn

-- 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息 
select a.s_id from
(select s_id,group_concat(c_id) as course from score group by s_id)a,
(select s_id,group_concat(c_id) as course from score group by s_id)b
where b.s_id = 01
and a.course = b.course
and a.s_id not in ('01')

第二天再写一遍，相似的写法...

-- 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息 
select b.s_id from
(select s_id,group_concat(c_id) as c1 from score group by s_id)a
,
(select s_id,group_concat(c_id) as c2 from score group by s_id)b
where a.c1 = b.c2 and a.s_id = 01
and b.s_id != 01

做到第十七题觉得比较有意思，是列转行的题

-- 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩