Each customer can have one or multiple accounts (account_id
)
How can I get max closed_date
for the customers who closed all their accounts and the rest of the active customers?*
*active customers are the ones with at least one open account.
+-------------+------------+--------------+-------------+ | 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/10/2020 | | 12kju | gege1 | 07/03/2020 | 01/07/2020 | | 12kju | mhfl1 | 08/03/2020 | 03/07/2020 | +-------------+------------+--------------+-------------+
Desired output:
+-------------+-------------+ | customer_id | closed_date | +-------------+-------------+ | 3eba3 | | | 5hlf1 | | | hdk12 | 01/10/2020 | | 12kju | 03/07/2020 | +-------------+-------------+
Advertisement
Answer
You can use distinct on
for this:
select distinct on (customer_id) t.* from t order by customer_id, closed_date desc nulls first;
This returns one row per customer_id
based on the order by
clause.