While investigating the topic users table last month
I noticed quite a few rows had NULL first_visited_at dates even though the last_visited_at date was not null.
Not certain whether or not it might be due to my dev set-up I ran this query to clean up the cruft.
UPDATE topic_users SET first_visited_at = last_visited_at - interval '1 hour' WHERE last_visited_at IS NOT NULL AND first_visited_at IS NULL
(* I didn’t think to give a total_msecs_viewed - a query for another day)
Shortly after more of the mysterious NULL first_visited_at fields appeared.
I’ve finally narrowed it down to Category Watching.
i.e. when a member is Watching a Category a row is created in the topic_users table. The first_visited_at date is NULL when the member hasn’t visited the topic yet.
Unfortunately, when the member does get around to visiting the topic the first_visited_at field does not get populated.
These two queries should give much the same result
SELECT COUNT(id) FROM topic_users WHERE topic_users.first_visited_at IS NULL AND topic_users.last_visited_at IS NOT NULL
SELECT COUNT(topic_users.user_id) FROM topic_users JOIN topics ON topics.id = topic_users.topic_id WHERE topic_users.first_visited_at IS NULL AND topic_users.last_visited_at IS NOT NULL AND topic_users.notification_level = 3 AND topics.category_id IS NOT NULL AND topics.category_id IN( SELECT category_users.category_id FROM category_users WHERE category_users.notification_level = 3 ) AND topic_users.user_id IN( SELECT category_users.user_id FROM category_users WHERE category_users.notification_level = 3 )