==== Број на трансакции што ги надминуваат приходите на сметка
{{{#!sql
CREATE OR REPLACE FUNCTION get_account_transaction_overdraft_counts()
RETURNS TABLE (
    account_name TEXT,
    transactions_exceeding_balance INT
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    WITH cumulative_balances AS (
        SELECT
            ta.account_name,
            t.transaction_id,
            tb.spent_amount,
            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 transaction_breakdown tb
            ON ta.transaction_account_id = tb.transaction_account_id
        JOIN transaction t
            ON tb.transaction_id = t.transaction_id
    )
    SELECT
        account_name,
        COUNT(transaction_id) AS transactions_exceeding_balance
    FROM cumulative_balances
    WHERE
        spent_amount > calculated_balance
        AND spent_amount > 0
    GROUP BY account_name
    ORDER BY transactions_exceeding_balance DESC;
END;
$$;
}}}

==== Релациона алгебра
- TA(transaction_account_id, account_name)
- TB(transaction_id, transaction_account_id, spent_amount, earned_amount)
- T(transaction_id, date)

JOIN на сите табели:
- J1 ← TA ⨝,,TA.transaction_account_id = TB.transaction_account_id,, TB
- J2 ← J1 ⨝,,TB.transaction_id = T.transaction_id,, T

Пресметка на кумулативен баланс по сметка:
- CB ← γ,,account_name, transaction_id, spent_amount; Σ(earned_amount - spent_amount) OVER (PARTITION BY transaction_account_id ORDER BY date) → calculated_balance,,(J2)

Филтрирање на трансакции што го надминуваат балансот:
- F ← σ,,spent_amount > calculated_balance ∧ spent_amount > 0,,(CB)

Броење на трансакции по сметка:
- R ← γ,,account_name; COUNT(transaction_id) → transactions_exceeding_balance,,(F)

Подредување по број на прекршувања:
- R_final ← τ,,transactions_exceeding_balance DESC,,(R)

== 

> Ако сакаме да ги добиеме вкупните трансакции кои ги надминуваат средствата низ целиот систем, без групирање по сметки:
{{{#!sql
CREATE OR REPLACE FUNCTION get_total_transaction_overdraft_count()
RETURNS TABLE (
    total_transactions_exceeding_balance INT
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    WITH cumulative_total_balances AS (
        SELECT
            t.transaction_id,
            tb.spent_amount,
            SUM(tb.earned_amount - tb.spent_amount)
                OVER (PARTITION BY u.user_id ORDER BY t.date)
                AS calculated_total_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
    )
    SELECT
        COUNT(transaction_id) AS total_transactions_exceeding_balance
    FROM cumulative_total_balances
    WHERE
        spent_amount > calculated_total_balance
        AND spent_amount > 0;
END;
$$;
}}}

==== Релациона алгебра
- U(user_id)
- TA(transaction_account_id, user_id)
- TB(transaction_id, transaction_account_id, spent_amount, earned_amount)
- T(transaction_id, 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

Пресметка на кумулативен вкупен баланс по корисник:
- CT ← γ,,transaction_id, spent_amount; Σ(earned_amount - spent_amount) OVER (PARTITION BY user_id ORDER BY date) → calculated_total_balance,,(J3)

Филтрирање на трансакции што го надминуваат вкупниот баланс:
- F ← σ,,spent_amount > calculated_total_balance ∧ spent_amount > 0,,(CT)

Броење на сите прекршувачки трансакции:
- R_final ← γ,,; COUNT(transaction_id) → total_transactions_exceeding_balance,,(F)