Objective

Given a table named events with the following columns:

Write an SQL query to fetch the most recent event for each user. The result should include the event_name, user_id, event_date, and status of these latest events, sorted in ascending order by user_id.

Additional information

• Each user may have multiple events recorded in the events table.
• The event_date for each event is unique per user.
• If a user has multiple events on the same latest date, you can return any one of them.
• Ensure that your query is optimized for performance, especially with large datasets.

SELECT
    e1.event_name,
    e1.user_id,
    e1.event_date,
    e1.status
FROM
    events e1
WHERE
    e1.event_date = (
        SELECT MAX(e2.event_date)
        FROM events e2
        WHERE e2.user_id = e1.user_id
    )
ORDER BY
    e1.user_id;

Explanation

Step 1: Correlated subquery to find the latest event date per user

For each row in e1, the subquery finds the maximum event_date among all events belonging to the same user_id. This gives the date of the most recent event for that user.

SELECT MAX(e2.event_date)
FROM events e2
WHERE e2.user_id = e1.user_id

Step 2: Filter to only rows matching the latest date

The WHERE clause keeps only those rows in e1 whose event_date equals the maximum date returned by the subquery, effectively selecting each user's most recent event.

WHERE e1.event_date = (
    SELECT MAX(e2.event_date)
    FROM events e2
    WHERE e2.user_id = e1.user_id
)

Step 3: Order by user_id

ORDER BY e1.user_id sorts the final results in ascending order so each user's latest event is easy to locate.

ORDER BY e1.user_id;