Contact Management with PostgreSQL

create table contacts (uid text primary key, name text);

create table interactions (uid text primary key, contact_uid text, notes text, created_at timestamp);

Show every contact who does not have any interactions logged:

select contacts.name from contacts
left join interactions i
on i.contact_uid = contacts.uid
where i.contact_uid is null;

Show interactions which happen within the recency window:

select contact_uid, created_at from interactions where created_at > now()-interval '1 day';

Join contacts table with interactions in the past interval; show people with no interactions in that interval

select contacts.name from contacts
left join (select contact_uid, created_at from interactions where created_at > now()-interval '1 day') i
on i.contact_uid = contacts.uid
where i.contact_uid is null;