How to write a query that returns rows that do not have a specific value in a dynamic list of future-CodePudding

I am working on a problem trying to accurately identify customers that have left a business service permanently. Currently, there is an incorrect definition of these customers defined as 'churners' in operation within the business.

'Churners' may re-enter the platform in the following months, and I don't want to include those specific accounts in my result set.

These accounts appear similar to the following table:

ID	Month_End_Date	Activity_Flag
123	31/07/22	Customer
123	30/06/22	Customer
123	31/05/22	Customer
123	30/04/22	Customer
123	31/03/22	Customer
123	28/02/22	Order
123	31/01/22	Churn

Whereas an actual 'churner' should appear as the following:

ID	Month_End_Date	Activity_Flag
321	31/07/22	x
321	30/06/22	x
321	31/05/22	x
321	30/04/22	x
321	31/03/22	x
321	28/02/22	x
321	31/01/22	Churn

And in some cases, these customers DO NOT populate any further rows (unique by Month_End_Date) within the dataset:

ID	Month_End_Date	Activity_Flag
321	31/01/22	Churn

So my question is, how can I write an SQL query that will show me the accounts that meet the parameter of Activity_Flag = 'Churn' and DO NOT HAVE any further Activity_Flags within the table itself?

Hope this is enough information,

I have tried to filter these applicable customers within the dataset by understanding unique monthly churners and their following behaviour (activity_type) but to no avail.

CodePudding user response：

You can use the Row_Number function with the Partition By clause and Order by Month_End_Date descending to get only records where the last record for the given ID has an Activity_Flag of Churn:

select x.ID
from
(
    select ID, Activity_Flag, 
    Row_Number() Over (Partition By ID 
    Order By Month_End_Date desc) as rn
    from yourtable
) x
where x.rn = 1 and x.Activity_Flag = 'Churn'