Leetflex Banned Accounts - Solution & Explanation
Problem Statement
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.
Approach Overview
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.
Solution
We can use a self-join to find out the cases where each account logs in from different IP addresses on the same day. The conditions for joining are:
- The account numbers are the same.
- The IP addresses are different.
- The login time of one record is within the login-logout time range of another record.
Code
MySQL
Detailed Complexity Analysis
| 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 |
Video Solution
Tech Job Goldmine: 50 Advanced SQL Questions to Earn You More! - Data Science | Leetcode 1747 • Everyday Data Science • 1,502 views views
Watch 1 more video solutions →Frequently Asked Questions
Is Leetflex Banned Accounts easy or hard?
Leetflex Banned Accounts Python/Java solution
How to solve Leetflex Banned Accounts in O(n)?
What is the best approach for Leetflex Banned Accounts?
Is Leetflex Banned Accounts asked at Google/Amazon/Meta?
What data structure is used in Leetflex Banned Accounts?
What is the time complexity of Leetflex Banned Accounts?
Ready to solve this problem?
Practice Leetflex Banned Accounts with our built-in code editor and test cases.
Practice on FleetCode