Brandon Brandon - 4 months ago 11
SQL Question

Select first N messages each user receives

I have a table that stores messages sent to users, the layout is as follows

id (auto-incrementing) | message_id | user_id | datetime_sent


I'm trying to find the first N message_id's that each user has received, but am completely stuck. I can do it easily on a per-user basis (when defining the user ID in the query), but not for all users.

Things to note:


  • Many users can get the same message_id

  • Message ID's aren't sent sequentially (i.e. we can send message 400 before message 200)

  • This is a read only mySQL database



EDIT: On second thought I removed this bit but have added it back in since someone was kind enough to work on it

The end goal is to see what % of users opened one of the first N messages they received.

That table of opens looks like this:

user_id | message_id | datetime_opened

Answer

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.

Comments