I'm trying to do a weekly scroll to see if a user did or did not attend an event. However, I only want to list them as "Not Attended"if they attended last week. For example, if a user attended weeks 1 and 7, they will only be counted as "not attending" in weeks 2 and 8.
Simplified example of data:
TableEvents, which includes all event dates (weekly) and unique event IDs (time ascending)
Event_Date | Event_ID |
---|---|
2023-03-09 | 1 |
2023-03-16 | 2 |
2023-03-23 | 3 |
TableUsersShows the event dates and IDs they attended:
Event_Date | Event_ID | User_ID |
---|---|---|
2023-03-09 | 1 | 151 |
2023-03-16 | 2 | 151 |
2023-03-23 | 3 | 151 |
2023-03-09 | 1 | 299 |
2023-03-23 | 3 | 299 |
2023-03-16 | 2 | 373 |
My question: How to use SQL to complete the following output? (User 299 participated in activity 1, but did not participate in activity 2; user 373 participated in activity 2, but did not participate in activity 3)
Missed_Event_Date | Missed_Event_ID | User_ID |
---|---|---|
2023-03-16 | 2 | 299 |
2023-03-23 | 3 | 373 |
I tried using
SELECT Event_ID + 1, User_ID FROM users u WHERE NOT EXISTS (SELECT 1 FROM events WHERE u.Event_ID=e.Event_ID + 1)
Does not produce correct output
P粉8185616822023-09-08 11:03:22
Assuming Event_ID
is increased by 1, you may also consider the following.
SELECT u.Event_ID + 1 AS Missed_Event_ID, u.User_ID FROM users u LEFT JOIN users v ON u.User_ID = v.User_ID AND u.Event_ID + 1 = v.Event_ID WHERE v.Event_ID IS NULL AND u.Event_ID < (SELECT MAX(Event_ID) FROM events); -- Query results +-----------------+---------+ | Missed_Event_ID | User_ID | +-----------------+---------+ | 2 | 299 | | 3 | 373 | +-----------------+---------+