时间戳

首页 > 解决方案 > 标记活跃客户 - 每月至少一笔交易

sql - 标记活跃客户 - 每月至少一笔交易

问题描述

客户注册后，在 date_registered 和当前日期之间 - 如果客户每个月至少进行了一笔交易，则将其标记为活动，否则将其标记为非活动

注意：每个客户都有不同的 date_registered

我试过了，但没有奏效，因为年中很少有客户入职 例如 -

-------------------------------------
txn_id | txn_date | name | amount
-------------------------------------
101     2018-05-01  ABC    100
102     2018-05-02  ABC    200
-------------------------------------

       (case when count(distinct case when txn_date >= '2018-05-01' and txn_date < '2019-06-01' then last_day(txn_date) end) = 13
             then 'active' else 'inactive'
        end) as flag
from t;

最终输出

----------------
name | flag
----------------
ABC    active
BCF    inactive

标签： sqlhive

解决方案

您可以对聚合查询使用过滤：

select customer,
       count(distinct last_day(txn_date)) as num_months
from (select t.*, min(date_registered) over (partition by customer) as min_dr
      from t
     ) t
group by customer, min_dr
having count(distinct last_day(txn_date)) = months_between(last_day(current_date), last_day(min_dr)) + 1;

注意：如果客户在当月的第一天并非全部有交易，这可能会在月初产生意想不到的结果。

编辑：

如果你想要一个标志，只需将HAVING逻辑移动到SELECT：

select customer,
       (case when count(distinct last_day(txn_date)) = months_between(last_day(current_date), last_day(min_dr)) + 1
             then 'Active' else 'Inactive'
        end) as active_flag
from (select t.*, min(date_registered) over (partition by customer) as min_dr
      from t
     ) t
group by customer, min_dr;

推荐阅读