I’ve made some progress.
Getting the only last_visited_at fields is straightforward enough.
SELECT topic_users.topic_id
, topic_users.user_id
, topic_users.last_visited_at
FROM topic_users
WHERE ( topic_users.first_visited_at IS NULL
AND topic_users.last_visited_at IS NOT NULL )
Getting the earliest post created_at fields was a bit more complex, but not all that bad to put together.
SELECT topic_users.topic_id
, topic_users.user_id
, MIN(posts.created_at)
FROM topic_users
JOIN posts
ON posts.topic_id = topic_users.topic_id
AND posts.user_id = topic_users.user_id
WHERE topic_users.posted
AND ( topic_users.first_visited_at IS NULL
AND topic_users.last_visited_at IS NOT NULL )
GROUP BY topic_users.topic_id, topic_users.user_id
Getting the earliest post_actions created_at was proving to be a hassle until I discovered a non-standard Postgres feature - “LATERAL”
SELECT tu.topic_id
, tu.user_id
, MIN(pa.created_at)
FROM ( SELECT topic_users.topic_id
, topic_users.user_id
FROM topic_users
WHERE ( topic_users.first_visited_at IS NULL
AND topic_users.last_visited_at IS NOT NULL )
) AS tu
JOIN LATERAL ( SELECT posts.id
FROM posts
WHERE posts.topic_id = tu.topic_id
) AS pt
ON true JOIN LATERAL ( SELECT post_actions.created_at
FROM post_actions
WHERE post_actions.post_id = pt.id
AND post_actions.user_id = tu.user_id
) AS pa
ON true
GROUP BY tu.topic_id, tu.user_id
Now to the temp table.