I've got a collection of duplicate relations of relations that I need to tidy up.
The table might be described like this
posts id: 1 impressions id: 1, post_id: 1, created_at: 2020-01-02 id: 2, post_id: 1, created_at: 2020-01-02 id: 3, post_id: 1, created_at: 2020-01-03 demographic_distribution id: 1, impression_id: 1 id: 2, impression_id: 1 id: 3, impression_id: 2 id: 4, impression_id: 2 id: 5, impression_id: 3
I intend to remove the impression with id 1, but due to foreign key constraints, I would need to remove the related demographic_distributions first.
From demographic_distributions I would want to remove ids 1 & 2.
The following query works, but it's not very efficient. I have around 78 millions rows in the demographic_distribution table, and I have other similar sized related tables.
DELETE FROM demographic_distribution WHERE impression_id NOT IN ( SELECT id FROM impressions AS i1 WHERE id IN ( SELECT MAX(id) FROM impressions AS i2 WHERE i2.post_id = i1.post_id AND i2.created_at::date = i1.created_at::date GROUP BY i2.post_id ) );