sql归纳题

1.留存率题目

1）求五月份每日新设备的次日留存率
table1 (每日活跃设备表）：p_date 日期 ，device_id 设备id，is_new_device

select a.p_date,count(b.device_id)/count(a.device_id) rate
--先找出每日新增用户表
(select device_id,p_date
from table1
where is_new_device=1) a
left join 
(
--找出活跃用户表
select device_id,p_date
from table1 t1
left join table1 t2 on t1.device_id=t2.device_id
where t1.is_new_device=1 and t2.is_new_device=0 and datediff(t2.p_date,t1.p_date)=1) b

on a.p_date=b.p_date and month（a.p_date）=5
group by a.p_date
order by a.p_date

2）SQL164 2021年11月每天新用户的次日留存率
牛客网sql 164

select a.dt,round(count(b.uid)/count(a.uid),2)  uv_rate
from
-- 先找出当天新增的用户表
(select uid,min(date(in_time)) dt
from tb_user_log 
group by uid ) a
left join 
-- 用户活跃表
(select uid,date(in_time) dt
 from tb_user_log 
 union
 select uid,date(out_time) dt
 from tb_user_log) b
on a.uid=b.uid and a.dt=date_sub(b.dt,INTERVAL 1 day) -- 限制b表
where date_format(a.dt,'%Y-%m') = '2021-11'
group by a.dt
order by a.dt

3）直播题，一个主播开播一段时间内，如果没有观众观看，主播的体验相当差，请你帮忙监控体验差的开播次数占整体的比例：
主播表 table1：date, author, live_id, broadcast_start_time
观众表 table2：date, audience, live_id, watch_start_time
求开播开始的3分钟内没有观众观看的live_id数量占比

select sum(a.flag = 0) / sum(a.flag in (0, 1)) as rate
from
(
	select t1.live_id, 
    IF(count(t1.live_id) >= 1, 1, 0) as flag 
    # 1:有观众, 0:无观众
	from table1 t1 
    left join table2 t2
	on t1.live_id = t2.live_id and timestampdiff(MINUTE,t1.broadcast_start_time,t2.watch_start_time) <= 3
	group by t1.live_id
) a


select count(a.live_id)/count(t1.live_id)
from

(select live_id 
from table1
where live_id not in
    (select live_id 
     from table1 t1
     left join table2 t2 on t1.live_id=t2.live_id
     where timestampdiff(MINUTE,t1.broadcast_start_time,t2.  watch_start_time)<=3 ) )a #找出3分钟内没有观众观看的live_id

2.。。。

1）将作者按照粉丝层级划分成1-10W，10W-100W，100W+三类，统计每类作者数 以及 每类作者5月份的人均活跃天数
全量作者表table1: author_id, fans_count；    
每日作者活跃表 table2：p_date, author_id

表A：类型 + 每个类型作者数
select distinct a.type, count(*) as total_num
from 
(
 select author_id, 
 (
  case 
   when fans_count < 100000 then 0
   when fans_count < 1000000 then 1
   else 2
  end
 ) type
 from table1
) a // id对应类型
group by a.type




表B：类型 + 五月人均活跃天数

select b.type, avg(c.alive_days)
from 
(
 select author_id, 
 (
  case 
   when fans_count < 100000 then 0
   when fans_count < 1000000 then 1
   else 2
  end
 ) type
 from table1
) b,  // id对应类型
(
 select author_id, count(*) as alive_days
 from table2 
 where month(p_date) = 5
 group by author_id
) c  // 每个作者五月份活跃天数

where b.author_id = c.author_id

group by b.type

最后再把表A和表B按照type连起来即可





-----另外的写法---



--统计每类人数
select a.type,count(a.author_id) as num
from
--作者分好类
(select author_id,
case when fans_count>1 and fans_count<=100000 then '1~10w'
when fans_count<=1000000 then '10w~100w'
else '100w+' end as type
from table1 ) a

group by a.type


select b.type,avg(count(p_date))
from
(select author_id,
case when fans_count>1 and fans_count<=100000 then '1~10w'
when fans_count<=1000000 then '10w~100w'
else '100w+' end as type
from table1 ) b

left join table t2 
on b.author_id=t2.author_id
where month(p_date)=5
group by b.type

4.排序分类问题

1）求每门课前3名的记录：
成绩表 table1：course_name，student_name，score
select a.course_name,a.student_name,a.score
from
(select course_name,student_name,score,
dense_rank() over (partition by course_name order by score desc) RN  
FROM table1 ) a
where a.RN between 1 and 3

网易商业分析sql题

table1:客服id，接打的客户电话号码num，电话的开始时间start_time，电话时长length，客户满意度（满意or不满意）sat 1/0
求每个客服在这段时间内的服务量，每个客服的满意率，出每个客服的第一个不满意的电话号码

select e.id,e.sat_num/e.tot_num,d.first_0
from
#找出每个客服的满意数
((select id，count(*) sat_num
from table1
where sat=1 
group by id) a

#每个客服总的服务数
(select id,count(*) tot_num
from table1
group by id ) b
where a.id=b.id ) e
,
(select c.id,c.num as first_0
from
(select id,num，
rank() over (partition by id order by start_time asc) RN
from table1 
where sat=0 ) c
where c.RN=1  ) d

where d.id=e.id