Posted by Xiaoyu, Nov. 2, 2023, 11:07 a.m.
Q131 churn account
I have a question regarding the following code from solution.
'potential both account' include 2 groups. completed deleted 'both' accounts and particial deleted 'both' accouts. In the 'deleted both account' table, when do the having clause, I did not see it filer the data before September. eg. for a 'both' account, what if one 'saving' account was deleted in August, another one 'checking' account was deleted in September?
Does that mean the affirm_account only inculde data that date <2021-09-01?
#code:
potential_deleted_both_account as(
select id, account_type
from affirm_account
where action = 'created' and id in(
select id
from affirm_account
where action = 'deleted' and date between '2021-08-01' and '2021-08-31'
)
and account_type ='both'
)
,deleted_both_account as(
select id
from affirm_account
where action = 'deleted'
and id in(
select id
from potential_deleted_both_account
)
group by id
having count(*) =2