Watch the video solution for Leetflex Banned Accounts, a medium level problem involving Database. This walkthrough by Nikunj Mistry has 559 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
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.
Example 1:
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.
Problem Overview: The database stores login sessions for Leetflex users with their account_id, ip_address, and login time window. An account should be banned if two sessions from the same account occur within 5 minutes but originate from different IP addresses. The goal is to return all such suspicious account IDs.
Approach 1: Self-Join on Login Records (O(n²) time, O(1) extra space)
The core idea is to compare each login session with every other session belonging to the same account. A SQL SELF JOIN lets you pair rows from the same table. Join the table to itself on account_id, then filter rows where the IP addresses differ and the login timestamps are within a 5‑minute window. The condition typically uses ABS(TIMESTAMPDIFF(MINUTE, a.login, b.login)) <= 5. Once such a pair exists, that account should be flagged as banned. Use DISTINCT to avoid duplicate account IDs in the final output. This approach works well because SQL engines efficiently execute joins with indexed columns like account_id and time fields.
The self-join technique is common in SQL interview problems that require comparing rows inside the same dataset. It appears frequently when detecting overlaps, duplicates, or suspicious activity patterns. Understanding how to structure join conditions and filter pairs is essential for database problems under the database category.
Approach 2: Window Function Comparison (O(n log n) time, O(n) space)
If the database supports window functions, you can sort sessions by account_id and login time, then compare each row with nearby rows using functions like LAG() or LEAD(). This reduces unnecessary comparisons by only checking adjacent sessions rather than every pair. After ordering sessions per account, compute the time difference and verify that IP addresses differ. When the difference is ≤ 5 minutes, the account is considered suspicious. This approach relies on features from advanced SQL query design and is often more efficient for large datasets.
Recommended for interviews: The self‑join solution is the expected approach. It clearly demonstrates that you understand relational comparisons inside a single table and how to filter based on multiple conditions. Explaining the brute-force pair comparison idea first, then translating it into a SQL self‑join, shows strong reasoning in database query design.
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Self-Join on Login Table | O(n²) | O(1) | Standard SQL solution for detecting row pairs within the same table |
| Window Function (LAG/LEAD) | O(n log n) | O(n) | Large datasets where ordered comparisons reduce pair checks |
