I have a table that stores messages sent to users, the layout is as follows
id (auto-incrementing) | message_id | user_id | datetime_sent
user_id | message_id | datetime_opened
This is an untested answer to the original question (with 2 tables and condition on first 5):
SELECT DISTINCT user_id FROM ( SELECT om.user_id, om.message_id, count(DISTINCT sm2.message_id) messages_before FROM opened_messages om INNER JOIN sent_messages sm ON om.user_id = sm.user_id AND om.message_id = sm.message_id LEFT JOIN sent_messages sm2 ON om.user_id = sm2.user_id AND sm2.datetime_sent < sm.datetime_sent GROUP BY om.user_id, om.message_id HAVING messages_before < 5 ) AS base
The subquery joins in
sm2 to count the number of preceding messages that were sent to the same user, and then the
having clause makes sure that there are fewer than 5 earlier messages sent. As for the same user there might be multiple messages (up to 5) with that condition, the outer query only lists the unique users that comply to the condition.