Changes between Version 5 and Version 6 of WikiStart/Optimization/vtora_verzija

Timestamp:

04/01/25 22:43:35 (3 months ago)

Author:

203206

Comment:

mk2

WikiStart/Optimization/vtora_verzija

@@ -1 @@
-== Оптимизиран SQL-код, кој работи поедноставно и побрзо
-
-
-=== DDL код
+= Оптимизиран SQL-код, кој работи поедноставно и побрзо
+
+
+== DDL код
 {{{
 create table account(
@@ -64 +64 @@
 
 
-=== Trigger 1
+== Trigger 1
 {{{
 create or replace function after_transaction_trigger()
@@ -110 +110 @@
 
 
-=== Trigger 2
+== Trigger 2
 
 {{{
@@ -153 +153 @@
 }}}
 
+
+== Прикажи ги корисниците со најголем број на трансфери на пари(број на с-ка, корисничко име, 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;
+
+
+}}}
+
+== Корисници кои направиле најголеми или примиле најголеми износи на трансфери и вкупен износ на трансфери(број на с-ка, корисничко име, 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; 
+}}}
+
+== Корисници со највисок износ на депозити
+
+{{{
+
+WITH deposit_totals AS (
+    SELECT account_id, SUM(amount) AS total_deposit
+    FROM deposit
+    GROUP BY account_id
+)
+
+SELECT a.id AS account_id,
+       a.username,
+       a.email,
+       COALESCE(dt.total_deposit, 0) AS total_deposit,
+       RANK() OVER (ORDER BY COALESCE(dt.total_deposit, 0) DESC) AS deposit_rank
+FROM account a
+LEFT JOIN deposit_totals dt ON a.id = dt.account_id
+ORDER BY deposit_rank
+LIMIT 3; 
+}}}
+
+
+== Најактивните корисници во последните 30 дена
+{{{
+WITH recent_activity AS (
+    SELECT account_id, COUNT(*) AS activity_count
+    FROM "transaction"
+    WHERE timestamp >= NOW() - INTERVAL '30 days'
+    GROUP BY account_id
+)
+
+SELECT a.id AS account_id,
+       a.username,
+       a.email,
+       COALESCE(ra.activity_count, 0) AS recent_transactions
+FROM account a
+LEFT JOIN recent_activity ra ON a.id = ra.account_id
+ORDER BY recent_transactions DESC
+LIMIT :top_n;
+
+
+
+}}}
+