Problem

Table: LogInfo

+-------------+----------+
| Column Name | Type     |
+-------------+----------+
| account_id  | int      |
| ip_address  | int      |
| login       | datetime |
| logout      | datetime |
+-------------+----------+
This table may contain duplicate rows.
The table contains information about the login and logout dates of Leetflex accounts. It also contains the IP address from which the account was logged in and out.
It is guaranteed that the logout time is after the login time.

Write a solution to find the account_id of the accounts that should be banned from Leetflex. An account should be banned if it was logged in at some moment from two different IP addresses.

Return the result table in any order.

The result format is in the following example.

Examples

Example 1:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

Input: 
LogInfo table:
+------------+------------+---------------------+---------------------+
| account_id | ip_address | login               | logout              |
+------------+------------+---------------------+---------------------+
| 1          | 1          | 2021-02-01 09:00:00 | 2021-02-01 09:30:00 |
| 1          | 2          | 2021-02-01 08:00:00 | 2021-02-01 11:30:00 |
| 2          | 6          | 2021-02-01 20:30:00 | 2021-02-01 22:00:00 |
| 2          | 7          | 2021-02-02 20:30:00 | 2021-02-02 22:00:00 |
| 3          | 9          | 2021-02-01 16:00:00 | 2021-02-01 16:59:59 |
| 3          | 13         | 2021-02-01 17:00:00 | 2021-02-01 17:59:59 |
| 4          | 10         | 2021-02-01 16:00:00 | 2021-02-01 17:00:00 |
| 4          | 11         | 2021-02-01 17:00:00 | 2021-02-01 17:59:59 |
+------------+------------+---------------------+---------------------+
Output: 
+------------+
| account_id |
+------------+
| 1          |
| 4          |
+------------+
Explanation: 
Account ID 1 --> The account was active from "2021-02-01 09:00:00" to "2021-02-01 09:30:00" with two different IP addresses (1 and 2). It should be banned.
Account ID 2 --> The account was active from two different addresses (6, 7) but in **two different times**.
Account ID 3 --> The account was active from two different addresses (9, 13) on the same day but **they do not intersect at any moment**.
Account ID 4 --> The account was active from "2021-02-01 17:00:00" to "2021-02-01 17:00:00" with two different IP addresses (10 and 11). It should be banned.

Solution

Method 1 – Self Join and Grouping

Intuition

If two different accounts log in from the same IP address with overlapping login/logout times, both accounts should be banned. We need to find all such pairs and collect all unique account IDs involved.

Approach

  1. Self-join the LogInfo table on ip_address, where account_id is different.
  2. Check if the login/logout intervals overlap: login_a < logout_b AND login_b < logout_a.
  3. Collect all account_ids from such pairs.
  4. Return the unique account_ids in ascending order.

Code

1
2
3
4
5
6
7
8

SELECT DISTINCT a.account_id AS banned_account
FROM LogInfo a
JOIN LogInfo b
  ON a.ip_address = b.ip_address
 AND a.account_id <> b.account_id
 AND a.login < b.logout
 AND b.login < a.logout
ORDER BY banned_account;
1
2
3
4
5
6
7
8

SELECT DISTINCT a.account_id AS banned_account
FROM LogInfo a
JOIN LogInfo b
  ON a.ip_address = b.ip_address
 AND a.account_id <> b.account_id
 AND a.login < b.logout
 AND b.login < a.logout
ORDER BY banned_account;
1
2
3
4
5
6
7

def banned_accounts(loginfo_df):
    import pandas as pd
    merged = loginfo_df.merge(loginfo_df, on='ip_address')
    merged = merged[merged['account_id_x'] != merged['account_id_y']]
    merged = merged[(merged['login_x'] < merged['logout_y']) & (merged['login_y'] < merged['logout_x'])]
    banned = pd.unique(merged['account_id_x'].tolist() + merged['account_id_y'].tolist())
    return sorted(banned)

Complexity

  • ⏰ Time complexity: O(n^2) for the self-join, where n is the number of log records.
  • 🧺 Space complexity: O(n) for storing the set of banned accounts.