Листа на трансакции кои го надминале балансот на една сметка подредени хронолошки

CREATE OR REPLACE FUNCTION get_transactions_exceeding_account_balance(
    p_account_name TEXT
)
RETURNS TABLE (
    user_id INT,
    user_name TEXT,
    account_name TEXT,
    transaction_id INT,
    transaction_name TEXT,
    transaction_amount NUMERIC,
    transaction_date TIMESTAMP,
    calculated_balance NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    WITH cumulative_balances AS (
        SELECT
            u.user_id,
            u.user_name,
            ta.account_name,
            t.transaction_id,
            t.transaction_name,
            tb.spent_amount AS transaction_amount,
            t.date AS transaction_date,
            SUM(tb.earned_amount - tb.spent_amount)
                OVER (PARTITION BY ta.transaction_account_id ORDER BY t.date)
                AS calculated_balance
        FROM transaction_account ta
        JOIN "user" u ON ta.user_id = u.user_id
        JOIN transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id
        JOIN transaction t ON tb.transaction_id = t.transaction_id
        WHERE ta.account_name = p_account_name
    )
    SELECT *
    FROM cumulative_balances
    WHERE transaction_amount > calculated_balance
      AND transaction_amount > 0
    ORDER BY transaction_date DESC;
END;
$$;

Релациона алгебра

• U(user_id, user_name)
• TA(transaction_account_id, user_id, account_name)
• TB(transaction_id, transaction_account_id, spent_amount, earned_amount)
• T(transaction_id, transaction_name, date)

JOIN на сите табели:

• J1 := TA ⨝_{TA.user_id = U.user_id} U
• J2 := J1 ⨝_{TA.transaction_account_id = TB.transaction_account_id} TB
• J3 := J2 ⨝_{TB.transaction_id = T.transaction_id} T

Филтер според име на акаунт:

• F1 := σ_{account_name = p_account_name}(J3)

Пресметка на кумулативен баланс:

• CB := γ_{user_id, user_name, account_name, transaction_id, transaction_name, transaction_amount, transaction_date;

Σ(transaction_amount) OVER (PARTITION BY transaction_account_id ORDER BY transaction_date) → calculated_balance}(F1)

каде transaction_amount = spent_amount и кумулативната сума е Σ(earned_amount - spent_amount)

Филтрирање на трансакциите кои го надминуваат балансот:

• R := σ_{transaction_amount > calculated_balance ∧ transaction_amount > 0}(CB)

Подредување хронолошки(почнувајќи од најновата трансакција до најстарата):

• R_final := τ_{transaction_date DESC}(R)