订单回购率/复购率
1. 计算本月和下月消费用户数以及相应的回购率
select a.umonth,count(a.customer_key) AS now_count,
count(b.customer_key) as next_count,
concat(round(count(b.customer_key)/count(a.customer_key)*100,2),'%')AS ratio
from
(select customer_key,DATE_FORMAT(create_date,'YYYY-MM') AS umonth
from ods_sales_orders group by customer_key,DATE_FORMAT(create_date,'YYYY-MM'))a
LEFT JOIN
(select customer_key,DATE_FORMAT(create_date,'YYYY-MM') AS umonth
from ods_sales_orders GROUP BY customer_key,DATE_FORMAT(create_date,'YYYY-MM'))b
ON a.customer_key = b.customer_key AND
concat(a.umonth,'-01')=add_months(concat(b.umonth,'-01'),-1)
GROUP BY a.umonth;
2.
对于以下需求：用户表：users (user_id int)
订单表：order_tb(user_id int, or_time date, or_money double)
求以下用户：
一月下过单，二月份没有下过单的三月份下单用户
的如下指标：
三月份订单金额大于100的订单数，三月份第一笔订单和最后一笔订单的订单金额。（每个用户的哦
with us1 as (select user_id,or_time,or_money,
sum(nvl(case when to_char(or_time,'MM') = '01' then 1 end,0)) over (partition by user_id) m1,
sum(nvl(case when to_char(or_time,'MM') = '02' then 1 end,0)) over (partition by user_id) m2,
sum(nvl(case when to_char(or_time,'MM') = '03' then 1 end,0)) over (partition by user_id) m3,
sum(case when to_char(or_time,'MM') = 03 and or_money > 100 then 1 end) over (partition by user_id) cnt
from order_tb
where or_time between to_date('20170101','YYYYMMDD') and to_date('20170331','YYYYMMDD')
),
mo1 as
(select distinct user_id,
case when to_char(or_time,'MM') = 03 then first_value(or_money) over (partition by user_id order by or_time) end f1v,
case when to_char(or_time,'MM') = 03 then last_value(or_money) over (partition by user_id order by or_time range between unbounded preceding and unbounded following) end f3l,
cnt from (select * from us1 where m2=0 and m1 >0 and m3 >0 and or_time between to_date('20170301','YYYYMMDD') and to_date('20170331','YYYYMMDD'))
)
