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

Надминување на баланс на една сметка со трансакција во сегашно време

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

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

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

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

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

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;

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

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;

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

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

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

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

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

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

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

  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 дена за цел систем

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

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