复购客户sql

复购客户

用户分析是电商数据分析中重要的模块，在对用户特征深度理解和用户需求充分挖掘基础上，进行全生命周期的运营管理（拉新—>活跃—>留存—>价值提升—>忠诚），请尝试回答以下3个问题：
① 现在数据库中有一张用户交易表order，其中有userid（用户ID）、orderid（订单ID）、amount（订单金额）、paytime（支付时间），请写出对应的SQL语句，查出每个月的新客数（新客指在严选首次支付的用户），当月有复购的新客数，新客当月复购率（公式=当月有复购的新客数/月总新客数）。
② 当你发现最近一周APP端新访用户当天转化率（公式=新访当天支付人数/新访用户数，新访是指首次访问严选APP的设备）环比最近4周日均转化率大幅下跌（超30%），你会如何去探查背后的原因？请描述你的思路和其中涉及的关键指标
③ 在进行用户运营之前，我们通常会对用户进行分层，针对不同类型用户实施差异化的运营策略和资源投入，请你帮助设计严选用户分层的方案，包括关键特征的选择，分层的方法，如涉及模型/算法，请说明选择的算法类型、基本原理和步骤

（1）

Alter table order add column ym  varchar(255) not null
Update order set ym=date_format(paytime,’%Y-%M’)
##data_format()函数自定义时间格式为"年-月“

#0.找出每个用户第一次下单的时间和年月，此次是以用户分组进行group by,输出为用户id，第一次下单的时间，第一次下单的年月
select user_id,min(paytime),ym
from order
group by user_id;

#1.找出每月的新客户数目,此次以年月进行分组，找出某年某月第一次下单用户数
select ym,count(user_id) as '新客户数'
from 
(select user_id,min(paytime),ym
	from order 
	group by user_id
) as o1 #这是用户第一次
group by ym;


#2.找出当月有复购的新客户数目，分两步，第一步找出用户在第一次下单的那个月的购买次数，第二步是将购买次数大于2
select ym,o3.user_id,count(o3.user_id) as '有复购的新客户数目'
from
(select o2.user_id,count(o2.user_id) as '次数',o2.ym
from
(select user_id,min(paytime),ym
	from order 
	group by use_id) o1,order o2 
where o1.user_id=o2.user_id and o1.ym=o2.ym 
group by 02.user_id)as o3
where 次数>2
group by ym;

##3.将他们连接起来

select order1.年月,order1.新客户数 as 每月新客户数,if null(order2.有复购的新客户数,0) as 当月有复购的新客数,
(if null(order2.有复购的新客户数,0)/order1.新客户数) as 新客当月复购率
from 
(select 年月,count(userid) as 新客户数
from(select userid,min(paytime),年月
from `order`
group by userid) as o1
group by 年月) as order1 left join 
(
select 年月,count(userid) as `有复购的新客户数`
from
(
select o2.userid,count(o2.userid) as 次数,o2.年月
from
(select userid,min(paytime),年月
from `order`
group by userid) as o1,`order` o2
where o2.userid=o1.userid and o2.年月=o1.年月
group by o2.userid) as o3 
where 次数>=2
group by 年月) as order2
on order1.年月=order2.年月
order by order1.年月;