每日sql-复购率问题count+case+timediff类函

强推良心公众号：猴子数据分析
资料来源：
如何分析​复购用户？
人均付费如何分析？

今日要点：

• timestampdiff（返回的时间格式，起始时间，结束时间）
• case when ...then...else...end
• count（case ……）
• count（distinct）

image.png

select a.购买时间,
count(distinct a.用户id) 当日首次购买用户数,
count(distinct case when timestampdiff(month,a.购买时间,b.购买时间) <=1 
      then a.用户id  else null end ) as 此月复购用户数,
count(distinct case when timestampdiff(month,a.购买时间,b.购买时间) =3 
      then a.用户id  else null end ) as 第三月复购用户数,
count(distinct case when timestampdiff(month,a.购买时间,b.购买时间) =4
      then a.用户id  else null end ) as 第四月复购用户数,
count(distinct case when timestampdiff(month,a.购买时间,b.购买时间) =5
      then a.用户id  else null end ) as 第五月复购用户数,
count(distinct case when timestampdiff(month,a.购买时间,b.购买时间) =20
      then a.用户id  else null end ) as 第二十月复购用户数
from 课程订单表 as a
left join 课程订单表 as b
on a.`用户id` = b.`用户id`
where a.课程类型=2 and a.购买时间！=b.购买时间
group by a.购买时间;

timestampdiff与timediff的区别，前者可以返回时分秒的差，后者只能返回相差的天数

image.png

1、各地用户数（以后看到这个题目就要想到去重呀）

select 城市,count(distinct 用户id),sum(ARPU值)
from 各城市用户ARPU值
group by 城市

2、各城市各分段用户数是多少

select 
count（distinct case when ARPU值>0 and ARPU值<30 then 1 else null end ）as '(0-30)'
count（distinct case when ARPU值>=30 and ARPU值<50 then 1 else null end ）as '[30-50)'
count（distinct case when ARPU值>=50 and ARPU值<80 then 1 else null end ）as '[50-80)'
count（distinct case when ARPU值>=80  then 1 else null end ）as '[80以上)'
from 各城市用户ARPU值
group by 城市

3、找出表2中重复的用户数

select 用户id
from 用户套餐费用表
group by 用户id
having count(用户id)>2