Changes between Initial Version and Version 1 of AdvancedReport6

Timestamp:

12/28/25 23:15:25 (5 days ago)

Author:

211101

Comment:

--

AdvancedReport6

@@ +1 @@
+===== Број на трансакции што ги надминуваат приходите на сметка
+{{{#!sql
+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;
+}}}
+Ако сакаме да ги добиеме вкупните трансакции кои ги надминуваат средствата низ целиот систем, без групирање по сметки:
+{{{#!sql
+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;
+}}}