Each customer can have one or multiple accounts (account_id
).
To find customer churn, all accounts attached to a customer must be closed, i.e. having closed_date
e.g. here customer churn is 2.
How can I get the customer churn in Postgres? Thanks in advance!
+-------------+------------+--------------+-------------+ | customer_id | account_id | created_date | closed_date | +-------------+------------+--------------+-------------+ | 3eba3 | 5dddd | 17/06/2020 | | | 3eba3 | eabbd | 29/06/2020 | | | 3eba3 | 9f3a4 | 29/06/2020 | 09/11/2020 | | 5hlf1 | khti1 | 01/02/2020 | | | hdk12 | sfsf2 | 05/03/2020 | 01/06/2020 | | hdk12 | sfsl3 | 06/03/2020 | 01/06/2020 | | 12kju | gege1 | 07/03/2020 | 01/07/2020 | | 12kju | mhfl1 | 08/03/2020 | 03/07/2020 | +-------------+------------+--------------+-------------+
Advertisement
Answer
You can use aggregation:
select count(*) from ( select customer_id from mytable group by customer_id having bool_and(closed_date is not null) ) t
An alternative is count(distinct)
and not exists
:
select count(distinct customer_id) from mytable t where not exists ( select 1 from mytable t1 where t1.customer_id = t.customer_id and t1.closed_date is null )