CODEWITHSUNDEEP
sqlpostgresqldatetimewhere-clausegreatest-n-per-group
kimi
kimi

Reputation: 525

Get rows with a condition

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

Upvotes: 0

Views: 47

Answers (2)

GMB
GMB

Reputation: 222462

You can use aggregation and a case expression:

select customer_id,
    case when bool_and(closed_date is not null) then max(closed_date) end as closed_date
from mytable
group by customer_id

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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.

Upvotes: 2

Related Questions