PostgreSQL – I want to query out the latest Subscription status for every client with the date

The client can opt-in or opt-out for subscription from time to time. I need the latest Status of every client with the subscription date.

If the client has opted-in at the first instance and never opted-out again, I need the first date when they opted-in. However, if the same client has opted-out after opting-in and then opted-in again, I need to know the date corresponding to the latest opt-in. (FIRST YES or FIRST YES after NO).

Can anyone guide how to query this in Postgresql.

For example, for Client C

Answer

Try this query:

WITH CTE1 AS
(
SELECT *,
ROW_NUMBER() OVER(PARTITION BY ClientID ORDER BY SubscriptionDate DESC) as Rn
FROM Table_name
WHERE SubscriptionStatus = 'Y'
)
SELECT ClientID, Source, 
SubscriptionStatus, SubscriptionDate
FROM CTE1
WHERE Rn = 1;