search

Home  >  Q&A  >  body text

SQL: Calculate if an ID that has only appeared in the last week has not appeared

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粉529581199P粉529581199448 days ago494

reply all(1)I'll reply

  • P粉818561682

    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 |
    +-----------------+---------+
    

    reply
    0
  • Cancelreply