雪花的客户保留率

Question

我有这个用于客户保留率的

公式过去购买的客户[不包括最近30天]

Answer 1

如果我们检查每个客户是否有“旧销售”和“新销售

select customer_id, min(date) as min_date, max(date) as max_date, min_date < (current_date()-30) as old_sales, max_date >= (current_date()-30) as new_sales
from values
    (1,'2022-05-01'),
    (2,'2022-05-01'),
    (2,'2022-04-01'),
    (3,'2022-04-01'),
    (4,'2022-04-01'),
    (4,'2022-03-01')
    t(customer_id, date)
group by 1

得到

customer_id	min_date	max_date	old_sales	new_sales
1	2022-05-05-01	2022-05-05	”	：
，	会	我们	真实	true
3	2022-04-01	2022-04-01	true	false
4	2022-03-01	2022-04-01	，	false

我们想将两者都视为顶部的true，而trop/false to the the the the，我们可以使用：

select count_if(old_sales and new_sales) as top
    ,count_if(old_sales and  new_sales=false) as bottom
    ,div0(top, bottom) as rention
from (
    select customer_id, min(date) as min_date, max(date) as max_date, min_date < (current_date()-30) as old_sales, max_date >= (current_date()-30) as new_sales
    from values
        (1,'2022-05-01'),
        (2,'2022-05-01'),
        (2,'2022-04-01'),
        (3,'2022-04-01'),
        (4,'2022-04-01'),
        (4,'2022-03-01')
        t(customer_id, date)
    group by 1
);

并获取：

最底部	的	典范
1	2	0.5

因此，使用data_table作为我们可以使用的来源：

select div0(count_if(old_sales and new_sales), count_if(old_sales and  new_sales=false)) as rention
from (
    select 
        min(date) < (current_date()-30) as old_sales, 
        max(date) >= (current_date()-30) as new_sales
    from data_table
    group by customer_id
);

获得魔术50％