Changes between Version 6 and Version 7 of WikiStart/Analytics

Timestamp:

03/30/25 21:21:25 (5 days ago)

Author:

203206

Comment:

db2

WikiStart/Analytics

@@ -128 +128 @@
 }}}
 
+== Корисници кои направиле најголеми или примиле најголеми износи на трансфери и вкупен износ на трансфери(број на с-ка, корисничко име, e-mail, вкупен износ на сите трансфери по корисник и ранг на корисник во растечки редослед)
 
+{{{
+WITH transfer_amount AS (
+    SELECT t.s_id AS account_id, SUM(tr.amount) AS total_transfer
+    FROM transfer t
+    JOIN "transaction" tr ON t.id = tr.id
+    GROUP BY t.s_id
 
+    UNION ALL
+
+    SELECT t.r_id AS account_id, SUM(tr.amount) AS total_transfer
+    FROM transfer t
+    JOIN "transaction" tr ON t.id = tr.id
+    GROUP BY t.r_id
+),
+
+aggregated_transfers AS (
+    SELECT account_id, SUM(total_transfer) AS total_transfer_amount
+    FROM transfer_amount
+    GROUP BY account_id
+)
+
+SELECT a.id AS account_id,
+       a.username,
+       a.email,
+       COALESCE(at.total_transfer_amount, 0) AS total_transfer_amount,
+       RANK() OVER (ORDER BY COALESCE(at.total_transfer_amount, 0) DESC) AS transfer_rank
+FROM account a
+LEFT JOIN aggregated_transfers at ON a.id = at.account_id
+ORDER BY transfer_rank
+LIMIT 3; 
+}}}