問(wèn)題：

1

2

答案：

第2題：

select *
from
(select date_format(min(pay_dt),'%Y-%m') as 月份,country as 國(guó)家,'新用戶' as 用戶類型,user_id as 用戶id,sum(pay_dt=首次購(gòu)買時(shí)間) as 新用戶訂單數(shù),sum((pay_dt=首次購(gòu)買時(shí)間)*cost_amt) as 新用戶訂單金額,rank() over(partition by date_format(min(pay_dt),'%Y-%m'),country order by sum((pay_dt=首次購(gòu)買時(shí)間)*cost_amt) desc) as 排名
from
(select *,min(pay_dt) over(partition by user_id) as 首次購(gòu)買時(shí)間 from dw_shein_pay_order_da) as t1
group by country,user_id) as t2
where 排名<=300;

第3題：

select
date_format(首次購(gòu)買時(shí)間,'%Y-%m') as 月份,
country as 國(guó)家,
count(distinct if(pay_dt=首次購(gòu)買時(shí)間,user_id,null)) as 新用戶數(shù),
count(distinct if(timestampdiff(day,首次購(gòu)買時(shí)間,pay_dt)<=30),user_id,null) as 30日復(fù)購(gòu)用戶數(shù),
sum(if(timestampdiff(day,首次購(gòu)買時(shí)間,pay_dt)<=30),pay_amt,null) as 30日復(fù)購(gòu)金額,
count(distinct if(timestampdiff(day,首次購(gòu)買時(shí)間,pay_dt)<=30),user_id,null)/count(distinct if(pay_dt=首次購(gòu)買時(shí)間,user_id,null)) as 30日復(fù)購(gòu)率
from
(select * ,min(pay_dt) over(partition by user_id) as 首次購(gòu)買時(shí)間 from dw_shein_pay_order_da) as t
group by date_format(首次購(gòu)買時(shí)間,'%Y-%m'),country;