Changes between Initial Version and Version 1 of AdvancedReport3

Timestamp:

12/28/25 23:13:36 (6 days ago)

Author:

211101

Comment:

--

AdvancedReport3

@@ +1 @@
+===== Листа на трансакции кои го надминале балансот на сите сметки вкупно подредени хронолошки
+{{{#!sql
+CREATE OR REPLACE FUNCTION get_transactions_exceeding_user_total_balance()
+RETURNS TABLE (
+    user_id INT,
+    user_name TEXT,
+    transaction_id INT,
+    transaction_name TEXT,
+    transaction_amount NUMERIC,
+    transaction_date TIMESTAMP,
+    calculated_total_balance NUMERIC
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    RETURN QUERY
+    WITH cumulative_totals AS (
+        SELECT
+            u.user_id,
+            u.user_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 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 *
+    FROM cumulative_totals
+    WHERE transaction_amount > calculated_total_balance
+      AND transaction_amount > 0
+    ORDER BY user_id, transaction_date DESC;
+END;
+$$;
+}}}