如何使用SQL查询新客户、重复客户和过期客户

Question

我正在进行客户生命周期分析，其中我需要将帐户id和事务活动分组为三个桶:新的、重复的和失效的事务。

当一个客户在同一天进行了多次交易时，我在细分新客户和重复客户时遇到了麻烦。根据我们的定义，当客户在同一天使用不同的purchase_order_id进行多次交易时，他们应该被贴上"重复交易者"的标签。

名为direct_transactions的事务表包含以下列:

tbody> <<tr>

account_id	country_iso_cd	purchase_order_id	product_desc	transaction_date
642250536147127524	我们	1015182481	product_1	2020-09-18 10:58:02.000
4984541218433989577	我们	1015469885	product_2	2020-11-13 13:35:53.000

如果您只是创建一个CTE，它对purchase_order_id进行计数，这不会告诉您所需的信息-如果计数>那么他是回头客了?

WITH 
daily_counts AS ( 
SELECT 
DATE_TRUNC('day',transaction_date) AS transaction_date,
account_id,  
COUNT(DISTINCT purchase_order_id) AS daily_distinct_orders
FROM direct_transactions 
GROUP BY 1,2
)...