Customers with Maximum Number of Transactions on Consecutive Days — LeetCode 2752 Python Solution

Table: Transactions +------------------+------+ | Column Name | Type | +------------------+------+ | transaction_id | int | | customer_id | int | | transaction_date | date | | amount | int | +------------------+------+ transaction_id is the column with unique values of this table. Each row contains information about transactions that includes unique (customer_id, transaction_date) along with the corresponding customer_id and amount.

+------------------+------+
| Column Name      | Type |
+------------------+------+
| transaction_id   | int  |
| customer_id      | int  |
| transaction_date | date |
| amount           | int  |
+------------------+------+
transaction_id is the column with unique values of this table.
Each row contains information about transactions that includes unique (customer_id, transaction_date) along with the corresponding customer_id and amount.

import duckdb
import pandas as pd

def solution(transactions: pd.DataFrame) -> pd.DataFrame:
    con = duckdb.connect()
    con.register("Transactions", transactions)
    return con.execute("""WITH
    s AS (
        SELECT
            customer_id,
            DATE_SUB(
                transaction_date,
                INTERVAL ROW_NUMBER() OVER (
                    PARTITION BY customer_id
                    ORDER BY transaction_date
                ) DAY
            ) AS transaction_date
        FROM Transactions
    ),
    t AS (
        SELECT customer_id, transaction_date, COUNT(1) AS cnt
        FROM s
        GROUP BY 1, 2
    )
SELECT customer_id
FROM t
WHERE cnt = (SELECT MAX(cnt) FROM t)
ORDER BY customer_id;""").df()