Concurrent Multi-IP Session Violations

Medium

A platform security team audits session telemetry to detect account sharing and session hijacking. Table: log_info account_id: user account identifier ip_address: source IP for that session login: session start timestamp logout: session end timestamp Notes: The table may contain duplicate rows. Each row has logout strictly later than login. Task: Return accounts that were active from two different IP addresses at any overlapping instant. Overlap definition: Treat every session as a closed interval [login, logout]. Two sessions overlap if they share at least one timestamp. Example: one session ending exactly at 17:00:00 and another starting at 17:00:00 counts as overlap. Output requirements: Return exactly one column named account_id. Each account_id should appear at most once. Sort by account_id ascending. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

log_info:
| account_id | ip_address | login               | logout              |
|------------|------------|---------------------|---------------------|
| 1          | 101        | 2024-03-01 09:00:00 | 2024-03-01 09:30:00 |
| 1          | 202        | 2024-03-01 08:00:00 | 2024-03-01 11:30:00 |
| 2          | 301        | 2024-03-01 20:30:00 | 2024-03-01 22:00:00 |
| 2          | 302        | 2024-03-02 20:30:00 | 2024-03-02 22:00:00 |
| 3          | 401        | 2024-03-01 16:00:00 | 2024-03-01 16:59:59 |
| 3          | 402        | 2024-03-01 17:00:00 | 2024-03-01 17:59:59 |
| 4          | 501        | 2024-03-01 16:00:00 | 2024-03-01 17:00:00 |
| 4          | 502        | 2024-03-01 17:00:00 | 2024-03-01 17:59:59 |

Output

[
  {"account_id":1},
  {"account_id":4}
]

Explanation

Account 1 has fully overlapping sessions on different IPs. Account 4 overlaps at the exact boundary timestamp 17:00:00, which is also considered overlap.

Example 2

Input

log_info:
| account_id | ip_address | login               | logout              |
|------------|------------|---------------------|---------------------|
| 7          | 7001       | 2024-06-10 10:00:00 | 2024-06-10 10:45:00 |
| 7          | 7002       | 2024-06-10 10:45:01 | 2024-06-10 11:15:00 |
| 8          | 8101       | 2024-06-11 09:00:00 | 2024-06-11 12:00:00 |
| 8          | 8102       | 2024-06-11 11:59:59 | 2024-06-11 13:00:00 |

Output

[
  {"account_id":8}
]

Explanation

Account 7 has a one-second gap, so no overlap. Account 8 overlaps for one second from 11:59:59 to 12:00:00 and is flagged.

Example 3

Input

log_info:
| account_id | ip_address | login               | logout              |
|------------|------------|---------------------|---------------------|
| 11         | 9001       | 2024-07-01 09:00:00 | 2024-07-01 10:00:00 |
| 11         | 9001       | 2024-07-01 09:15:00 | 2024-07-01 09:45:00 |
| 11         | 9002       | 2024-07-01 10:00:01 | 2024-07-01 10:30:00 |
| 12         | 9101       | 2024-07-02 14:00:00 | 2024-07-02 15:00:00 |
| 12         | 9102       | 2024-07-02 14:30:00 | 2024-07-02 14:40:00 |
| 12         | 9102       | 2024-07-02 14:30:00 | 2024-07-02 14:40:00 |

Output

[
  {"account_id":12}
]

Explanation

Overlapping sessions from the same IP do not trigger a flag for account 11. Account 12 has a different-IP overlap and duplicate rows still yield a single account in output.

Constraints

1 <= number of rows in log_info <= 100000
1 <= account_id <= 1000000000
1 <= ip_address <= 1000000000
login and logout are valid datetime values with second precision
logout is strictly later than login for every row
Rows may be duplicated
Return exactly one column: account_id
Each account_id must appear at most once in the final output
Sort by account_id ascending
SQL submissions must follow SQLite 3.27.2 behavior
Pandas submissions must return a DataFrame with column account_id

log_info: | account_id | ip_address | login | logout | |------------|------------|---------------------|---------------------| | 1 | 101 | 2024-03-01 09:00:00 | 2024-03-01 09:30:00 | | 1 | 202 | 2024-03-01 08:00:00 | 2024-03-01 11:30:00 | | 2 | 301 | 2024-03-01 20:30:00 | 2024-03-01 22:00:00 | | 2 | 302 | 2024-03-02 20:30:00 | 2024-03-02 22:00:00 | | 3 | 401 | 2024-03-01 16:00:00 | 2024-03-01 16:59:59 | | 3 | 402 | 2024-03-01 17:00:00 | 2024-03-01 17:59:59 | | 4 | 501 | 2024-03-01 16:00:00 | 2024-03-01 17:00:00 | | 4 | 502 | 2024-03-01 17:00:00 | 2024-03-01 17:59:59 |

log_info: | account_id | ip_address | login | logout | |------------|------------|---------------------|---------------------| | 7 | 7001 | 2024-06-10 10:00:00 | 2024-06-10 10:45:00 | | 7 | 7002 | 2024-06-10 10:45:01 | 2024-06-10 11:15:00 | | 8 | 8101 | 2024-06-11 09:00:00 | 2024-06-11 12:00:00 | | 8 | 8102 | 2024-06-11 11:59:59 | 2024-06-11 13:00:00 |

log_info: | account_id | ip_address | login | logout | |------------|------------|---------------------|---------------------| | 11 | 9001 | 2024-07-01 09:00:00 | 2024-07-01 10:00:00 | | 11 | 9001 | 2024-07-01 09:15:00 | 2024-07-01 09:45:00 | | 11 | 9002 | 2024-07-01 10:00:01 | 2024-07-01 10:30:00 | | 12 | 9101 | 2024-07-02 14:00:00 | 2024-07-02 15:00:00 | | 12 | 9102 | 2024-07-02 14:30:00 | 2024-07-02 14:40:00 | | 12 | 9102 | 2024-07-02 14:30:00 | 2024-07-02 14:40:00 |