SQL题目5：一个关于用户留存得笔试题

SQL大数据开发 2021-04-19

1130

前几天写了个题目。题目5：一个关于用户留存得笔试题,是一个笔试题，今天有朋友在群里问答案，我就把自己的思路写了一下，欢迎各位大佬指点，由于马上过十二点了。怕错过今天发文章的时间，所以没有创建表进行测试，大家可以测试一下

1.查询2019年7月，累计文章点击量超过十万的媒体名，以及每个媒体展示量排名前五的文章id,展示量，阅读量

解答：根据文章表可以得到后面的文章ID 展示量和阅读量，媒体名称可以在媒体表中得到，有一个限制是只查询2019年7月

SQL：

with t1 as 
(
SELECT   媒体id,文章id,展示量,阅读量,sum(点击量)over(partition by 媒体id ) as 媒体点击量
,row_number()over(partition by 媒体id order by 展示量 desc )  as 媒体内排名
  from 文章表  where 时间 = 201907  
  )
  select t2.媒体名称,文章id,展示量,阅读量 from t1 t  inner  join 媒体表 t2  on t1.媒体id =t2.媒体id 
  where t1.媒体内排名 <=5

2.查询2019年7月1日关注粉丝人数Top100的媒体名称，以及其粉丝的7日留存率和30日留存率。

注:留存率=登录用户数/用户数*100% (一般统计周期为天) ;

7日留存率= (第1天登陆过的用户中，在第7天依然登录的用户数) 第1天总用户数*100%。

解答：限制20190701的关注人数，选出前一百名,然后计算粉丝七日留存和三十日留存，单纯从这个题目来说可以直接求出20190701

这天的关注明细，20190708的上线明细，20190731 的上线明细，进行关联计算

SQL:

with t1 as 
(
select 媒体id ,row_number()over(partition by 媒体id order by count(1) desc ) as 排名
 from 留存表 where 时间 = 20190701  group by  媒体id  
 )
 ,t2 as 
 (select 媒体id from t1 where 排名<=100)
 ,t3 as 
 (select 粉丝_id ,媒体_id from 留存表  inner join t2 on 留存表.媒体_id=t2.媒体_id  and 留存表.时间=20190701 )
 ,t4 as 
 (select 粉丝_id ,媒体_id from 留存表  inner join t2 on 留存表.媒体_id=t2.媒体_id  and 留存表.时间=20190708)
 ,t5 as 
 (select 粉丝_id ,媒体_id from 留存表  inner join t2 on 留存表.媒体_id=t2.媒体_id  and 留存表.时间=20190731)
 select t3.媒体_id,
 sum(case when t4.粉丝_id is null then 0 else 1 end )/count(1) as 七日留存 ,
 sum(case when t5.粉丝_id is null then 0 else 1 end )/count(1) as 三十日留存 ,
 from t3 left join t4 on t3.粉丝_id=t4.粉丝_id and t3.粉丝_id=t4.粉丝_id  
 left join t5 on t3.粉丝_id=t5.粉丝_id and t3.粉丝_id=t5.粉丝_id
group by t3.媒体_id

3.现发现粉丝关系表中存在一个用户同时关注多个媒体的情况存在，例:户Id为1001的用户，对应的用户关注媒体id数据为1010,1020,1031。

请将该表中的这种情况进行拆分为多条，使每条数据中的用户id只对应-个用户关注媒体id。

解答：这个有两个方式第一个是使用一个叫做 split的函数可以直接拆，在这个题目里面也可以使用查找字符位置的方式

使用两表关联进行拆分

SQL:

with fensi1 as 
(
select 10001 as fensi_id , '1001,1002' as meiti_id from dual union all
select 10002 ,'1001,1002,1003' from dual  union all
select 10003,'1002,1004,1006' from dual 
)
,meiti as 
(
select '1001' as meiti_id ,'河南卫视' as meiti_name from dual union all
select '1002' ,'山东卫视' from dual union all
select '1003' ,'辽宁卫视' from dual union all
select '1004' ,'山西卫视' from dual union all
select '1005' ,'江苏卫视' from dual union all
select '1006' ,'吉林卫视' from dual 
)
select * from fensi1 f ,meiti m where instr(','||f.meiti_id,','||m.meiti_id )>0

刚开始编写文章，很多地方都有不足，希望以后可以为大家贡献出更好的文章，谢谢大家！有哪里不明白的地方可以和作者联系~

没有关注的也可以关注下公众号~再次感谢

数据库

最后修改时间：2021-04-19 17:17:01

文章转载自SQL大数据开发，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

SQL题目5：一个关于用户留存得笔试题

评论