==== Просечно трошење во последните N дена
- по корисник (со параметар p_user_id)
- за цел систем (со параметар p_user_id = NULL)

{{{#!sql
CREATE OR REPLACE FUNCTION get_average_daily_spending_last_N_days(
    p_days INT,
    p_user_id INT DEFAULT NULL  -- ако е NULL, се зема цел систем
)
RETURNS TABLE (
    average_spending NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT
        SUM(daily_spending) / p_days AS average_spending
    FROM (
        -- генерација на последни p_days
        SELECT
            d.day::date AS transaction_date,
            COALESCE(SUM(tb.spent_amount), 0) AS daily_spending
        FROM (
            SELECT generate_series(CURRENT_DATE - (p_days - 1), CURRENT_DATE, '1 day') AS day
        ) d
        LEFT JOIN transaction t
            ON t.date::date = d.day
        LEFT JOIN transaction_breakdown tb
            ON t.transaction_id = tb.transaction_id
        LEFT JOIN transaction_account ta
            ON tb.transaction_account_id = ta.transaction_account_id
            AND (p_user_id IS NULL OR ta.user_id = p_user_id)
        GROUP BY d.day
    ) daily_totals;
END;
$$;
}}}

==== Релациона алгебра
- TA(transaction_account_id, user_id)
- T(transaction_id, date)
- TB(transaction_id, spent_amount)
- D(day) -- генерација на последните p_days

Генерација на последни p_days:
- D ← { CURRENT_DATE − (p_days − 1), ..., CURRENT_DATE }

LEFT JOIN со трансакции:
- J1 ← D ⟕,,TRUE,, T
- J2 ← J1 ⟕,,T.transaction_id = TB.transaction_id,, TB
- J3 ← J2 ⟕,,TB.transaction_account_id = TA.transaction_account_id ∧ (p_user_id IS NULL OR TA.user_id = p_user_id),, TA

Групирање по ден и пресметка на дневно трошење:
- G ← γ,,day; Σ(spent_amount) → daily_spending,,(J3)

Просечно дневно трошење (фиксно делење со p_days):
- R_final ← γ,,; (Σ(daily_spending) / p_days) → average_spending,,(G)