Write a query to find each user's previous login date?
Interview preparation resource from Gate Smashers.
SELECT user_id, login_date, LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date) AS previous_login FROM User_Logins;
| user_id | login_date | session_minutes |
|---|---|---|
| 1 | 2024-07-01 | 35 |
| 1 | 2024-07-02 | 42 |
| 1 | 2024-07-03 | 28 |
| 2 | 2024-07-01 | 20 |
| 2 | 2024-07-03 | 25 |
| 3 | 2024-07-02 | 45 |
SELECT
user_id,
login_date,
LAG(login_date) OVER (
PARTITION BY user_id
ORDER BY login_date
) AS previous_login
FROM User_Logins;Query
Use the LAG window function to access the previous login_date per user. The query returns each row with an extra column previous_login containing the prior login_date for that user (or NULL for their first login).
- SQL: LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date) AS previous_login
How it works
The window specification PARTITION BY user_id restarts the history for each user. ORDER BY login_date establishes the chronological sequence within that user's rows so LAG returns the immediately preceding login_date.
- Partition: PARTITION BY user_id — separate history per user
- Order: ORDER BY login_date — chronological order for LAG to reference
- First row: The first login for a user has no previous row, so previous_login is NULL
Notes & edge cases
If multiple logins can share the same login_date, include a more granular ordering column (for example a timestamp or a unique event ID) in the ORDER BY to make the previous row deterministic.
- Ties: Add timestamp or event_id to ORDER BY when login_date is not unique
Sample data reference
See the sample User_Logins rows below. Run the query against that table to get each row's previous_login.