Find Third Transaction — LeetCode 2986 Python Solution

Table: Transactions +------------------+----------+ | Column Name | Type | +------------------+----------+ | user_id | int | | spend | decimal | | transaction_date | datetime | +------------------+----------+ (user_id, transaction_date) is column of unique values for this table. This table contains user_id, spend, and transaction_date.

+------------------+----------+
| Column Name      | Type     |
+------------------+----------+
| user_id          | int      |
| spend            | decimal  |
| transaction_date | datetime |
+------------------+----------+
(user_id, transaction_date) is column of unique values for this table.
This table contains user_id, spend, and transaction_date.

import duckdb
import pandas as pd

def solution(transactions: pd.DataFrame) -> pd.DataFrame:
    con = duckdb.connect()
    con.register("Transactions", transactions)
    return con.execute("""WITH
    T AS (
        SELECT
            *,
            RANK() OVER (
                PARTITION BY user_id
                ORDER BY transaction_date
            ) AS rk,
            spend > (
                LAG(spend) OVER (
                    PARTITION BY user_id
                    ORDER BY transaction_date
                )
            )
            AND spend > (
                LAG(spend, 2) OVER (
                    PARTITION BY user_id
                    ORDER BY transaction_date
                )
            ) AS st
        FROM Transactions
    )
SELECT user_id, spend AS third_transaction_spend, transaction_date AS third_transaction_date
FROM T
WHERE rk = 3 AND st = 1;""").df()