最新面试题集锦（4）-UV-PV-活跃分析SQL

--1--
with tmp1 as(
select
cuid
from tb_cuid_1d
where event_day='20200401'
group by cuid
)
,tmp2 as (
select
cuid
from tb_cuid_1d
where event_day=date_add('20200401',1)
group by cuid
)
,tmp3 as (
select
cuid
from tb_cuid_1d
where event_day=date_add('20200401',7)
group by cuid
)

select
count(1) as uv,
count(if(tmp2.cuid is not null,1,null)) as `次日留存`,
count(if(tmp3.cuid is not null,1,null)) as `7日留存`
from tmp1 left join tmp2 on tmp1.cuid=tmp2.cuid
          left join tmp3 on tmp1.cuid=tmp3.cuid
		  
--2--
with tmp as (
select
explode(ext) as ext
from tb_cuid_1d	
)
select ext.type as `type` from tmp 	  
;

--3--
with tmp as (
select
cuid,
concat_ws('_',os,cast(rand()*1000%6 as int)) as os,
concat_ws('_',soft_version,cast(rand()*1000%6 as int)) as soft_version
from tb_cuid_1d
)

select
split(os,'_')[0] as os,
split(soft_version,'_')[0] as soft_version
sum(pv) as pv_amt,
count(distinct cuid) as uv_amt

from 
(
select
cuid,os,soft_version,count(1) as pv
from tmp 
group by cuid,os,soft_version
) o
group by split(os,'_')[0],split(soft_version,'_')[0]

--3.1--
-- 不能直接加和
-- 加入有一个用户，在os1,ver1上有记录，在os2,ver2上也有记录，直接加和则重复了


--3.2--
select
count(distinct cuid) as uv_amt,
count(1) as pv_amt
from tb_cuid_1d
group by os,soft_version
grouping sets((),(os,soft_version))


--4--
select
count(distinct cuid) as dau_amt,
count(distinct if(b.uid is not null,1,null)) as dau_login_amt
from tb_cuid_1d a left join tb_account_1d b
on a.event_day='20200401' and b.event_day='20200401' and a.cuid=b.cuid

关注 易学在线 公众号

每日更新大量优质技术文档
第一时间获知最新上架课程
与众多大数据猿分享与交流