数据分析SQL面试题目9套汇总

面试题1

create table datafrog_test1
(userid varchar(20),
changjing varchar(20),
inttime varchar(20)
);

insert into datafrog_test1 values
(1,1001,1400),
(2,1002,1401),
(1,1002,1402),
(1,1001,1402),
(2,1003,1403),
(2,1004,1404),
(3,1003,1400),
(4,1004,1402),
(4,1003,1403),
(4,1001,1403),
(4,1002,1404),
(5,1002,1402),
(5,1002,1403),
(5,1001,1404),
(5,1003,1405);


答案：
select concat(t.userid, '-', substr(t.group_str, 1, 9)) as anwer
from (select userid,
             group_concat(distinct changjing order by inttime separator '-') as group_str
      from datafrog_test1
      group by userid) as t;

面试题2

答案：
select a.dayno,
       count(distinct a.uid)                                                                   as '活跃用户数',
       count(distinct if(datediff(b.dayno, a.dayno) = 1, b.uid, null))                         as '次日留存用户数',
       count(distinct if(datediff(b.dayno, a.dayno) = 2, b.uid, null))                         as '三日留存用户数',
       count(distinct if(datediff(b.dayno, a.dayno) = 6, b.uid, null))                         as '七日留存用户数',
       count(distinct if(datediff(b.dayno, a.dayno) = 1, b.uid, null)) / count(distinct a.uid) as 次日留存率,
       count(distinct if(datediff(b.dayno, a.dayno) = 2, b.uid, null)) / count(distinct a.uid) as 三日留存率,
       count(distinct if(datediff(b.dayno, a.dayno) = 6, b.uid, null)) / count(distinct a.uid) as 七日留存率
from (select * from act_user_info where app_name = '相机') as a
         left join
         (select * from act_user_info where app_name = '相机') as b on a.uid = b.uid
group by a.dayno;

面试题3

1.行转列(图中左变右)

create table course (
id varchar(20),
teacher_id varchar(20),
week_day varchar(20),
has_course varchar(20)
);
insert into course value
(1,1,2,"Yes"),
(2,1,3,"Yes"),
(3,2,1,"Yes"),
(4,3,2,"Yes"),
(5,1,2,"Yes");


select teacher_id,
       if(week_day = 1, 'yes', null) as one,
       if(week_day = 2, 'yes', null) as two,
       if(week_day = 3, 'yes', null) as three,
       if(week_day = 4, 'yes', null) as four,
       if(week_day = 5, 'yes', null) as five
from course;

面试题4

列转行

create table a1 (
name varchar(20),
english int,
maths int,
music int);
insert into a1 values
("Jim",90,88,99);


select name,
       'english' as subject,
       english as score
from a1
union
select name,
       'maths' as subject,
       maths as score
from a1
union
select name,
       'music' as subject,
       music as score
from a1;

面试题5

create table A2 (
FDATE datetime,
value int
);
insert into a2 values
("2018/11/23",10),
("2018/12/31",3),
("2019/2/9",53),
("2019/3/31",23),
("2019/7/8",11),
("2019/7/31",10);
# delete from a2 where value = 10;
select * from a2;

答案：

select *,
       sum(t.value) over (partition by t.year order by t.month) as YSUM,
       sum(t.value) over (order by t.year,t.month)              as SUM
from (select distinct year(FDATE)                                                       as year,
                      month(FDATE)                                                      as month,
                      sum(value) over (partition by month(FDATE) order by month(FDATE)) as value
      from a2
      order by year(FDATE)) as t;

面试五

create table user_order
(
    name      varchar(32),
    orderdate date,
    cost      int
)


insert into user_order values
('jack','2017-01-01',10),
('tony','2017-01-02',15),
('jack','2017-02-03',23),
('tony','2017-01-04',29),
('jack','2017-01-05',46),
('jack','2017-04-06',42),
('tony','2017-01-07',50),
('jack','2017-01-08',55),
('mart','2017-04-08',62),
('mart','2017-04-09',68),
('neil','2017-05-10',12),
('mart','2017-04-11',75),
('neil','2017-06-12',80),
('mart','2017-04-13',94);


（1）查询在2017年4月份购买过的顾客及总人数
select t.name,
       count(t.name) over () as c
from (select distinct name
      from user_order
      where left(orderdate, 7) = '2017-04') as t;
      
（2）查询顾客的购买明细及月购买总额
select t.name,
       t.orderdate,
       t.cost,
       sum(t.cost) over (partition by t.name,t.ym order by t.orderdate) as sum_month
from (select *,
             left(orderdate, 7) as ym
      from user_order) as t;

（3）上述的场景, 将每个顾客的cost按照日期进行累加
select *,
       sum(cost) over (partition by name order by orderdate) as sum
from user_order;

（4）查询每个顾客上次的购买时间
select *,
       lag(orderdate, 1, '1900-01-01') over (partition by name order by orderdate) as last_time
from user_order;

（5）查询前20%时间的订单信息
select t.name, t.orderdate, t.cost
from (select *,
             ntile(5) over (order by orderdate) as n
      from user_order) as t
where t.n = 1;

select t.name, t.orderdate, t.cost
from (select *,
             dense_rank() over (order by orderdate) as t_rank
      from user_order) as t
where t.t_rank <= ceil((select count(1)
                        from user_order) * 0.2);