訂單回購率/復(fù)購率

1. 計(jì)算本月和下月消費(fèi)用戶數(shù)以及相應(yīng)的回購率

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.

對(duì)于以下需求：用戶表：users (user_id? int)

訂單表：order_tb(user_id int, or_time? date, or_money double)

求以下用戶：

一月下過單，二月份沒有下過單的三月份下單用戶

的如下指標(biāo)：

三月份訂單金額大于100的訂單數(shù)，三月份第一筆訂單和最后一筆訂單的訂單金額。（每個(gè)用戶的哦

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'))

)