扫码加入数据分析学习群

问题：

1

2

答案：

第2题：

select *
from
(select date_format(min(pay_dt),'%Y-%m') as 月份,country as 国家,'新用户' as 用户类型,user_id as 用户id,sum(pay_dt=首次购买时间) as 新用户订单数,sum((pay_dt=首次购买时间)*cost_amt) as 新用户订单金额,rank() over(partition by date_format(min(pay_dt),'%Y-%m'),country order by sum((pay_dt=首次购买时间)*cost_amt) desc) as 排名
from
(select *,min(pay_dt) over(partition by user_id) as 首次购买时间 from dw_shein_pay_order_da) as t1
group by country,user_id) as t2
where 排名<=300;

第3题：

select
date_format(首次购买时间,'%Y-%m') as 月份,
country as 国家,
count(distinct if(pay_dt=首次购买时间,user_id,null)) as 新用户数,
count(distinct if(timestampdiff(day,首次购买时间,pay_dt)<=30),user_id,null) as 30日复购用户数,
sum(if(timestampdiff(day,首次购买时间,pay_dt)<=30),pay_amt,null) as 30日复购金额,
count(distinct if(timestampdiff(day,首次购买时间,pay_dt)<=30),user_id,null)/count(distinct if(pay_dt=首次购买时间,user_id,null)) as 30日复购率
from
(select * ,min(pay_dt) over(partition by user_id) as 首次购买时间 from dw_shein_pay_order_da) as t
group by date_format(首次购买时间,'%Y-%m'),country;