Tracking the most recent event you invited someone to (SQLite.)

In this case we have a contact table to store our little black book, an events table to track ongoings we did, and an attendance record that states someone showed up to the event.

We then use a fangly query to map attendance records to the name and date of the event, collapse it to a single attendance per contact, and join it with the contact list to get the list of contacts we know of and the most recent event (and when) they attended.

This list then tells us who we should probably consider inviting to a new event of some kind.

The database:

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE events(id integer, subject text, event_at integer);
INSERT INTO events VALUES(100,'D&D One-shot',1000);
INSERT INTO events VALUES(90,'Hanging out',900);
CREATE TABLE contact(id integer, name text);
INSERT INTO contact VALUES(500,'Blob Cat');
INSERT INTO contact VALUES(510,'Blob Fox');
INSERT INTO contact VALUES(520,'Blob Bunny');
CREATE TABLE attendance(id integer, event_id integer, contact_id integer);
INSERT INTO attendance VALUES(-8722670385899813800,100,500);
INSERT INTO attendance VALUES(9131040737207445484,100,510);
INSERT INTO attendance VALUES(-9152198680421745238,90,500);
COMMIT;

The query:

select contact.name, subject, event_at
from contact left join
  (select *
    from attendance
    join events on attendance.event_id = events.id
    group by contact_id
    order by max(event_at))
  on contact_id = contact.id;

The results correctly show us that Blob Cat and Blob Fox's most recent event was the D&D One-shot, which ocurred at 1,000 Unix time. Blob Bunny has never attended an event in our database.