Find if event happened within N days after every record using SQL

Im trying to figure out a way to in SQL find if an event had a different event happening after it within a N days period

So for example, if my data looks as follows:

User_ID Code    Date
1122    A   01-01-2020
1122    A   02-01-2020
1122    A   02-01-2020
2233    A   04-01-2020
2233    A   04-01-2020
1122    B   04-01-2020
3344    A   07-01-2020
3344    B   07-01-2020
2233    A   08-01-2020

and say N=2, meaning i need to find if an event B happened within 2 days an event A for each user, and if that is the case, i need to filter out the event A

So in my data, there is one event A on day 1 and two events A for user 1122 on day 2 and then an event B on day 4. The event B happened 3 days after the first event A for user 1122, and 2 days after the two events A for user 1122. So those two A event should be tagged: (same for user 3344 who had an event B in the same day as an A, so complies with the 2 days as well)

  User_ID   Code    Date    B_within_2_days
1122    A   01-01-2020  NO
1122    A   02-01-2020  YES
1122    A   02-01-2020  YES
2233    A   04-01-2020  NO
2233    A   04-01-2020  NO
1122    B   04-01-2020  NO
3344    A   07-01-2020  YES
3344    B   07-01-2020  NO
2233    A   08-01-2020  NO

Leave a Reply

Your email address will not be published.