= Напредни извештаи од базата (SQL и складирани процедури)

===== Листа на трансакции кои го надминале балансот на една сметка подредени хронолошки
{{{#!sql
WITH CumulativeBalances AS (
    SELECT 
        t.transaction_id,
        t.transaction_name,
        t.date AS transaction_date,
        ta.account_name,
        u.user_id,
        u.user_name,
        tb.spent_amount AS transaction_amount,
        SUM(tb.earned_amount - tb.spent_amount) OVER (
            PARTITION BY ta.transaction_account_id
            ORDER BY t.date
        ) AS calculated_balance
    FROM 
        transaction_account ta
    JOIN 
        user u ON ta.user_id = u.user_id
    JOIN 
        transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id
    JOIN 
        transaction t ON tb.transaction_id = t.transaction_id
    WHERE 
        ta.account_name = 'Specific Account Name' -- Може да се замени со специфично име на акаунт
)
SELECT 
    user_id,
    user_name,
    account_name,
    transaction_id,
    transaction_name,
    transaction_amount,
    transaction_date,
    calculated_balance
FROM 
    CumulativeBalances
WHERE 
    transaction_amount > calculated_balance -- Трансакцијата го надминува пресметаниот баланс
    AND transaction_amount > 0
ORDER BY 
    user_id, account_name, transaction_date DESC;
}}}

===== Надминување на баланс на една сметка со трансакција во сегашно време
{{{#!sql
SELECT 
    u.user_id,
    u.user_name,
    ta.account_name,
    ta.balance AS current_balance,
    t.transaction_id,
    t.transaction_name,
    tb.spent_amount AS transaction_amount,
    t.date AS transaction_date
FROM 
    transaction_account ta
JOIN user u ON ta.user_id = u.user_id
JOIN transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id
JOIN transaction t ON tb.transaction_id = t.transaction_id
WHERE 
    tb.spent_amount > ta.balance -- Трансакцијата го надминува моменталниот баланс на СМЕТКАТА
    AND tb.spent_amount > 0
ORDER BY 
    u.user_id, ta.account_name, t.date DESC;
}}}

===== Листа на трансакции кои го надминале балансот на сите сметки вкупно подредени хронолошки
{{{#!sql
WITH CumulativeTotalBalances AS (
    SELECT 
        t.transaction_id,
        t.transaction_name,
        t.date AS transaction_date,
        u.user_id,
        u.user_name,
        tb.spent_amount AS transaction_amount,
        SUM(tb.earned_amount - tb.spent_amount) OVER (
            PARTITION BY u.user_id
            ORDER BY t.date
        ) AS calculated_total_balance
    FROM 
        transaction_account ta
    JOIN 
        user u ON ta.user_id = u.user_id
    JOIN 
        transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id
    JOIN 
        transaction t ON tb.transaction_id = t.transaction_id
)
SELECT 
    user_id,
    user_name,
    transaction_id,
    transaction_name,
    transaction_amount,
    transaction_date,
    calculated_total_balance
FROM 
    CumulativeTotalBalances
WHERE 
    transaction_amount > calculated_total_balance -- Трансакцијата го надминува пресметаниот вкупен баланс од сите сметки
    AND transaction_amount > 0
ORDER BY 
    user_id, transaction_date DESC;
}}}

===== Надминување на вкупниот баланс на сите сметки на корисникот во сегашно време
Овде ќе го собереме моменталниот баланс на сите сметки на корисникот и ќе провериме дали сумата на моменталната трансакцијата ја надминува таа вкупна вредност
{{{#!sql
WITH total_user_balance AS (
    SELECT 
        u.user_id,
        SUM(ta.balance) AS total_balance
    FROM 
        transaction_account ta
    JOIN user u ON ta.user_id = u.user_id
    GROUP BY 
        u.user_id
)
SELECT 
    u.user_id,
    u.user_name,
    SUM(tb.spent_amount) AS total_transaction_amount,
    tub.total_balance AS user_total_balance
FROM 
    transaction_account ta
JOIN user u ON ta.user_id = u.user_id
JOIN transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id
JOIN transaction t ON tb.transaction_id = t.transaction_id
JOIN total_user_balance tub ON u.user_id = tub.user_id
WHERE 
    t.date <= CURRENT_DATE -- Само активни трансакции до денешен датум
GROUP BY 
    u.user_id, u.user_name, tub.total_balance
HAVING 
    SUM(tb.spent_amount) > tub.total_balance -- Трансакциите го надминуваат ВКУПНИОТ моментален баланс
ORDER BY 
    u.user_id;
}}}

===== Сумарни приходи и расходи по месеци
Сумарните приходи и расходи (состојба) во целиот систем, поделени по месеци, со цел да се видат проблематичните месеци, се пресметува вкупниот приход и расход за секој месец, а разликата меѓу нив ќе ја даде состојбата
{{{#!sql
SELECT 
    TO_CHAR(t.date, 'YYYY-MM') AS month, -- Форматирање на датумот во формат "Година-Месец"
    SUM(CASE WHEN tb.earned_amount > 0 THEN tb.earned_amount ELSE 0 END) AS total_income, -- Вкупен приход
    SUM(CASE WHEN tb.spent_amount > 0 THEN tb.spent_amount ELSE 0 END) AS total_expense, -- Вкупен расход
    SUM(CASE WHEN tb.earned_amount > 0 THEN tb.earned_amount ELSE 0 END) -
    SUM(CASE WHEN tb.spent_amount > 0 THEN tb.spent_amount ELSE 0 END) AS net_balance -- Нето состојба
FROM 
    transaction_breakdown tb
JOIN 
    transaction t ON tb.transaction_id = t.transaction_id
GROUP BY 
    TO_CHAR(t.date, 'YYYY-MM') -- Групирање по месец
ORDER BY 
    month;
}}}
Проблематичен месец е месец каде што {{{net_balance}}} е негативен. Резултатите можат да се прикажат во графикон (на пример, бар график или линиски график) за подобра анализа. Може да се додаде процентуална промена меѓу месеците за да се откријат трендовите во приходите и трошоците

===== Број на трансакции што ги надминуваат приходите на сметка
{{{#!sql
WITH CumulativeBalances AS (
    SELECT 
        t.transaction_id,
        ta.account_name,
        SUM(tb.earned_amount - tb.spent_amount) OVER (
            PARTITION BY ta.transaction_account_id
            ORDER BY t.date
        ) AS calculated_balance,
        tb.spent_amount
    FROM 
        transaction_account ta
    JOIN 
        transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id
    JOIN 
        transaction t ON tb.transaction_id = t.transaction_id
)
SELECT 
    account_name, 
    COUNT(transaction_id) AS transactions_exceeding_balance
FROM 
    CumulativeBalances
WHERE 
    spent_amount > calculated_balance -- Кога потрошениот износ го надминува пресметаниот баланс
    AND spent_amount > 0
GROUP BY 
    account_name
ORDER BY 
    transactions_exceeding_balance DESC;
}}}
Ако сакаме да ги добиеме вкупните трансакции кои ги надминуваат средствата низ целиот систем, без групирање по сметки:
{{{#!sql
WITH CumulativeTotalBalances AS (
    SELECT 
        t.transaction_id,
        u.user_id,
        SUM(tb.earned_amount - tb.spent_amount) OVER (
            PARTITION BY u.user_id
            ORDER BY t.date
        ) AS calculated_total_balance,
        tb.spent_amount
    FROM 
        transaction_account ta
    JOIN 
        user u ON ta.user_id = u.user_id
    JOIN 
        transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id
    JOIN 
        transaction t ON tb.transaction_id = t.transaction_id
)
SELECT 
    COUNT(transaction_id) AS total_transactions_exceeding_balance
FROM 
    CumulativeTotalBalances
WHERE 
    spent_amount > calculated_total_balance -- Кога потрошениот износ го надминува кумулативниот баланс
    AND spent_amount > 0;
}}}

===== Сумарни податоци за тагови
Сумирани податоци каде редовите се месеци, а колоните се тагови, а во полината се покажуваат различни сумирани податоци:
 - Вкупен број на трансакции
{{{#!sql
DO $$
DECLARE
    col_list TEXT; -- Листа на тагови за изведување на пивот
    dynamic_query TEXT; -- Динамичко SQL прашање
BEGIN
    -- Чекор 1: Динамички се генерира листа на колони
    SELECT STRING_AGG(DISTINCT format('SUM(CASE WHEN tg.tag_name = %L THEN 1 ELSE 0 END) AS %I', tag_name, tag_name), ', ')
    INTO col_list
    FROM tag;

    -- Чекор 2: Се гради динамичкото прашање
    dynamic_query := format(
        'SELECT 
            TO_CHAR(t.date, ''YYYY-MM'') AS transaction_month,
            %s
        FROM 
            transaction t
        JOIN 
            tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id
        JOIN 
            tag tg ON tat.tag_id = tg.tag_id
        GROUP BY 
            transaction_month
        ORDER BY 
            transaction_month;',
        col_list
    );

    -- Чекор 3: Извршување на динамичкото прашање
    EXECUTE dynamic_query;
END $$;
}}}
 - Вкупно примени средства
{{{#!sql
DO $$
DECLARE
    col_list TEXT; -- Листа на тагови за изведување на пивот
    dynamic_query TEXT; -- Динамичко SQL прашање
BEGIN
    -- Чекор 1: Динамички се генерира листа на колони
    SELECT STRING_AGG(DISTINCT format('SUM(CASE WHEN tg.tag_name = %L THEN tb.earned_amount ELSE 0 END) AS %I', tag_name, tag_name), ', ')
    INTO col_list
    FROM tag;

    -- Чекор 2: Се гради динамичкото прашање
    dynamic_query := format(
        'SELECT 
            TO_CHAR(t.date, ''YYYY-MM'') AS transaction_month,
            %s
        FROM 
            transaction t
        JOIN 
            transaction_breakdown tb ON t.transaction_id = tb.transaction_id
        JOIN 
            tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id
        JOIN 
            tag tg ON tat.tag_id = tg.tag_id
        GROUP BY 
            transaction_month
        ORDER BY 
            transaction_month;',
        col_list
    );

    -- Чекор 3: Извршување на динамичкото прашање
    EXECUTE dynamic_query;
END $$;
}}}
 - Вкупно потрошени средства
{{{#!sql
DO $$
DECLARE
    col_list TEXT; -- Листа на тагови за изведување на пивот
    dynamic_query TEXT; -- Динамичко SQL прашање
BEGIN
    -- Чекор 1: Динамички се генерира листа на колони
    SELECT STRING_AGG(DISTINCT format('SUM(CASE WHEN tg.tag_name = %L THEN tb.spent_amount ELSE 0 END) AS %I', tag_name, tag_name), ', ')
    INTO col_list
    FROM tag;

    -- Чекор 2: Се гради динамичкото прашање
    dynamic_query := format(
        'SELECT 
            TO_CHAR(t.date, ''YYYY-MM'') AS transaction_month,
            %s
        FROM 
            transaction t
        JOIN 
            transaction_breakdown tb ON t.transaction_id = tb.transaction_id
        JOIN 
            tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id
        JOIN 
            tag tg ON tat.tag_id = tg.tag_id
        GROUP BY 
            transaction_month
        ORDER BY 
            transaction_month;',
        col_list
    );

    -- Чекор 3: Извршување на динамичкото прашање
    EXECUTE dynamic_query;
END $$;
}}}

===== Просечно трошење во последните 3 дена за цел систем
Просечно дневно трошење за последните три дена, земајќи ги предвид сите трансакции:
{{{#!sql
SELECT 
    AVG(daily_spending) AS average_spending_last_3_days
FROM (
    SELECT 
        t.date::date AS transaction_date, 
        SUM(tb.spent_amount) AS daily_spending
    FROM 
        transaction t
    JOIN 
        transaction_breakdown tb ON t.transaction_id = tb.transaction_id
    WHERE 
        t.date >= CURRENT_DATE - INTERVAL '2 DAY'
        AND t.date < CURRENT_DATE + INTERVAL '1 DAY'
    GROUP BY 
        t.date::date
) daily_totals;
}}}

===== Просечно трошење во последните 3 дена за корисник
Просечно дневно трошење за последните 3 дена за одреден корисник:
{{{#!sql
SELECT 
    AVG(daily_spending) AS average_spending_last_3_days
FROM (
    SELECT 
        t.date::date AS transaction_date, 
        SUM(tb.spent_amount) AS daily_spending
    FROM 
        transaction t
    JOIN transaction_breakdown tb ON t.transaction_id = tb.transaction_id
    JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id
    WHERE 
        ta.user_id = 101 -- ID на конкретниот корисник
        AND t.date >= CURRENT_DATE - INTERVAL '2 DAY'
        AND t.date < CURRENT_DATE + INTERVAL '1 DAY'
    GROUP BY 
        t.date::date
) daily_totals;
}}}

===== Вкупно потрошено во тековниот месец за цел систем
Вкупна сума на трошоци во тековниот месец
{{{#!sql
SELECT 
    SUM(tb.spent_amount) AS total_spent
FROM 
    transaction_breakdown tb
JOIN transaction t ON tb.transaction_id = t.transaction_id
WHERE 
    EXTRACT(MONTH FROM t.date) = EXTRACT(MONTH FROM CURRENT_DATE)
    AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE);
}}}

===== Вкупно потрошено во тековниот месец за корисник
Вкупна сума на трошоци во тековниот месец на еден корисник
{{{#!sql
SELECT 
    SUM(tb.spent_amount) 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
WHERE 
    ta.user_id = 101 -- ID на конкретниот корисник
    AND EXTRACT(MONTH FROM t.date) = EXTRACT(MONTH FROM CURRENT_DATE)
    AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE);
}}}

===== Дневен буџет до крајот на месецот за корисник
Дневен буџет базиран на вкупниот баланс на сите сметки на корисникот
{{{#!sql
SELECT 
    SUM(ta.balance) / (DATE_PART('day', DATE_TRUNC('month', CURRENT_DATE + INTERVAL '1 MONTH') - INTERVAL '1 DAY') - DATE_PART('day', CURRENT_DATE)) AS daily_budget
FROM 
    transaction_account ta
WHERE 
    ta.user_id = 101; -- ID на конкретниот корисник
}}}

===== Долг на кредитна картичка од минатиот месец за цел систем
Вкупна сума потрошена од сметките обележани како „кредитна картичка“ за минатиот месец
{{{#!sql
SELECT 
    SUM(tb.spent_amount) AS credit_card_debt
FROM 
    transaction_breakdown tb
JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id
JOIN transaction t ON tb.transaction_id = t.transaction_id
WHERE 
    (ta.account_name ILIKE '%кредитна%' OR ta.account_name ILIKE '%credit%')
    AND EXTRACT(MONTH FROM t.date) = EXTRACT(MONTH FROM CURRENT_DATE - INTERVAL '1 MONTH')
    AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE - INTERVAL '1 MONTH');
}}}

===== Долг на кредитна картичка од минатиот месец за корисник
Потрошено од сметки означени како „кредитна картичка“ за минатиот месец за еден корисник
{{{#!sql
SELECT 
    SUM(tb.spent_amount) AS credit_card_debt
FROM 
    transaction_breakdown tb
JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id
JOIN transaction t ON tb.transaction_id = t.transaction_id
WHERE 
    ta.user_id = 101 -- ID на конкретниот корисник
    AND (ta.account_name ILIKE '%кредитна%' OR ta.account_name ILIKE '%credit%')
    AND EXTRACT(MONTH FROM t.date) = EXTRACT(MONTH FROM CURRENT_DATE - INTERVAL '1 MONTH')
    AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE - INTERVAL '1 MONTH');
}}}

===== Трендови на трошење според тагови за цел систем
Трендови на трошење за секој таг во последните шест месеци
{{{#!sql
SELECT 
    tg.tag_name, 
    DATE_TRUNC('month', t.date) AS month,
    SUM(tb.spent_amount) AS total_spent
FROM 
    tag tg
JOIN tag_assigned_to_transaction tat ON tg.tag_id = tat.tag_id
JOIN transaction t ON tat.transaction_id = t.transaction_id
JOIN transaction_breakdown tb ON t.transaction_id = tb.transaction_id
WHERE 
    t.date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '6 MONTHS')
GROUP BY 
    tg.tag_name, month
ORDER BY 
    tg.tag_name, month;
}}}

===== Трендови на трошење според тагови за корисник
Сумирање на трошењата според тагови во последните 6 месеци за одреден корисник
{{{#!sql
SELECT 
    tg.tag_name, 
    DATE_TRUNC('month', t.date) AS month,
    SUM(tb.spent_amount) AS total_spent
FROM 
    tag_assigned_to_transaction tat
JOIN transaction t ON tat.transaction_id = t.transaction_id
JOIN transaction_breakdown tb ON t.transaction_id = tb.transaction_id
JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id
JOIN tag tg ON tat.tag_id = tg.tag_id
WHERE 
    ta.user_id = 101 -- ID на конкретниот корисник
    AND t.date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '6 MONTHS')
GROUP BY 
    tg.tag_name, month
ORDER BY 
    tg.tag_name, month;
}}}

===== Вкупно трошење според тагови
Вкупно трошење групирано според тагови за тековниот месец
{{{#!sql
SELECT 
    tg.tag_name,
    SUM(tb.spent_amount) AS total_spent
FROM 
    tag tg
JOIN tag_assigned_to_transaction tat ON tg.tag_id = tat.tag_id
JOIN transaction t ON tat.transaction_id = t.transaction_id
JOIN transaction_breakdown tb ON t.transaction_id = tb.transaction_id
WHERE 
    EXTRACT(MONTH FROM t.date) = EXTRACT(MONTH FROM CURRENT_DATE)
    AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE)
GROUP BY 
    tg.tag_name
ORDER BY 
    total_spent DESC;
}}}

===== Сметки со највисоко вкупно трошење во изминатата година
{{{#!sql
SELECT 
    ta.account_name, 
    SUM(tb.spent_amount) 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
WHERE 
    t.date >= NOW() - INTERVAL '1 YEAR'
GROUP BY 
    ta.account_name
ORDER BY 
    total_spent DESC
LIMIT 10;
}}}

===== Најчести времиња за трансакции
Во кој час од денот корисниците најчесто вршат трансакции
{{{#!sql
SELECT 
    EXTRACT(HOUR FROM t.date) AS transaction_hour, 
    COUNT(*) AS transaction_count
FROM 
    transaction t
GROUP BY 
    transaction_hour
ORDER BY 
    transaction_count DESC;
}}}

===== Трансакции според тагови со највисоко трошење за цел систем
{{{#!sql
SELECT 
    tg.tag_name, 
    SUM(tb.spent_amount) AS total_spent
FROM 
    transaction_breakdown tb
JOIN transaction t ON tb.transaction_id = t.transaction_id
JOIN tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id
JOIN tag tg ON tat.tag_id = tg.tag_id
GROUP BY 
    tg.tag_name
ORDER BY 
    total_spent DESC;
}}}

===== Трансакции според тагови со највисоко трошење за корисник
Сумирање на трошењата според тагови за одреден корисник
{{{#!sql
SELECT 
    tg.tag_name, 
    SUM(tb.spent_amount) 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 = 101 -- ID на конкретниот корисник
GROUP BY 
    tg.tag_name
ORDER BY 
    total_spent DESC;
}}}

===== Годишни трендови на трансакции за цел систем
Трошења според сметки за секој квартал од тековната година
{{{#!sql
SELECT 
    ta.account_name, 
    SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 1 THEN tb.spent_amount ELSE 0 END) AS q1_spent,
    SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 2 THEN tb.spent_amount ELSE 0 END) AS q2_spent,
    SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 3 THEN tb.spent_amount ELSE 0 END) AS q3_spent,
    SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 4 THEN tb.spent_amount ELSE 0 END) AS q4_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
WHERE 
    EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE)
GROUP BY 
    ta.account_name
ORDER BY 
    ta.account_name;
}}}

===== Годишни трендови на трансакции за корисник
Трошења по квартали за тековната година за одреден корисник
{{{#!sql
SELECT 
    ta.account_name, 
    SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 1 THEN tb.spent_amount ELSE 0 END) AS q1_spent,
    SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 2 THEN tb.spent_amount ELSE 0 END) AS q2_spent,
    SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 3 THEN tb.spent_amount ELSE 0 END) AS q3_spent,
    SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 4 THEN tb.spent_amount ELSE 0 END) AS q4_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
WHERE 
    ta.user_id = 101 -- ID на конкретниот корисник
    AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE)
GROUP BY 
    ta.account_name
ORDER BY 
    ta.account_name;
}}}

===== Неактивни тагови
Идентификување тагови кои не биле користени во изминатиот месец
{{{#!sql
SELECT 
    tg.tag_name
FROM 
    tag tg
LEFT JOIN tag_assigned_to_transaction tat ON tg.tag_id = tat.tag_id
LEFT JOIN transaction t ON tat.transaction_id = t.transaction_id
WHERE 
    t.date IS NULL OR t.date < NOW() - INTERVAL '1 MONTH';
}}}