新浪数据分析笔试题讨论
select p2.*,p7.growth
from
(select p1.channel_type,count(p1.uid)/count(distinct p1.uid)
from(select n.uid,n.channel_type,u.date,u.vid,u.tag
from new_user_info as n left join user_video as u
on n.uid=u.uid) as p1
where p1.date in ('20180710','20180711')
group by p1.channel_type) as p2
inner join
(select p4.channel_type,(p6.mean2-p4.mean1)/p4.mean1 as growth
from
(select p3.channel_type,count(p3.uid)/count(distinct p3.uid) as mean1
from(select n.uid,n.channel_type,u.date,u.vid,u.tag
from new_user_info as n left join user_video as u
on n.uid=u.uid) as p3
where p3.date = '20180710'
group by p3.channel_type) as p4
inner join
(select p5.channel_type,count(p5.uid)/count(distinct p5.uid) as mean2
from(select n.uid,n.channel_type,u.date,u.vid,u.tag
from new_user_info as n left join user_video as u
on n.uid=u.uid) as p5
where p5.date = '20180711'
group by p5.channel_type) as p6
on p4.channel_type=p6..channel_type
) as p7
select p1.uid
from
(select v.*,i.gender,i.age
from user_video v left join user_info i
on v.uid=i.uid) as p1
where p1.gender='女'
and p1.age<=25
and p1.tag='美食'
and p1.date in ('20190801','20190802','20190803','20190804','20190805','20190806','20190807')
group by p1.uid
having count(*)>10
except
select p2.uid
from
(select v.*,i.gender,i.age
from user_video v left join user_info i
on v.uid=i.uid) as p2
where p2.gender='女'
and p2.age<=25
and p2.tag='美食'
and p2.date ='20190808'
group by p2.uid
having count(*)<3;
3.第三题求不同频道观看最多的人和观看最少的人之间观看的次数差距
select p.tag,max(p.fre)-min(p.fre)
from
(select tag,uid,count(*) as fre
from user_video
group by tag,uid) p
group by p.tag;
