Finding all entries with no new reference in another table within last two years

Question

I have the following three tables:

CREATE TABLE group (
  id SERIAL PRIMARY KEY,
  name VARCHAR NOT NULL,
  insert_date TIMESTAMP WITH TIME ZONE NOT NULL
);

CREATE TABLE customer (
                         id SERIAL PRIMARY KEY,
                         ext_id VARCHAR NOT NULL,
                         insert_date TIMESTAMP WITH TIME ZONE NOT NULL
);

CREATE TABLE customer_in_group (
                          id SERIAL PRIMARY KEY,
                          customer_id INT NOT NULL,
                          group_id INT NOT NULL,
                          insert_date TIMESTAMP WITH TIME ZONE NOT NULL,
                          CONSTRAINT customer_id_fk
                              FOREIGN KEY(customer_id)
                                  REFERENCES customer(id),
                          CONSTRAINT group_id_fk
                              FOREIGN KEY(group_id)
                                  REFERENCES group(id)
)

I need to find all of the groups which have not had any customer_in_group entities' group_id column reference them within the last two years. I then plan to delete all of the customer_in_groups that reference them, and finally delete that group after finding them.

So basically given the following two groups and the following 3 customer_in_groups

Group
| id | name   | insert_date              |
|----|--------|--------------------------|
| 1  | group1 | 2011-10-05T14:48:00.000Z |
| 2  | group2 | 2011-10-05T14:48:00.000Z |

Customer In Group
| id | group_id | customer_id | insert_date              |
|----|----------|-------------|--------------------------|
| 1  | 1        | 1           | 2011-10-05T14:48:00.000Z |
| 2  | 1        | 1           | 2020-10-05T14:48:00.000Z |
| 3  | 2        | 1           | 2011-10-05T14:48:00.000Z |

I would expect just to get back group2, since group1 has a customer_in_group referencing it inserted in the last two years.

I am not sure how I would write the query that would find all of these groups.

Answer 1

As a starter, I would recommend enabling on delete cascade on foreing keys of customer_in_group.

Then, you can just delete the rows you want from groups, and it will drop the dependent rows in the child table. For this, you can use not exists:

delete from groups g
where not exists (
    select 1
    from customer_in_group cig
    where cig.group_id = g.id and cig.insert_date >= now() - interval '2 year'
)