Deduplicate User Login Sessions

SQL coding challenge · Difficulty: medium · +100 XP

In an online service, users may have multiple login sessions recorded due to technical issues. Given a 'logins' table, deduplicate login sessions for each user by keeping only the earliest session for any given day. Return user_id, login_date, and login_time for each unique user and day combination, ordered by user_id and login_date.

Example input

`

logins table:

login_id | user_id | login_date | login_time

1 | 201 | 2024-03-01 | 09:00:00

2 | 201 | 2024-03-01 | 12:30:00

3 | 201 | 2024-03-02 | 08:45:00

4 | 202 | 2024-03-01 | 10:15:00

5 | 202 | 2024-03-01 | 14:00:00

6 | 203 | 2024-03-03 | 11:00:00

7 | 203 | 2024-03-03 | 11:05:00

`

Expected output

`

user_id | login_date | login_time

201 | 2024-03-01 | 09:00:00

201 | 2024-03-02 | 08:45:00

202 | 2024-03-01 | 10:15:00

203 | 2024-03-03 | 11:00:00

`