Counting rows with a window function

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  |
+-------------+------------+--------------+-------------+

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
)

Advertisement

Answer