活跃用户表:
领取红包表:
#1.计算2019年6月1日至今,每日DAU(活跃用户是指有登陆的用户) SELECt 登录日期, COUNT(DISTINCT 用户id) AS DAU FROM 活跃用户表 WHERe 登录日期 >= '20190601' GROUP BY 登录日期
#2.分析每天领取红包的用户数、人均领取金额、人均领取次数,要考虑用户属性及领取红包未登录情况。 select t1.抢红包日期 , count(distinct case when t2.是新用户="1" then t1.用户ID end) as 新用户数, count(distinct case when t2.是新用户="0" then t1.用户ID end) as 老用户数, count(distinct case when t2.是新用户 is null then t1.用户ID end) as 未登录用户数, sum(t1.金额)/count(distinct t1.用户ID) as 人均领取金额, count(*)/count(distinct t1.用户ID) as 人均领取次数 from 领取红包表 t1 left join 活跃用户表 t2 on t1.用户ID =t2.用户ID and t1.抢红包日期 =t2.登录日期 group by t1.抢红包日期 ;
#3.分析每个月红包领取天数、每个月领取红包的用户数、人均领取金额、人均领取次数。 SELECt DATE_FORMAT(抢红包日期, '%Y%m') AS 年月, COUNT(DISTINCT 抢红包日期) AS 每月红包领取天数, COUNT(DISTINCT 用户id) AS 每月领取红包用户数, SUM(金额) / COUNT(DISTINCT 用户id) AS 人均领取金额, COUNT(*) / COUNT(DISTINCT 用户id) AS 人均领取次数 FROM 领取红包表 GROUP BY DATE_FORMAT(抢红包日期, '%Y%m')
#4.分析每个月领过红包用户和未领红包用户的数量
select 年月,
count(distinct case when 各用户每月首次抢红包日期 is not null then 用户ID end) as 领过红包用户数,
count(distinct case when 各用户每月首次抢红包日期 is null then 用户ID end) as 未领过红包用户数
from
(select DATE_FORMAT(t1.登录日期, '%Y%m') as 年月 ,
t1.用户ID ,
t2.抢红包日期,
min(t2.抢红包日期)over(partition by DATE_FORMAT(t1.登录日期, '%Y%m'),用户ID) as 各用户每月首次抢红包日期
from 活跃用户表 t1
left join 领取红包表 t2
on t1.登录日期 =t2.抢红包日期
and t1.用户ID =t2.用户ID) t3
group by 年月;



