Detecting Multi-Device Login Anomalies | Dataford Interview Questions

Your question is Detecting Multi-Device Login Anomalies. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

TikTok Trust and Safety needs to identify accounts with simultaneous logins from different device types and measure each account's total active session time without double-counting overlapping intervals. Write a PostgreSQL query using the provided tables.

Requirements

Consider only completed sessions where ended_at is not null.
Flag users with at least one pair of overlapping sessions from different devices. Treat a null device value as different from a non-null device value.
Merge overlapping or touching session intervals for each user before calculating total duration.
Return anomalous users with their username, number of distinct cross-device overlapping pairs, and total active minutes, ordered by total active minutes descending.

Schema

users

Column	Type	Description
user_idPK	INT	TikTok user identifier
username	VARCHAR(50)	TikTok account username
country_code	CHAR(2)	User country code

login_sessions

Column	Type	Description
session_idPK	INT	Login session identifier
user_id	INT	References users.user_id
device_type	VARCHAR(20)	Device category used for login
started_at	TIMESTAMP	Session start timestamp
ended_at	TIMESTAMP	Session end timestamp