How hard is LeetCode 1843. Suspicious Bank Accounts?

LeetCode 1843. Suspicious Bank Accounts is rated Medium on LeetCode.

What topics does LeetCode 1843. Suspicious Bank Accounts cover?

LeetCode 1843. Suspicious Bank Accounts is tagged Database on LeetCode.

Is LeetCode 1843. Suspicious Bank Accounts a premium problem?

Yes. LeetCode 1843. Suspicious Bank Accounts is a LeetCode Premium problem, so the full statement and test cases require a paid LeetCode subscription.

Suspicious Bank Accounts — LeetCode 1843 Python Solution

MediumLeetCode PremiumDatabase

Problem: #1843
Reading time: 5 min
Source: leetcode.com

Table schema

SQL

Table: Accounts +----------------+------+ | Column Name | Type | +----------------+------+ | account_id | int | | max_income | int | +----------------+------+ account_id is the column with unique values for this table. Each row contains information about the maximum monthly income for one bank account.

Example

SQL

+----------------+------+
| Column Name    | Type |
+----------------+------+
| account_id     | int  |
| max_income     | int  |
+----------------+------+
account_id is the column with unique values for this table.
Each row contains information about the maximum monthly income for one bank account.

Python solution

Python

import duckdb
import pandas as pd

def solution(accounts: pd.DataFrame, transactions: pd.DataFrame) -> pd.DataFrame:
    con = duckdb.connect()
    con.register("Accounts", accounts)
    con.register("Transactions", transactions)
    return con.execute("""WITH
    S AS (
        SELECT DISTINCT
            t.account_id,
            DATE_FORMAT(day, '%Y-%m-01') AS day,
            transaction_id AS tx,
            SUM(amount) OVER (
                PARTITION BY account_id, DATE_FORMAT(day, '%Y-%m-01')
            ) > max_income AS marked
        FROM
            Transactions AS t
            LEFT JOIN Accounts AS a ON t.account_id = a.account_id
        WHERE type = 'Creditor'
    )
SELECT DISTINCT s1.account_id
FROM
    S AS s1
    LEFT JOIN S AS s2 ON s1.account_id = s2.account_id AND TIMESTAMPDIFF(Month, s1.day, s2.day) = 1
WHERE s1.marked = 1 AND s2.marked = 1
ORDER BY s1.tx;""").df()

Complexity

Measure	Complexity
Time	O(n log n) (typical)
Space	O(n) auxiliary

Frequently asked questions

How hard is LeetCode 1843. Suspicious Bank Accounts?: LeetCode 1843. Suspicious Bank Accounts is rated Medium on LeetCode.
What topics does LeetCode 1843. Suspicious Bank Accounts cover?: LeetCode 1843. Suspicious Bank Accounts is tagged Database on LeetCode.
Is LeetCode 1843. Suspicious Bank Accounts a premium problem?: Yes. LeetCode 1843. Suspicious Bank Accounts is a LeetCode Premium problem, so the full statement and test cases require a paid LeetCode subscription.