Трансакции според тагови со највисоко трошење за корисник

Сумирање на трошењата според тагови за одреден корисник

CREATE OR REPLACE FUNCTION get_total_spent_by_tag_user(
    p_user_id INT
)
RETURNS TABLE (
    tag_name TEXT,
    total_spent NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT 
        tg.tag_name, 
        COALESCE(SUM(tb.spent_amount), 0) AS total_spent
    FROM transaction_breakdown tb
    JOIN transaction t 
        ON tb.transaction_id = t.transaction_id
    JOIN transaction_account ta 
        ON tb.transaction_account_id = ta.transaction_account_id
    JOIN tag_assigned_to_transaction tat 
        ON t.transaction_id = tat.transaction_id
    JOIN tag tg 
        ON tat.tag_id = tg.tag_id
    WHERE ta.user_id = p_user_id
    GROUP BY tg.tag_name
    ORDER BY total_spent DESC;
END;
$$;

Релациона алгебра

• TA(transaction_account_id, user_id)
• TB(transaction_id, transaction_account_id, spent_amount)
• T(transaction_id, date)
• TAT(tag_id, transaction_id)
• TG(tag_id, tag_name)

JOIN на сите табели:

• J1 ← TA ⨝_{TA.transaction_account_id = TB.transaction_account_id} TB
• J2 ← J1 ⨝_{TB.transaction_id = T.transaction_id} T
• J3 ← J2 ⨝_{T.transaction_id = TAT.transaction_id} TAT
• J4 ← J3 ⨝_{TAT.tag_id = TG.tag_id} TG

Филтрирање по корисник:

• F ← σ_{user_id = p_user_id}(J4)

Групирање и агрегација по таг:

• G ← γ_{tag_name; SUM(spent_amount) → total_spent}(F)

Подредување по вкупно потрошено:

• R_final ← τ_{total_spent DESC}(G)