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!
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.