Аналитика и статистика

Детален преглед на трансакции помеѓу корисниците по датум

SELECT 
    tm.s_account_id AS sender_id,
    a_s.username AS sender_username,
    tm.r_account_id AS receiver_id,
    a_r.username AS receiver_username,
    tm.t_id,
    t.timestamp,
    t.amount
    
FROM 
    transferred_money tm
JOIN 
    account a_s ON tm.s_account_id = a_s.id
JOIN 
    account a_r ON tm.r_account_id = a_r.id
JOIN
    "transaction" t ON tm.t_id = t.id; 

Трансакции до одреден датум и време

SELECT 
    tm.s_account_id AS sender_id,
	a_s.username AS sender_username,
	tm.r_account_id AS receiver_id,
	a_r.username AS receiver_username,
    tm.t_id,
	t.timestamp,
	t.amount
    
FROM 
    transferred_money tm
JOIN 
    account a_s ON tm.s_account_id = a_s.id
JOIN 
    account a_r ON tm.r_account_id = a_r.id
JOIN
    "transaction" t ON tm.t_id = t.id
WHERE
      timestamp<'2025-03-23 20:30:00.000000';

Трансакции по валута, подредени по време

SELECT 
    tm.s_account_id AS sender_id,
	a_s.username AS sender_username,
	tm.r_account_id AS receiver_id,
	a_r.username AS receiver_username,
    tm.t_id,
	t.timestamp,
	t.amount,
	tm.currency
    
FROM 
    transferred_money tm
JOIN 
    account a_s ON tm.s_account_id = a_s.id
JOIN 
    account a_r ON tm.r_account_id = a_r.id
JOIN
    "transaction" t ON tm.t_id = t.id
WHERE currency='EUR'
ORDER BY timestamp;

Максимум, минимум и просечен износ на сума по трансакција во одредена валута

SELECT 
	max(t.amount) as maximum,
	avg(t.amount) as "average",
	min(t.amount) as minimum
    
FROM 
    transferred_money tm
JOIN 
    account a_s ON tm.s_account_id = a_s.id
JOIN 
    account a_r ON tm.r_account_id = a_r.id
JOIN
    "transaction" t ON tm.t_id = t.id
WHERE currency='USD';

Прикажи ги корисниците со најголем број на трансфери на пари(број на с-ка, корисничко име, 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;

Индекс клиент

create index client on account(username, email, balance);


SELECT indexname, tablename
FROM pg_indexes 
WHERE tablename = 'account';

Инфо индекс

create index form on "transaction"(currency,"type",timestamp);
select * from pg_indexes  where tablename='transaction';