Changes between Version 5 and Version 6 of WikiStart/Analytics

Timestamp:

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

Author:

203206

Comment:

db

WikiStart/Analytics

@@ -96 +96 @@
 }}}
 
+
+== Прикажи ги корисниците со најголем број на трансфери на пари(број на с-ка, корисничко име, e-mail, број на трансфери и ранг во растечки редослед)
+
+{{{
+WITH transfer_frequency AS (
+    SELECT t.s_id AS account_id, COUNT(*) AS transfer_count
+    FROM transfer t
+    GROUP BY t.s_id
+
+    UNION ALL
+
+    SELECT t.r_id AS account_id, COUNT(*) AS transfer_count
+    FROM transfer t
+    GROUP BY t.r_id
+)
+
+SELECT *
+FROM (
+    SELECT a.id AS account_id,
+           a.username,
+           a.email,
+           tf.transfer_count,
+           RANK() OVER (ORDER BY tf.transfer_count DESC) AS transfer_rank
+    FROM account a
+    JOIN transfer_frequency tf ON a.id = tf.account_id
+) ranked_accounts
+WHERE transfer_rank <= 3
+ORDER BY transfer_rank;
+
+
+}}}
+
+
+