Напредни извештаи од базата (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;

Надминување на вкупниот баланс на сите сметки на корисникот

Овде ќе го собереме балансот на сите сметки на корисникот и ќе провериме дали сумата на трансакцијата ја надминува таа вкупна вредност

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;

Сумарни приходи и расходи по месеци

Сумарните приходи и расходи (состојба) во целиот систем, поделени по месеци, со цел да се видат проблематичните месеци, се пресметува вкупниот приход и расход за секој месец, а разликата меѓу нив ќе ја даде состојбата

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 е негативен. Резултатите можат да се прикажат во графикон (на пример, бар график или линиски график) за подобра анализа. Може да се додаде процентуална промена меѓу месеците за да се откријат трендовите во приходите и трошоците

Број на трансакции што ги надминуваат приходите на сметка

SELECT 
    ta.account_name, 
    COUNT(tb.transaction_id) AS transactions_exceeding_balance
FROM 
    transaction_account ta
JOIN 
    transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id
WHERE 
    tb.spent_amount > ta.balance -- Кога потрошеното е поголемо од состојбата на сметката
GROUP BY 
    ta.account_name
ORDER BY 
    transactions_exceeding_balance DESC;

Ако сакаме да ги добиеме вкупните трансакции кои ги надминуваат средствата низ целиот систем, без групирање по сметки:

SELECT 
    COUNT(tb.transaction_id) AS total_transactions_exceeding_balance
FROM 
    transaction_account ta
JOIN 
    transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id
WHERE 
    tb.spent_amount > ta.balance;

Сумарни податоци за тагови

Сумирани податоци каде редовите се месеци, а колоните се тагови, а во полината се покажуваат различни сумирани податоци:

• Вкупен број на трансакции

  SELECT 
      TO_CHAR(t.date, 'YYYY-MM') AS transaction_month, 
      tg.tag_name, 
      COUNT(t.transaction_id) AS total_transactions
  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, tg.tag_name
  ORDER BY 
      transaction_month, tg.tag_name;
  

• Вкупно примени средства

  SELECT 
      TO_CHAR(t.date, 'YYYY-MM') AS transaction_month, 
      tg.tag_name, 
      SUM(tb.earned_amount) AS total_earned
  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, tg.tag_name
  ORDER BY 
      transaction_month, tg.tag_name;
  

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

  SELECT 
      TO_CHAR(t.date, 'YYYY-MM') AS transaction_month, 
      tg.tag_name, 
      SUM(tb.spent_amount) AS total_spent
  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, tg.tag_name
  ORDER BY 
      transaction_month, tg.tag_name;
  

Просечно трошење во последните 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;

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

Просечно дневно трошење за последните 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
    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;

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

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

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

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

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

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 на конкретниот корисник

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

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

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

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

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

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 месеци за одреден корисник

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;

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

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

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;

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

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

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

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

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;

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

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

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;

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

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

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;

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

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

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