Број на трансакции што ги надминуваат приходите на сметка

WITH CumulativeBalances AS (
    SELECT 
        t.transaction_id,
        ta.account_name,
        SUM(tb.earned_amount - tb.spent_amount) OVER (
            PARTITION BY ta.transaction_account_id
            ORDER BY t.date
        ) AS calculated_balance,
        tb.spent_amount
    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 
    CumulativeBalances
WHERE 
    spent_amount > calculated_balance -- Кога потрошениот износ го надминува пресметаниот баланс
    AND spent_amount > 0
GROUP BY 
    account_name
ORDER BY 
    transactions_exceeding_balance DESC;

Ако сакаме да ги добиеме вкупните трансакции кои ги надминуваат средствата низ целиот систем, без групирање по сметки:

WITH CumulativeTotalBalances AS (
    SELECT 
        t.transaction_id,
        u.user_id,
        SUM(tb.earned_amount - tb.spent_amount) OVER (
            PARTITION BY u.user_id
            ORDER BY t.date
        ) AS calculated_total_balance,
        tb.spent_amount
    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 
    CumulativeTotalBalances
WHERE 
    spent_amount > calculated_total_balance -- Кога потрошениот износ го надминува кумулативниот баланс
    AND spent_amount > 0;