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

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

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;