Delete duplicates keeping the latest
SQL
Medium
3 views
Problem Description
In LoginLogs(user_id, logged_at, ip_address), remove duplicates where same user_id and ip_address appear multiple times on same day; keep the latest logged_at.
Output Format
DML statement(s)
Sample Test Case
Output:
Duplicates removed
Constraints
Use window function
Official Solution
DELETE FROM LoginLogs WHERE log_id IN (SELECT log_id FROM (SELECT log_id, ROW_NUMBER() OVER (PARTITION BY user_id, ip_address, CAST(logged_at AS DATE) ORDER BY logged_at DESC) AS rn FROM LoginLogs) t WHERE rn > 1);
Solutions (0)
No solutions submitted yet. Be the first!
No comments yet. Start the discussion!