SQL Server: Display Users where a value follows another value in a single column-CodePudding

I have a table of users and their responses to a campaign:

user     campaign_id     date                response
John     5               2022/02/01 16:44    positive
Wendy    4               2022/02/01 16:00    negative
Wendy    5               2022/01/20 12:40    negative
John     16              2022/01/20 12:15    negative
Bob      16              2022/01/20 12:10    negative
Bob      5               2022/01/20 10:00    positive
John     10              2022/01/10 15:00    positive
Bob      2               2022/01/05 12:00    negative
Max      10              2022/01/05 11:45    positive

I'm attempting to write a query to just display users where we've received a negative response and then directly followed by a positive one (campaign_id doesn't matter).

My desired outcome

user     campaign_id     date                response
John     5               2022/02/01 16:44    positive
John     16              2022/01/20 12:15    negative
Bob      5               2022/01/20 10:00    positive
Bob      2               2022/01/05 12:00    negative

CodePudding user response：

You can use rank() to the group and order your users, considering testusers is your table name.

;With OrderedUsers as
(
    SELECT *
    , RANK() OVER (PARTITION BY [user] order by date) Attempt
    FROM testusers
)
SELECT F.* 
FROM OrderedUsers F
INNER JOIN OrderedUsers S
ON F.[user] = S.[user] 
  AND S.Attempt = F.Attempt  1
  AND S.response = 'positive'  
where F.response = 'negative'

UNION 
SELECT S.* 
FROM OrderedUsers F
INNER JOIN OrderedUsers S
ON F.[user] = S.[user] 
  AND S.Attempt = F.Attempt  1
  AND S.response = 'positive'  
where F.response = 'negative'