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

Просечно трошење во последните 3 дена

Просечно дневно трошење за последните три дена, земајќи ги предвид сите трансакции:

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;

Вкупно потрошено во тековниот месец

Вкупна сума на трошоци во тековниот месец

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);

Просечно дневно трошење за тековниот месец

SELECT 
    SUM(tb.spent_amount) / EXTRACT(DAY FROM CURRENT_DATE) AS average_daily_spending
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)
    AND t.date <= CURRENT_DATE;

Дневен буџет до крајот на месецот

Дневен буџет за преостанатите денови од месецот врз основа на вкупниот баланс на сите сметки

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;

Долг на кредитна картичка од минатиот месец

Вкупна сума потрошена од сметките обележани како „кредитна картичка“ за минатиот месец

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');

Трендови на трошење според тагови

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

SELECT 
    tg.tag_name, 
    DATE_TRUNC('month', t.date) AS month,
    SUM(tb.spent_amount) AS total_spent
FROM 
    tag tg
JOIN transaction t ON tg.tag_id = t.tag_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;

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

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

SELECT 
    tg.tag_name,
    SUM(tb.spent_amount) AS total_spent
FROM 
    tag tg
JOIN transaction t ON tg.tag_id = t.tag_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;

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

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;

Најчести времиња за трансакции

Во кој час од денот корисниците најчесто вршат трансакции

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;

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

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 tg ON t.tag_id = tg.tag_id
GROUP BY 
    tg.tag_name
ORDER BY 
    total_spent DESC;

Годишни трендови на трансакции

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

SELECT 
    ta.account_name, 
    CASE WHEN EXTRACT(QUARTER FROM t.date) = 1 THEN SUM(tb.spent_amount) ELSE 0 END AS q1_spent,
    CASE WHEN EXTRACT(QUARTER FROM t.date) = 2 THEN SUM(tb.spent_amount) ELSE 0 END AS q2_spent,
    CASE WHEN EXTRACT(QUARTER FROM t.date) = 3 THEN SUM(tb.spent_amount) ELSE 0 END AS q3_spent,
    CASE WHEN EXTRACT(QUARTER FROM t.date) = 4 THEN SUM(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 NOW())
GROUP BY 
    ta.account_name, EXTRACT(QUARTER FROM t.date);

Неактивни тагови

Идентификување тагови кои не биле користени во изминатиот месец

SELECT 
    tg.tag_name
FROM 
    tag tg
LEFT JOIN transaction t ON tg.tag_id = t.tag_id
WHERE 
    t.date IS NULL OR t.date < NOW() - INTERVAL '1 MONTH';