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
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
Assuming
Event_IDis 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 | +-----------------+---------+