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

问1：

select
 event_day,osname,soft_version,count(cuid)
 from tb_tmp_1d
 group by event_day,osname,soft_version
 grouping sets((event_day),(event_day,os_name),(event_day,soft_version))

问2：

 with tmp1 as (select cuid from tb_tmp_1d where event_day='20190101')
 ,tmp2 as (select cuid from tb_tmp_1d where event_day=date_add('20190101',1))
 ,tmp3 as (select cuid from tb_tmp_1d where event_day=date_add('20190101',7))

--次日留存
 select 
 '20190101' as newday,
 date_add('20190101',1) as rt1_day,
 cuid
 from tmp1 join tmp2 on tmp1.cuid=tmp2.cuid

--次7日留存
 select 
 '20190101' as newday,
 date_add('20190101',7) as rt7_day,
 cuid
 from tmp1 join tmp2 on tmp1.cuid=tmp2.cuid

问3：

select
 os_name,
 count(distinct a.cuid) as act_ucnts, 
 count(distinct b.cuid) as act_lg_ucnts
 from tb_cuid_1d a join tb_accounts_df b
 on a.cuid=b.cuid  and a.event_day='20200327'
 group by os_name

问4：

select
 cuid,
 avg(duration) as avg_duration
 from tb_cuid_1d
 group by cuid
 ;

问5：

 with tmp as (
 select
 cuid,
 count(1) as act_days
 from tb_cuid_1d
 where event_day between '20200320' and '20200326'
 group by cuid
 )
 select
 avg(act_days) as avg_act_days
 from tmp

关注 易学在线 公众号

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