mysqlgroup-byaggregate-functions

How to Retrieve First and Last Login Timestamps Per User Without Using Window Functions


I’m trying to get the first and last login timestamps for each user from a logins table that records a new timestamp each time a user logs in. Here’s the structure of my table:

user_id login_timestamp
1 2024-11-01 08:30:00
1 2024-11-02 09:15:00
1 2024-11-03 07:45:00
2 2024-11-01 12:20:00
2 2024-11-02 08:50:00

I want to generate a result that includes the first and last login timestamps for each user, like this:

user_id first_login last_login
1 2024-11-01 08:30:00 2024-11-03 07:45:00
2 2024-11-01 12:20:00 2024-11-02 08:50:00

The catch is that I need to achieve this without using OVER (PARTITION BY). How can I do this?

I attempted a query using GROUP BY, but I'm not sure how to combine MIN and MAX functions in a way that gives both first_login and last_login in one row per user. Here’s what I have so far:

SELECT user_id, MIN(login_timestamp) AS first_login, MAX(login_timestamp) AS last_login
FROM logins
GROUP BY 1

This seems to work, but I’d like to confirm if this is the best approach, or if there’s a more efficient way to get both timestamps without using window functions.

Any advice would be appreciated!


Solution

  • Your query is correct and efficient for getting the first and last login timestamps for each user without using window functions. The GROUP BY approach you used works well:

    SELECT user_id, MIN(login_timestamp) AS first_login, MAX(login_timestamp) AS last_login
    FROM logins
    GROUP BY user_id;
    

    This groups the data by user_id and uses MIN to get the first login and MAX for the last login. It's simple, effective, and performs well for most table sizes. Just make sure your login_timestamp column is indexed for better performance if your dataset is large.