SQL面试题练习

刚开始自学MYSQL，看到一套面试题，做一做放上来记录。
别的博主也有做过其中一两道，学习过程中有参考大佬们的答案，按自己的理解写了一遍，有更简单的也有不太理解的，文末会把参考链接放上来。

第一题

首先建表插入数据：

create table program(
   date datetime,
   user varchar(20),
   age int,
   programid varchar(20),
   playtime varchar(20)
 );
#插入数据
insert into program values('2019-04-21','u1',30,'a','4min');
insert into program values('2019-04-21','u1',30,'b','10min');
insert into program values('2019-04-21','u2',27,'a','2min');
insert into program values('2019-04-22','u3',35,'c','3min');
insert into program values('2019-04-22','u2',27,'d','1min');

开始解题：
问题1：用户总量，用户平均年龄，用户平均观看时常

#子查询：按不同用户分组
select user,age,sum(playtime) sumtime from program group by user,age;
--解题：
select count(user),round(avg(age),2),round(avg(sumtime),2) 
from (
select user,age,sum(playtime) as sumtime from program group by user,age) temp;

问题2：每10岁一个分段，统计每个区间的用户总量，用户平均观看时长

#手动标记年龄段
select user,(case
when age between 0 and 9 then '0-9'
when age between 10 and 19 then '10-19'
when age between 20 and 29 then '20-29'
when age between 30 and 39 then '30-39'
when age