Changes between Version 8 and Version 9 of AdvancedReports


Ignore:
Timestamp:
12/28/25 22:45:49 (5 days ago)
Author:
211101
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • AdvancedReports

    v8 v9  
    11= Напредни извештаи од базата (SQL и складирани процедури)
    22
    3 ===== Листа на трансакции кои го надминале балансот на една сметка подредени хронолошки
    4 {{{#!sql
    5 CREATE OR REPLACE FUNCTION get_transactions_exceeding_account_balance(
    6     p_account_name TEXT
    7 )
    8 RETURNS TABLE (
    9     user_id INT,
    10     user_name TEXT,
    11     account_name TEXT,
    12     transaction_id INT,
    13     transaction_name TEXT,
    14     transaction_amount NUMERIC,
    15     transaction_date TIMESTAMP,
    16     calculated_balance NUMERIC
    17 )
    18 LANGUAGE plpgsql
    19 AS $$
    20 BEGIN
    21     RETURN QUERY
    22     WITH cumulative_balances AS (
    23         SELECT
    24             u.user_id,
    25             u.user_name,
    26             ta.account_name,
    27             t.transaction_id,
    28             t.transaction_name,
    29             tb.spent_amount AS transaction_amount,
    30             t.date AS transaction_date,
    31             SUM(tb.earned_amount - tb.spent_amount)
    32                 OVER (PARTITION BY ta.transaction_account_id ORDER BY t.date)
    33                 AS calculated_balance
    34         FROM transaction_account ta
    35         JOIN "user" u ON ta.user_id = u.user_id
    36         JOIN transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id
    37         JOIN transaction t ON tb.transaction_id = t.transaction_id
    38         WHERE ta.account_name = p_account_name
    39     )
    40     SELECT *
    41     FROM cumulative_balances
    42     WHERE transaction_amount > calculated_balance
    43       AND transaction_amount > 0
    44     ORDER BY transaction_date DESC;
    45 END;
    46 $$;
    47 
    48 }}}
    49 
    50 ===== Надминување на баланс на една сметка со трансакција во сегашно време
    51 {{{#!sql
    52 CREATE OR REPLACE FUNCTION get_current_account_overdrafts()
    53 RETURNS TABLE (
    54     user_id INT,
    55     user_name TEXT,
    56     account_name TEXT,
    57     current_balance NUMERIC,
    58     transaction_id INT,
    59     transaction_name TEXT,
    60     transaction_amount NUMERIC,
    61     transaction_date TIMESTAMP
    62 )
    63 LANGUAGE plpgsql
    64 AS $$
    65 BEGIN
    66     RETURN QUERY
    67     SELECT
    68         u.user_id,
    69         u.user_name,
    70         ta.account_name,
    71         ta.balance,
    72         t.transaction_id,
    73         t.transaction_name,
    74         tb.spent_amount,
    75         t.date
    76     FROM transaction_account ta
    77     JOIN "user" u ON ta.user_id = u.user_id
    78     JOIN transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id
    79     JOIN transaction t ON tb.transaction_id = t.transaction_id
    80     WHERE tb.spent_amount > ta.balance
    81       AND tb.spent_amount > 0
    82     ORDER BY t.date DESC;
    83 END;
    84 $$;
    85 }}}
    86 
    87 ===== Листа на трансакции кои го надминале балансот на сите сметки вкупно подредени хронолошки
    88 {{{#!sql
    89 CREATE OR REPLACE FUNCTION get_transactions_exceeding_user_total_balance()
    90 RETURNS TABLE (
    91     user_id INT,
    92     user_name TEXT,
    93     transaction_id INT,
    94     transaction_name TEXT,
    95     transaction_amount NUMERIC,
    96     transaction_date TIMESTAMP,
    97     calculated_total_balance NUMERIC
    98 )
    99 LANGUAGE plpgsql
    100 AS $$
    101 BEGIN
    102     RETURN QUERY
    103     WITH cumulative_totals AS (
    104         SELECT
    105             u.user_id,
    106             u.user_name,
    107             t.transaction_id,
    108             t.transaction_name,
    109             tb.spent_amount AS transaction_amount,
    110             t.date AS transaction_date,
    111             SUM(tb.earned_amount - tb.spent_amount)
    112                 OVER (PARTITION BY u.user_id ORDER BY t.date)
    113                 AS calculated_total_balance
    114         FROM transaction_account ta
    115         JOIN "user" u ON ta.user_id = u.user_id
    116         JOIN transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id
    117         JOIN transaction t ON tb.transaction_id = t.transaction_id
    118     )
    119     SELECT *
    120     FROM cumulative_totals
    121     WHERE transaction_amount > calculated_total_balance
    122       AND transaction_amount > 0
    123     ORDER BY user_id, transaction_date DESC;
    124 END;
    125 $$;
    126 }}}
    127 
    128 ===== Надминување на вкупниот баланс на сите сметки на корисникот во сегашно време
    129 Овде ќе го собереме моменталниот баланс на сите сметки на корисникот и ќе провериме дали сумата на моменталната трансакцијата ја надминува таа вкупна вредност
    130 {{{#!sql
    131 WITH total_user_balance AS (
    132     SELECT
    133         u.user_id,
    134         SUM(ta.balance) AS total_balance
    135     FROM
    136         transaction_account ta
    137     JOIN user u ON ta.user_id = u.user_id
    138     GROUP BY
    139         u.user_id
    140 )
    141 SELECT
    142     u.user_id,
    143     u.user_name,
    144     SUM(tb.spent_amount) AS total_transaction_amount,
    145     tub.total_balance AS user_total_balance
    146 FROM
    147     transaction_account ta
    148 JOIN user u ON ta.user_id = u.user_id
    149 JOIN transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id
    150 JOIN transaction t ON tb.transaction_id = t.transaction_id
    151 JOIN total_user_balance tub ON u.user_id = tub.user_id
    152 WHERE
    153     t.date <= CURRENT_DATE -- Само активни трансакции до денешен датум
    154 GROUP BY
    155     u.user_id, u.user_name, tub.total_balance
    156 HAVING
    157     SUM(tb.spent_amount) > tub.total_balance -- Трансакциите го надминуваат ВКУПНИОТ моментален баланс
    158 ORDER BY
    159     u.user_id;
    160 }}}
    161 
    162 ===== Сумарни приходи и расходи по месеци
    163 Сумарните приходи и расходи (состојба) во целиот систем, поделени по месеци, со цел да се видат проблематичните месеци, се пресметува вкупниот приход и расход за секој месец, а разликата меѓу нив ќе ја даде состојбата
    164 {{{#!sql
    165 CREATE OR REPLACE FUNCTION get_monthly_system_balance()
    166 RETURNS TABLE (
    167     month TEXT,
    168     total_income NUMERIC,
    169     total_expense NUMERIC,
    170     net_balance NUMERIC
    171 )
    172 LANGUAGE plpgsql
    173 AS $$
    174 BEGIN
    175     RETURN QUERY
    176     SELECT
    177         TO_CHAR(t.date, 'YYYY-MM'),
    178         SUM(tb.earned_amount),
    179         SUM(tb.spent_amount),
    180         SUM(tb.earned_amount) - SUM(tb.spent_amount)
    181     FROM transaction t
    182     JOIN transaction_breakdown tb ON t.transaction_id = tb.transaction_id
    183     GROUP BY TO_CHAR(t.date, 'YYYY-MM')
    184     ORDER BY month;
    185 END;
    186 $$;
    187 }}}
    188 Проблематичен месец е месец каде што {{{net_balance}}} е негативен. Резултатите можат да се прикажат во графикон (на пример, бар график или линиски график) за подобра анализа. Може да се додаде процентуална промена меѓу месеците за да се откријат трендовите во приходите и трошоците
    189 
    190 ===== Број на трансакции што ги надминуваат приходите на сметка
    191 {{{#!sql
    192 WITH CumulativeBalances AS (
    193     SELECT
    194         t.transaction_id,
    195         ta.account_name,
    196         SUM(tb.earned_amount - tb.spent_amount) OVER (
    197             PARTITION BY ta.transaction_account_id
    198             ORDER BY t.date
    199         ) AS calculated_balance,
    200         tb.spent_amount
    201     FROM
    202         transaction_account ta
    203     JOIN
    204         transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id
    205     JOIN
    206         transaction t ON tb.transaction_id = t.transaction_id
    207 )
    208 SELECT
    209     account_name,
    210     COUNT(transaction_id) AS transactions_exceeding_balance
    211 FROM
    212     CumulativeBalances
    213 WHERE
    214     spent_amount > calculated_balance -- Кога потрошениот износ го надминува пресметаниот баланс
    215     AND spent_amount > 0
    216 GROUP BY
    217     account_name
    218 ORDER BY
    219     transactions_exceeding_balance DESC;
    220 }}}
    221 Ако сакаме да ги добиеме вкупните трансакции кои ги надминуваат средствата низ целиот систем, без групирање по сметки:
    222 {{{#!sql
    223 WITH CumulativeTotalBalances AS (
    224     SELECT
    225         t.transaction_id,
    226         u.user_id,
    227         SUM(tb.earned_amount - tb.spent_amount) OVER (
    228             PARTITION BY u.user_id
    229             ORDER BY t.date
    230         ) AS calculated_total_balance,
    231         tb.spent_amount
    232     FROM
    233         transaction_account ta
    234     JOIN
    235         user u ON ta.user_id = u.user_id
    236     JOIN
    237         transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id
    238     JOIN
    239         transaction t ON tb.transaction_id = t.transaction_id
    240 )
    241 SELECT
    242     COUNT(transaction_id) AS total_transactions_exceeding_balance
    243 FROM
    244     CumulativeTotalBalances
    245 WHERE
    246     spent_amount > calculated_total_balance -- Кога потрошениот износ го надминува кумулативниот баланс
    247     AND spent_amount > 0;
    248 }}}
    249 
    250 ===== Сумарни податоци за тагови
    251 Сумирани податоци каде редовите се месеци, а колоните се тагови, а во полината се покажуваат различни сумирани податоци:
    252  - Вкупен број на трансакции
    253 {{{#!sql
    254 DO $$
    255 DECLARE
    256     col_list TEXT; -- Листа на тагови за изведување на пивот
    257     dynamic_query TEXT; -- Динамичко SQL прашање
    258 BEGIN
    259     -- Чекор 1: Динамички се генерира листа на колони
    260     SELECT STRING_AGG(DISTINCT format('SUM(CASE WHEN tg.tag_name = %L THEN 1 ELSE 0 END) AS %I', tag_name, tag_name), ', ')
    261     INTO col_list
    262     FROM tag;
    263 
    264     -- Чекор 2: Се гради динамичкото прашање
    265     dynamic_query := format(
    266         'SELECT
    267             TO_CHAR(t.date, ''YYYY-MM'') AS transaction_month,
    268             %s
    269         FROM
    270             transaction t
    271         JOIN
    272             tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id
    273         JOIN
    274             tag tg ON tat.tag_id = tg.tag_id
    275         GROUP BY
    276             transaction_month
    277         ORDER BY
    278             transaction_month;',
    279         col_list
    280     );
    281 
    282     -- Чекор 3: Извршување на динамичкото прашање
    283     EXECUTE dynamic_query;
    284 END $$;
    285 }}}
    286  - Вкупно примени средства
    287 {{{#!sql
    288 DO $$
    289 DECLARE
    290     col_list TEXT; -- Листа на тагови за изведување на пивот
    291     dynamic_query TEXT; -- Динамичко SQL прашање
    292 BEGIN
    293     -- Чекор 1: Динамички се генерира листа на колони
    294     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), ', ')
    295     INTO col_list
    296     FROM tag;
    297 
    298     -- Чекор 2: Се гради динамичкото прашање
    299     dynamic_query := format(
    300         'SELECT
    301             TO_CHAR(t.date, ''YYYY-MM'') AS transaction_month,
    302             %s
    303         FROM
    304             transaction t
    305         JOIN
    306             transaction_breakdown tb ON t.transaction_id = tb.transaction_id
    307         JOIN
    308             tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id
    309         JOIN
    310             tag tg ON tat.tag_id = tg.tag_id
    311         GROUP BY
    312             transaction_month
    313         ORDER BY
    314             transaction_month;',
    315         col_list
    316     );
    317 
    318     -- Чекор 3: Извршување на динамичкото прашање
    319     EXECUTE dynamic_query;
    320 END $$;
    321 }}}
    322  - Вкупно потрошени средства
    323 {{{#!sql
    324 DO $$
    325 DECLARE
    326     col_list TEXT; -- Листа на тагови за изведување на пивот
    327     dynamic_query TEXT; -- Динамичко SQL прашање
    328 BEGIN
    329     -- Чекор 1: Динамички се генерира листа на колони
    330     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), ', ')
    331     INTO col_list
    332     FROM tag;
    333 
    334     -- Чекор 2: Се гради динамичкото прашање
    335     dynamic_query := format(
    336         'SELECT
    337             TO_CHAR(t.date, ''YYYY-MM'') AS transaction_month,
    338             %s
    339         FROM
    340             transaction t
    341         JOIN
    342             transaction_breakdown tb ON t.transaction_id = tb.transaction_id
    343         JOIN
    344             tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id
    345         JOIN
    346             tag tg ON tat.tag_id = tg.tag_id
    347         GROUP BY
    348             transaction_month
    349         ORDER BY
    350             transaction_month;',
    351         col_list
    352     );
    353 
    354     -- Чекор 3: Извршување на динамичкото прашање
    355     EXECUTE dynamic_query;
    356 END $$;
    357 }}}
    358 
    359 ===== Просечно трошење во последните 3 дена за цел систем
    360 Просечно дневно трошење за последните три дена, земајќи ги предвид сите трансакции:
    361 {{{#!sql
    362 SELECT
    363     AVG(daily_spending) AS average_spending_last_3_days
    364 FROM (
    365     SELECT
    366         t.date::date AS transaction_date,
    367         SUM(tb.spent_amount) AS daily_spending
    368     FROM
    369         transaction t
    370     JOIN
    371         transaction_breakdown tb ON t.transaction_id = tb.transaction_id
    372     WHERE
    373         t.date >= CURRENT_DATE - INTERVAL '2 DAY'
    374         AND t.date < CURRENT_DATE + INTERVAL '1 DAY'
    375     GROUP BY
    376         t.date::date
    377 ) daily_totals;
    378 }}}
    379 
    380 ===== Просечно трошење во последните 3 дена за корисник
    381 Просечно дневно трошење за последните 3 дена за одреден корисник:
    382 {{{#!sql
    383 SELECT
    384     AVG(daily_spending) AS average_spending_last_3_days
    385 FROM (
    386     SELECT
    387         t.date::date AS transaction_date,
    388         SUM(tb.spent_amount) AS daily_spending
    389     FROM
    390         transaction t
    391     JOIN transaction_breakdown tb ON t.transaction_id = tb.transaction_id
    392     JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id
    393     WHERE
    394         ta.user_id = 101 -- ID на конкретниот корисник
    395         AND t.date >= CURRENT_DATE - INTERVAL '2 DAY'
    396         AND t.date < CURRENT_DATE + INTERVAL '1 DAY'
    397     GROUP BY
    398         t.date::date
    399 ) daily_totals;
    400 }}}
    401 
    402 ===== Вкупно потрошено во тековниот месец за цел систем
    403 Вкупна сума на трошоци во тековниот месец
    404 {{{#!sql
    405 SELECT
    406     SUM(tb.spent_amount) AS total_spent
    407 FROM
    408     transaction_breakdown tb
    409 JOIN transaction t ON tb.transaction_id = t.transaction_id
    410 WHERE
    411     EXTRACT(MONTH FROM t.date) = EXTRACT(MONTH FROM CURRENT_DATE)
    412     AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE);
    413 }}}
    414 
    415 ===== Вкупно потрошено во тековниот месец за корисник
    416 Вкупна сума на трошоци во тековниот месец на еден корисник
    417 {{{#!sql
    418 SELECT
    419     SUM(tb.spent_amount) AS total_spent
    420 FROM
    421     transaction_breakdown tb
    422 JOIN transaction t ON tb.transaction_id = t.transaction_id
    423 JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id
    424 WHERE
    425     ta.user_id = 101 -- ID на конкретниот корисник
    426     AND EXTRACT(MONTH FROM t.date) = EXTRACT(MONTH FROM CURRENT_DATE)
    427     AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE);
    428 }}}
    429 
    430 ===== Дневен буџет до крајот на месецот за корисник
    431 Дневен буџет базиран на вкупниот баланс на сите сметки на корисникот
    432 {{{#!sql
    433 SELECT
    434     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
    435 FROM
    436     transaction_account ta
    437 WHERE
    438     ta.user_id = 101; -- ID на конкретниот корисник
    439 }}}
    440 
    441 ===== Долг на кредитна картичка од минатиот месец за цел систем
    442 Вкупна сума потрошена од сметките обележани како „кредитна картичка“ за минатиот месец
    443 {{{#!sql
    444 SELECT
    445     SUM(tb.spent_amount) AS credit_card_debt
    446 FROM
    447     transaction_breakdown tb
    448 JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id
    449 JOIN transaction t ON tb.transaction_id = t.transaction_id
    450 WHERE
    451     (ta.account_name ILIKE '%кредитна%' OR ta.account_name ILIKE '%credit%')
    452     AND EXTRACT(MONTH FROM t.date) = EXTRACT(MONTH FROM CURRENT_DATE - INTERVAL '1 MONTH')
    453     AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE - INTERVAL '1 MONTH');
    454 }}}
    455 
    456 ===== Долг на кредитна картичка од минатиот месец за корисник
    457 Потрошено од сметки означени како „кредитна картичка“ за минатиот месец за еден корисник
    458 {{{#!sql
    459 SELECT
    460     SUM(tb.spent_amount) AS credit_card_debt
    461 FROM
    462     transaction_breakdown tb
    463 JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id
    464 JOIN transaction t ON tb.transaction_id = t.transaction_id
    465 WHERE
    466     ta.user_id = 101 -- ID на конкретниот корисник
    467     AND (ta.account_name ILIKE '%кредитна%' OR ta.account_name ILIKE '%credit%')
    468     AND EXTRACT(MONTH FROM t.date) = EXTRACT(MONTH FROM CURRENT_DATE - INTERVAL '1 MONTH')
    469     AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE - INTERVAL '1 MONTH');
    470 }}}
    471 
    472 ===== Трендови на трошење според тагови за цел систем
    473 Трендови на трошење за секој таг во последните шест месеци
    474 {{{#!sql
    475 SELECT
    476     tg.tag_name,
    477     DATE_TRUNC('month', t.date) AS month,
    478     SUM(tb.spent_amount) AS total_spent
    479 FROM
    480     tag tg
    481 JOIN tag_assigned_to_transaction tat ON tg.tag_id = tat.tag_id
    482 JOIN transaction t ON tat.transaction_id = t.transaction_id
    483 JOIN transaction_breakdown tb ON t.transaction_id = tb.transaction_id
    484 WHERE
    485     t.date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '6 MONTHS')
    486 GROUP BY
    487     tg.tag_name, month
    488 ORDER BY
    489     tg.tag_name, month;
    490 }}}
    491 
    492 ===== Трендови на трошење според тагови за корисник
    493 Сумирање на трошењата според тагови во последните 6 месеци за одреден корисник
    494 {{{#!sql
    495 SELECT
    496     tg.tag_name,
    497     DATE_TRUNC('month', t.date) AS month,
    498     SUM(tb.spent_amount) AS total_spent
    499 FROM
    500     tag_assigned_to_transaction tat
    501 JOIN transaction t ON tat.transaction_id = t.transaction_id
    502 JOIN transaction_breakdown tb ON t.transaction_id = tb.transaction_id
    503 JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id
    504 JOIN tag tg ON tat.tag_id = tg.tag_id
    505 WHERE
    506     ta.user_id = 101 -- ID на конкретниот корисник
    507     AND t.date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '6 MONTHS')
    508 GROUP BY
    509     tg.tag_name, month
    510 ORDER BY
    511     tg.tag_name, month;
    512 }}}
    513 
    514 ===== Вкупно трошење според тагови
    515 Вкупно трошење групирано според тагови за тековниот месец
    516 {{{#!sql
    517 SELECT
    518     tg.tag_name,
    519     SUM(tb.spent_amount) AS total_spent
    520 FROM
    521     tag tg
    522 JOIN tag_assigned_to_transaction tat ON tg.tag_id = tat.tag_id
    523 JOIN transaction t ON tat.transaction_id = t.transaction_id
    524 JOIN transaction_breakdown tb ON t.transaction_id = tb.transaction_id
    525 WHERE
    526     EXTRACT(MONTH FROM t.date) = EXTRACT(MONTH FROM CURRENT_DATE)
    527     AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE)
    528 GROUP BY
    529     tg.tag_name
    530 ORDER BY
    531     total_spent DESC;
    532 }}}
    533 
    534 ===== Сметки со највисоко вкупно трошење во изминатата година
    535 {{{#!sql
    536 SELECT
    537     ta.account_name,
    538     SUM(tb.spent_amount) AS total_spent
    539 FROM
    540     transaction_breakdown tb
    541 JOIN transaction t ON tb.transaction_id = t.transaction_id
    542 JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id
    543 WHERE
    544     t.date >= NOW() - INTERVAL '1 YEAR'
    545 GROUP BY
    546     ta.account_name
    547 ORDER BY
    548     total_spent DESC
    549 LIMIT 10;
    550 }}}
    551 
    552 ===== Најчести времиња за трансакции
    553 Во кој час од денот корисниците најчесто вршат трансакции
    554 {{{#!sql
    555 SELECT
    556     EXTRACT(HOUR FROM t.date) AS transaction_hour,
    557     COUNT(*) AS transaction_count
    558 FROM
    559     transaction t
    560 GROUP BY
    561     transaction_hour
    562 ORDER BY
    563     transaction_count DESC;
    564 }}}
    565 
    566 ===== Трансакции според тагови со највисоко трошење за цел систем
    567 {{{#!sql
    568 SELECT
    569     tg.tag_name,
    570     SUM(tb.spent_amount) AS total_spent
    571 FROM
    572     transaction_breakdown tb
    573 JOIN transaction t ON tb.transaction_id = t.transaction_id
    574 JOIN tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id
    575 JOIN tag tg ON tat.tag_id = tg.tag_id
    576 GROUP BY
    577     tg.tag_name
    578 ORDER BY
    579     total_spent DESC;
    580 }}}
    581 
    582 ===== Трансакции според тагови со највисоко трошење за корисник
    583 Сумирање на трошењата според тагови за одреден корисник
    584 {{{#!sql
    585 SELECT
    586     tg.tag_name,
    587     SUM(tb.spent_amount) AS total_spent
    588 FROM
    589     transaction_breakdown tb
    590 JOIN transaction t ON tb.transaction_id = t.transaction_id
    591 JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id
    592 JOIN tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id
    593 JOIN tag tg ON tat.tag_id = tg.tag_id
    594 WHERE
    595     ta.user_id = 101 -- ID на конкретниот корисник
    596 GROUP BY
    597     tg.tag_name
    598 ORDER BY
    599     total_spent DESC;
    600 }}}
    601 
    602 ===== Годишни трендови на трансакции за цел систем
    603 Трошења според сметки за секој квартал од тековната година
    604 {{{#!sql
    605 SELECT
    606     ta.account_name,
    607     SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 1 THEN tb.spent_amount ELSE 0 END) AS q1_spent,
    608     SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 2 THEN tb.spent_amount ELSE 0 END) AS q2_spent,
    609     SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 3 THEN tb.spent_amount ELSE 0 END) AS q3_spent,
    610     SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 4 THEN tb.spent_amount ELSE 0 END) AS q4_spent
    611 FROM
    612     transaction_breakdown tb
    613 JOIN transaction t ON tb.transaction_id = t.transaction_id
    614 JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id
    615 WHERE
    616     EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE)
    617 GROUP BY
    618     ta.account_name
    619 ORDER BY
    620     ta.account_name;
    621 }}}
    622 
    623 ===== Годишни трендови на трансакции за корисник
    624 Трошења по квартали за тековната година за одреден корисник
    625 {{{#!sql
    626 SELECT
    627     ta.account_name,
    628     SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 1 THEN tb.spent_amount ELSE 0 END) AS q1_spent,
    629     SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 2 THEN tb.spent_amount ELSE 0 END) AS q2_spent,
    630     SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 3 THEN tb.spent_amount ELSE 0 END) AS q3_spent,
    631     SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 4 THEN tb.spent_amount ELSE 0 END) AS q4_spent
    632 FROM
    633     transaction_breakdown tb
    634 JOIN transaction t ON tb.transaction_id = t.transaction_id
    635 JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id
    636 WHERE
    637     ta.user_id = 101 -- ID на конкретниот корисник
    638     AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE)
    639 GROUP BY
    640     ta.account_name
    641 ORDER BY
    642     ta.account_name;
    643 }}}
    644 
    645 ===== Неактивни тагови
    646 Идентификување тагови кои не биле користени во изминатиот месец
    647 {{{#!sql
    648 SELECT
    649     tg.tag_name
    650 FROM
    651     tag tg
    652 LEFT JOIN tag_assigned_to_transaction tat ON tg.tag_id = tat.tag_id
    653 LEFT JOIN transaction t ON tat.transaction_id = t.transaction_id
    654 WHERE
    655     t.date IS NULL OR t.date < NOW() - INTERVAL '1 MONTH';
    656 }}}
    657 
    658 ==
    659 
    660 [wiki:= Напредни извештаи од базата (SQL и складирани процедури)
    661 
    662 ===== Листа на трансакции кои го надминале балансот на една сметка подредени хронолошки
    663 {{{#!sql
    664 CREATE OR REPLACE FUNCTION get_transactions_exceeding_account_balance(
    665     p_account_name TEXT
    666 )
    667 RETURNS TABLE (
    668     user_id INT,
    669     user_name TEXT,
    670     account_name TEXT,
    671     transaction_id INT,
    672     transaction_name TEXT,
    673     transaction_amount NUMERIC,
    674     transaction_date TIMESTAMP,
    675     calculated_balance NUMERIC
    676 )
    677 LANGUAGE plpgsql
    678 AS $$
    679 BEGIN
    680     RETURN QUERY
    681     WITH cumulative_balances AS (
    682         SELECT
    683             u.user_id,
    684             u.user_name,
    685             ta.account_name,
    686             t.transaction_id,
    687             t.transaction_name,
    688             tb.spent_amount AS transaction_amount,
    689             t.date AS transaction_date,
    690             SUM(tb.earned_amount - tb.spent_amount)
    691                 OVER (PARTITION BY ta.transaction_account_id ORDER BY t.date)
    692                 AS calculated_balance
    693         FROM transaction_account ta
    694         JOIN "user" u ON ta.user_id = u.user_id
    695         JOIN transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id
    696         JOIN transaction t ON tb.transaction_id = t.transaction_id
    697         WHERE ta.account_name = p_account_name
    698     )
    699     SELECT *
    700     FROM cumulative_balances
    701     WHERE transaction_amount > calculated_balance
    702       AND transaction_amount > 0
    703     ORDER BY transaction_date DESC;
    704 END;
    705 $$;
    706 
    707 }}}
    708 
    709 ===== Надминување на баланс на една сметка со трансакција во сегашно време
    710 {{{#!sql
    711 CREATE OR REPLACE FUNCTION get_current_account_overdrafts()
    712 RETURNS TABLE (
    713     user_id INT,
    714     user_name TEXT,
    715     account_name TEXT,
    716     current_balance NUMERIC,
    717     transaction_id INT,
    718     transaction_name TEXT,
    719     transaction_amount NUMERIC,
    720     transaction_date TIMESTAMP
    721 )
    722 LANGUAGE plpgsql
    723 AS $$
    724 BEGIN
    725     RETURN QUERY
    726     SELECT
    727         u.user_id,
    728         u.user_name,
    729         ta.account_name,
    730         ta.balance,
    731         t.transaction_id,
    732         t.transaction_name,
    733         tb.spent_amount,
    734         t.date
    735     FROM transaction_account ta
    736     JOIN "user" u ON ta.user_id = u.user_id
    737     JOIN transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id
    738     JOIN transaction t ON tb.transaction_id = t.transaction_id
    739     WHERE tb.spent_amount > ta.balance
    740       AND tb.spent_amount > 0
    741     ORDER BY t.date DESC;
    742 END;
    743 $$;
    744 }}}
    745 
    746 ===== Листа на трансакции кои го надминале балансот на сите сметки вкупно подредени хронолошки
    747 {{{#!sql
    748 CREATE OR REPLACE FUNCTION get_transactions_exceeding_user_total_balance()
    749 RETURNS TABLE (
    750     user_id INT,
    751     user_name TEXT,
    752     transaction_id INT,
    753     transaction_name TEXT,
    754     transaction_amount NUMERIC,
    755     transaction_date TIMESTAMP,
    756     calculated_total_balance NUMERIC
    757 )
    758 LANGUAGE plpgsql
    759 AS $$
    760 BEGIN
    761     RETURN QUERY
    762     WITH cumulative_totals AS (
    763         SELECT
    764             u.user_id,
    765             u.user_name,
    766             t.transaction_id,
    767             t.transaction_name,
    768             tb.spent_amount AS transaction_amount,
    769             t.date AS transaction_date,
    770             SUM(tb.earned_amount - tb.spent_amount)
    771                 OVER (PARTITION BY u.user_id ORDER BY t.date)
    772                 AS calculated_total_balance
    773         FROM transaction_account ta
    774         JOIN "user" u ON ta.user_id = u.user_id
    775         JOIN transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id
    776         JOIN transaction t ON tb.transaction_id = t.transaction_id
    777     )
    778     SELECT *
    779     FROM cumulative_totals
    780     WHERE transaction_amount > calculated_total_balance
    781       AND transaction_amount > 0
    782     ORDER BY user_id, transaction_date DESC;
    783 END;
    784 $$;
    785 }}}
    786 
    787 ===== Надминување на вкупниот баланс на сите сметки на корисникот во сегашно време
    788 Овде ќе го собереме моменталниот баланс на сите сметки на корисникот и ќе провериме дали сумата на моменталната трансакцијата ја надминува таа вкупна вредност
    789 {{{#!sql
    790 WITH total_user_balance AS (
    791     SELECT
    792         u.user_id,
    793         SUM(ta.balance) AS total_balance
    794     FROM
    795         transaction_account ta
    796     JOIN user u ON ta.user_id = u.user_id
    797     GROUP BY
    798         u.user_id
    799 )
    800 SELECT
    801     u.user_id,
    802     u.user_name,
    803     SUM(tb.spent_amount) AS total_transaction_amount,
    804     tub.total_balance AS user_total_balance
    805 FROM
    806     transaction_account ta
    807 JOIN user u ON ta.user_id = u.user_id
    808 JOIN transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id
    809 JOIN transaction t ON tb.transaction_id = t.transaction_id
    810 JOIN total_user_balance tub ON u.user_id = tub.user_id
    811 WHERE
    812     t.date <= CURRENT_DATE -- Само активни трансакции до денешен датум
    813 GROUP BY
    814     u.user_id, u.user_name, tub.total_balance
    815 HAVING
    816     SUM(tb.spent_amount) > tub.total_balance -- Трансакциите го надминуваат ВКУПНИОТ моментален баланс
    817 ORDER BY
    818     u.user_id;
    819 }}}
    820 
    821 ===== Сумарни приходи и расходи по месеци
    822 Сумарните приходи и расходи (состојба) во целиот систем, поделени по месеци, со цел да се видат проблематичните месеци, се пресметува вкупниот приход и расход за секој месец, а разликата меѓу нив ќе ја даде состојбата
    823 {{{#!sql
    824 CREATE OR REPLACE FUNCTION get_monthly_system_balance()
    825 RETURNS TABLE (
    826     month TEXT,
    827     total_income NUMERIC,
    828     total_expense NUMERIC,
    829     net_balance NUMERIC
    830 )
    831 LANGUAGE plpgsql
    832 AS $$
    833 BEGIN
    834     RETURN QUERY
    835     SELECT
    836         TO_CHAR(t.date, 'YYYY-MM'),
    837         SUM(tb.earned_amount),
    838         SUM(tb.spent_amount),
    839         SUM(tb.earned_amount) - SUM(tb.spent_amount)
    840     FROM transaction t
    841     JOIN transaction_breakdown tb ON t.transaction_id = tb.transaction_id
    842     GROUP BY TO_CHAR(t.date, 'YYYY-MM')
    843     ORDER BY month;
    844 END;
    845 $$;
    846 }}}
    847 Проблематичен месец е месец каде што {{{net_balance}}} е негативен. Резултатите можат да се прикажат во графикон (на пример, бар график или линиски график) за подобра анализа. Може да се додаде процентуална промена меѓу месеците за да се откријат трендовите во приходите и трошоците
    848 
    849 ===== Број на трансакции што ги надминуваат приходите на сметка
    850 {{{#!sql
    851 WITH CumulativeBalances AS (
    852     SELECT
    853         t.transaction_id,
    854         ta.account_name,
    855         SUM(tb.earned_amount - tb.spent_amount) OVER (
    856             PARTITION BY ta.transaction_account_id
    857             ORDER BY t.date
    858         ) AS calculated_balance,
    859         tb.spent_amount
    860     FROM
    861         transaction_account ta
    862     JOIN
    863         transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id
    864     JOIN
    865         transaction t ON tb.transaction_id = t.transaction_id
    866 )
    867 SELECT
    868     account_name,
    869     COUNT(transaction_id) AS transactions_exceeding_balance
    870 FROM
    871     CumulativeBalances
    872 WHERE
    873     spent_amount > calculated_balance -- Кога потрошениот износ го надминува пресметаниот баланс
    874     AND spent_amount > 0
    875 GROUP BY
    876     account_name
    877 ORDER BY
    878     transactions_exceeding_balance DESC;
    879 }}}
    880 Ако сакаме да ги добиеме вкупните трансакции кои ги надминуваат средствата низ целиот систем, без групирање по сметки:
    881 {{{#!sql
    882 WITH CumulativeTotalBalances AS (
    883     SELECT
    884         t.transaction_id,
    885         u.user_id,
    886         SUM(tb.earned_amount - tb.spent_amount) OVER (
    887             PARTITION BY u.user_id
    888             ORDER BY t.date
    889         ) AS calculated_total_balance,
    890         tb.spent_amount
    891     FROM
    892         transaction_account ta
    893     JOIN
    894         user u ON ta.user_id = u.user_id
    895     JOIN
    896         transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id
    897     JOIN
    898         transaction t ON tb.transaction_id = t.transaction_id
    899 )
    900 SELECT
    901     COUNT(transaction_id) AS total_transactions_exceeding_balance
    902 FROM
    903     CumulativeTotalBalances
    904 WHERE
    905     spent_amount > calculated_total_balance -- Кога потрошениот износ го надминува кумулативниот баланс
    906     AND spent_amount > 0;
    907 }}}
    908 
    909 ===== Сумарни податоци за тагови
    910 Сумирани податоци каде редовите се месеци, а колоните се тагови, а во полината се покажуваат различни сумирани податоци:
    911  - Вкупен број на трансакции
    912 {{{#!sql
    913 DO $$
    914 DECLARE
    915     col_list TEXT; -- Листа на тагови за изведување на пивот
    916     dynamic_query TEXT; -- Динамичко SQL прашање
    917 BEGIN
    918     -- Чекор 1: Динамички се генерира листа на колони
    919     SELECT STRING_AGG(DISTINCT format('SUM(CASE WHEN tg.tag_name = %L THEN 1 ELSE 0 END) AS %I', tag_name, tag_name), ', ')
    920     INTO col_list
    921     FROM tag;
    922 
    923     -- Чекор 2: Се гради динамичкото прашање
    924     dynamic_query := format(
    925         'SELECT
    926             TO_CHAR(t.date, ''YYYY-MM'') AS transaction_month,
    927             %s
    928         FROM
    929             transaction t
    930         JOIN
    931             tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id
    932         JOIN
    933             tag tg ON tat.tag_id = tg.tag_id
    934         GROUP BY
    935             transaction_month
    936         ORDER BY
    937             transaction_month;',
    938         col_list
    939     );
    940 
    941     -- Чекор 3: Извршување на динамичкото прашање
    942     EXECUTE dynamic_query;
    943 END $$;
    944 }}}
    945  - Вкупно примени средства
    946 {{{#!sql
    947 DO $$
    948 DECLARE
    949     col_list TEXT; -- Листа на тагови за изведување на пивот
    950     dynamic_query TEXT; -- Динамичко SQL прашање
    951 BEGIN
    952     -- Чекор 1: Динамички се генерира листа на колони
    953     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), ', ')
    954     INTO col_list
    955     FROM tag;
    956 
    957     -- Чекор 2: Се гради динамичкото прашање
    958     dynamic_query := format(
    959         'SELECT
    960             TO_CHAR(t.date, ''YYYY-MM'') AS transaction_month,
    961             %s
    962         FROM
    963             transaction t
    964         JOIN
    965             transaction_breakdown tb ON t.transaction_id = tb.transaction_id
    966         JOIN
    967             tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id
    968         JOIN
    969             tag tg ON tat.tag_id = tg.tag_id
    970         GROUP BY
    971             transaction_month
    972         ORDER BY
    973             transaction_month;',
    974         col_list
    975     );
    976 
    977     -- Чекор 3: Извршување на динамичкото прашање
    978     EXECUTE dynamic_query;
    979 END $$;
    980 }}}
    981  - Вкупно потрошени средства
    982 {{{#!sql
    983 DO $$
    984 DECLARE
    985     col_list TEXT; -- Листа на тагови за изведување на пивот
    986     dynamic_query TEXT; -- Динамичко SQL прашање
    987 BEGIN
    988     -- Чекор 1: Динамички се генерира листа на колони
    989     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), ', ')
    990     INTO col_list
    991     FROM tag;
    992 
    993     -- Чекор 2: Се гради динамичкото прашање
    994     dynamic_query := format(
    995         'SELECT
    996             TO_CHAR(t.date, ''YYYY-MM'') AS transaction_month,
    997             %s
    998         FROM
    999             transaction t
    1000         JOIN
    1001             transaction_breakdown tb ON t.transaction_id = tb.transaction_id
    1002         JOIN
    1003             tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id
    1004         JOIN
    1005             tag tg ON tat.tag_id = tg.tag_id
    1006         GROUP BY
    1007             transaction_month
    1008         ORDER BY
    1009             transaction_month;',
    1010         col_list
    1011     );
    1012 
    1013     -- Чекор 3: Извршување на динамичкото прашање
    1014     EXECUTE dynamic_query;
    1015 END $$;
    1016 }}}
    1017 
    1018 ===== Просечно трошење во последните 3 дена за цел систем
    1019 Просечно дневно трошење за последните три дена, земајќи ги предвид сите трансакции:
    1020 {{{#!sql
    1021 SELECT
    1022     AVG(daily_spending) AS average_spending_last_3_days
    1023 FROM (
    1024     SELECT
    1025         t.date::date AS transaction_date,
    1026         SUM(tb.spent_amount) AS daily_spending
    1027     FROM
    1028         transaction t
    1029     JOIN
    1030         transaction_breakdown tb ON t.transaction_id = tb.transaction_id
    1031     WHERE
    1032         t.date >= CURRENT_DATE - INTERVAL '2 DAY'
    1033         AND t.date < CURRENT_DATE + INTERVAL '1 DAY'
    1034     GROUP BY
    1035         t.date::date
    1036 ) daily_totals;
    1037 }}}
    1038 
    1039 ===== Просечно трошење во последните 3 дена за корисник
    1040 Просечно дневно трошење за последните 3 дена за одреден корисник:
    1041 {{{#!sql
    1042 SELECT
    1043     AVG(daily_spending) AS average_spending_last_3_days
    1044 FROM (
    1045     SELECT
    1046         t.date::date AS transaction_date,
    1047         SUM(tb.spent_amount) AS daily_spending
    1048     FROM
    1049         transaction t
    1050     JOIN transaction_breakdown tb ON t.transaction_id = tb.transaction_id
    1051     JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id
    1052     WHERE
    1053         ta.user_id = 101 -- ID на конкретниот корисник
    1054         AND t.date >= CURRENT_DATE - INTERVAL '2 DAY'
    1055         AND t.date < CURRENT_DATE + INTERVAL '1 DAY'
    1056     GROUP BY
    1057         t.date::date
    1058 ) daily_totals;
    1059 }}}
    1060 
    1061 ===== Вкупно потрошено во тековниот месец за цел систем
    1062 Вкупна сума на трошоци во тековниот месец
    1063 {{{#!sql
    1064 SELECT
    1065     SUM(tb.spent_amount) AS total_spent
    1066 FROM
    1067     transaction_breakdown tb
    1068 JOIN transaction t ON tb.transaction_id = t.transaction_id
    1069 WHERE
    1070     EXTRACT(MONTH FROM t.date) = EXTRACT(MONTH FROM CURRENT_DATE)
    1071     AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE);
    1072 }}}
    1073 
    1074 ===== Вкупно потрошено во тековниот месец за корисник
    1075 Вкупна сума на трошоци во тековниот месец на еден корисник
    1076 {{{#!sql
    1077 SELECT
    1078     SUM(tb.spent_amount) AS total_spent
    1079 FROM
    1080     transaction_breakdown tb
    1081 JOIN transaction t ON tb.transaction_id = t.transaction_id
    1082 JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id
    1083 WHERE
    1084     ta.user_id = 101 -- ID на конкретниот корисник
    1085     AND EXTRACT(MONTH FROM t.date) = EXTRACT(MONTH FROM CURRENT_DATE)
    1086     AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE);
    1087 }}}
    1088 
    1089 ===== Дневен буџет до крајот на месецот за корисник
    1090 Дневен буџет базиран на вкупниот баланс на сите сметки на корисникот
    1091 {{{#!sql
    1092 SELECT
    1093     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
    1094 FROM
    1095     transaction_account ta
    1096 WHERE
    1097     ta.user_id = 101; -- ID на конкретниот корисник
    1098 }}}
    1099 
    1100 ===== Долг на кредитна картичка од минатиот месец за цел систем
    1101 Вкупна сума потрошена од сметките обележани како „кредитна картичка“ за минатиот месец
    1102 {{{#!sql
    1103 SELECT
    1104     SUM(tb.spent_amount) AS credit_card_debt
    1105 FROM
    1106     transaction_breakdown tb
    1107 JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id
    1108 JOIN transaction t ON tb.transaction_id = t.transaction_id
    1109 WHERE
    1110     (ta.account_name ILIKE '%кредитна%' OR ta.account_name ILIKE '%credit%')
    1111     AND EXTRACT(MONTH FROM t.date) = EXTRACT(MONTH FROM CURRENT_DATE - INTERVAL '1 MONTH')
    1112     AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE - INTERVAL '1 MONTH');
    1113 }}}
    1114 
    1115 ===== Долг на кредитна картичка од минатиот месец за корисник
    1116 Потрошено од сметки означени како „кредитна картичка“ за минатиот месец за еден корисник
    1117 {{{#!sql
    1118 SELECT
    1119     SUM(tb.spent_amount) AS credit_card_debt
    1120 FROM
    1121     transaction_breakdown tb
    1122 JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id
    1123 JOIN transaction t ON tb.transaction_id = t.transaction_id
    1124 WHERE
    1125     ta.user_id = 101 -- ID на конкретниот корисник
    1126     AND (ta.account_name ILIKE '%кредитна%' OR ta.account_name ILIKE '%credit%')
    1127     AND EXTRACT(MONTH FROM t.date) = EXTRACT(MONTH FROM CURRENT_DATE - INTERVAL '1 MONTH')
    1128     AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE - INTERVAL '1 MONTH');
    1129 }}}
    1130 
    1131 ===== Трендови на трошење според тагови за цел систем
    1132 Трендови на трошење за секој таг во последните шест месеци
    1133 {{{#!sql
    1134 SELECT
    1135     tg.tag_name,
    1136     DATE_TRUNC('month', t.date) AS month,
    1137     SUM(tb.spent_amount) AS total_spent
    1138 FROM
    1139     tag tg
    1140 JOIN tag_assigned_to_transaction tat ON tg.tag_id = tat.tag_id
    1141 JOIN transaction t ON tat.transaction_id = t.transaction_id
    1142 JOIN transaction_breakdown tb ON t.transaction_id = tb.transaction_id
    1143 WHERE
    1144     t.date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '6 MONTHS')
    1145 GROUP BY
    1146     tg.tag_name, month
    1147 ORDER BY
    1148     tg.tag_name, month;
    1149 }}}
    1150 
    1151 ===== Трендови на трошење според тагови за корисник
    1152 Сумирање на трошењата според тагови во последните 6 месеци за одреден корисник
    1153 {{{#!sql
    1154 SELECT
    1155     tg.tag_name,
    1156     DATE_TRUNC('month', t.date) AS month,
    1157     SUM(tb.spent_amount) AS total_spent
    1158 FROM
    1159     tag_assigned_to_transaction tat
    1160 JOIN transaction t ON tat.transaction_id = t.transaction_id
    1161 JOIN transaction_breakdown tb ON t.transaction_id = tb.transaction_id
    1162 JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id
    1163 JOIN tag tg ON tat.tag_id = tg.tag_id
    1164 WHERE
    1165     ta.user_id = 101 -- ID на конкретниот корисник
    1166     AND t.date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '6 MONTHS')
    1167 GROUP BY
    1168     tg.tag_name, month
    1169 ORDER BY
    1170     tg.tag_name, month;
    1171 }}}
    1172 
    1173 ===== Вкупно трошење според тагови
    1174 Вкупно трошење групирано според тагови за тековниот месец
    1175 {{{#!sql
    1176 SELECT
    1177     tg.tag_name,
    1178     SUM(tb.spent_amount) AS total_spent
    1179 FROM
    1180     tag tg
    1181 JOIN tag_assigned_to_transaction tat ON tg.tag_id = tat.tag_id
    1182 JOIN transaction t ON tat.transaction_id = t.transaction_id
    1183 JOIN transaction_breakdown tb ON t.transaction_id = tb.transaction_id
    1184 WHERE
    1185     EXTRACT(MONTH FROM t.date) = EXTRACT(MONTH FROM CURRENT_DATE)
    1186     AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE)
    1187 GROUP BY
    1188     tg.tag_name
    1189 ORDER BY
    1190     total_spent DESC;
    1191 }}}
    1192 
    1193 ===== Сметки со највисоко вкупно трошење во изминатата година
    1194 {{{#!sql
    1195 SELECT
    1196     ta.account_name,
    1197     SUM(tb.spent_amount) AS total_spent
    1198 FROM
    1199     transaction_breakdown tb
    1200 JOIN transaction t ON tb.transaction_id = t.transaction_id
    1201 JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id
    1202 WHERE
    1203     t.date >= NOW() - INTERVAL '1 YEAR'
    1204 GROUP BY
    1205     ta.account_name
    1206 ORDER BY
    1207     total_spent DESC
    1208 LIMIT 10;
    1209 }}}
    1210 
    1211 ===== Најчести времиња за трансакции
    1212 Во кој час од денот корисниците најчесто вршат трансакции
    1213 {{{#!sql
    1214 SELECT
    1215     EXTRACT(HOUR FROM t.date) AS transaction_hour,
    1216     COUNT(*) AS transaction_count
    1217 FROM
    1218     transaction t
    1219 GROUP BY
    1220     transaction_hour
    1221 ORDER BY
    1222     transaction_count DESC;
    1223 }}}
    1224 
    1225 ===== Трансакции според тагови со највисоко трошење за цел систем
    1226 {{{#!sql
    1227 SELECT
    1228     tg.tag_name,
    1229     SUM(tb.spent_amount) AS total_spent
    1230 FROM
    1231     transaction_breakdown tb
    1232 JOIN transaction t ON tb.transaction_id = t.transaction_id
    1233 JOIN tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id
    1234 JOIN tag tg ON tat.tag_id = tg.tag_id
    1235 GROUP BY
    1236     tg.tag_name
    1237 ORDER BY
    1238     total_spent DESC;
    1239 }}}
    1240 
    1241 ===== Трансакции според тагови со највисоко трошење за корисник
    1242 Сумирање на трошењата според тагови за одреден корисник
    1243 {{{#!sql
    1244 SELECT
    1245     tg.tag_name,
    1246     SUM(tb.spent_amount) AS total_spent
    1247 FROM
    1248     transaction_breakdown tb
    1249 JOIN transaction t ON tb.transaction_id = t.transaction_id
    1250 JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id
    1251 JOIN tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id
    1252 JOIN tag tg ON tat.tag_id = tg.tag_id
    1253 WHERE
    1254     ta.user_id = 101 -- ID на конкретниот корисник
    1255 GROUP BY
    1256     tg.tag_name
    1257 ORDER BY
    1258     total_spent DESC;
    1259 }}}
    1260 
    1261 ===== Годишни трендови на трансакции за цел систем
    1262 Трошења според сметки за секој квартал од тековната година
    1263 {{{#!sql
    1264 SELECT
    1265     ta.account_name,
    1266     SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 1 THEN tb.spent_amount ELSE 0 END) AS q1_spent,
    1267     SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 2 THEN tb.spent_amount ELSE 0 END) AS q2_spent,
    1268     SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 3 THEN tb.spent_amount ELSE 0 END) AS q3_spent,
    1269     SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 4 THEN tb.spent_amount ELSE 0 END) AS q4_spent
    1270 FROM
    1271     transaction_breakdown tb
    1272 JOIN transaction t ON tb.transaction_id = t.transaction_id
    1273 JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id
    1274 WHERE
    1275     EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE)
    1276 GROUP BY
    1277     ta.account_name
    1278 ORDER BY
    1279     ta.account_name;
    1280 }}}
    1281 
    1282 ===== Годишни трендови на трансакции за корисник
    1283 Трошења по квартали за тековната година за одреден корисник
    1284 {{{#!sql
    1285 SELECT
    1286     ta.account_name,
    1287     SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 1 THEN tb.spent_amount ELSE 0 END) AS q1_spent,
    1288     SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 2 THEN tb.spent_amount ELSE 0 END) AS q2_spent,
    1289     SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 3 THEN tb.spent_amount ELSE 0 END) AS q3_spent,
    1290     SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 4 THEN tb.spent_amount ELSE 0 END) AS q4_spent
    1291 FROM
    1292     transaction_breakdown tb
    1293 JOIN transaction t ON tb.transaction_id = t.transaction_id
    1294 JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id
    1295 WHERE
    1296     ta.user_id = 101 -- ID на конкретниот корисник
    1297     AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE)
    1298 GROUP BY
    1299     ta.account_name
    1300 ORDER BY
    1301     ta.account_name;
    1302 }}}
    1303 
    1304 ===== Неактивни тагови
    1305 Идентификување тагови кои не биле користени во изминатиот месец
    1306 {{{#!sql
    1307 SELECT
    1308     tg.tag_name
    1309 FROM
    1310     tag tg
    1311 LEFT JOIN tag_assigned_to_transaction tat ON tg.tag_id = tat.tag_id
    1312 LEFT JOIN transaction t ON tat.transaction_id = t.transaction_id
    1313 WHERE
    1314     t.date IS NULL OR t.date < NOW() - INTERVAL '1 MONTH';
    1315 }}}
     3- [wiki:AdvancedReport1 Листа на трансакции кои го надминале балансот на една сметка подредени хронолошки]
     4- [wiki:AdvancedReport2 Надминување на баланс на една сметка со трансакција во сегашно време]
     5- [wiki:AdvancedReport3 Листа на трансакции кои го надминале балансот на сите сметки вкупно подредени хронолошки]
     6- [wiki:AdvancedReport4 Надминување на вкупниот баланс на сите сметки на корисникот во сегашно време]
     7- [wiki:AdvancedReport5 Сумарни приходи и расходи по месеци]
     8- [wiki:AdvancedReport6 Број на трансакции што ги надминуваат приходите на сметка]
     9- [wiki:AdvancedReport7 Сумарни податоци за тагови]
     10- [wiki:AdvancedReport8 Просечно трошење во последните 3 дена за цел систем]
     11- [wiki:AdvancedReport9 Просечно трошење во последните 3 дена за корисник]
     12- [wiki:AdvancedReport10 Вкупно потрошено во тековниот месец за цел систем]
     13- [wiki:AdvancedReport11 Вкупно потрошено во тековниот месец за корисник]
     14- [wiki:AdvancedReport12 Дневен буџет до крајот на месецот за корисник]
     15- [wiki:AdvancedReport13 Долг на кредитна картичка од минатиот месец за цел систем]
     16- [wiki:AdvancedReport14 Долг на кредитна картичка од минатиот месец за корисник]
     17- [wiki:AdvancedReport15 Трендови на трошење според тагови за цел систем]
     18- [wiki:AdvancedReport16 Трендови на трошење според тагови за корисник]
     19- [wiki:AdvancedReport17 Вкупно трошење според тагови]
     20- [wiki:AdvancedReport18 Сметки со највисоко вкупно трошење во изминатата година]
     21- [wiki:AdvancedReport19 Најчести времиња за трансакции]
     22- [wiki:AdvancedReport20 Трансакции според тагови со највисоко трошење за цел систем]
     23- [wiki:AdvancedReport21 Трансакции според тагови со највисоко трошење за корисник]
     24- [wiki:AdvancedReport22 Годишни трендови на трансакции за цел систем]
     25- [wiki:AdvancedReport23 Годишни трендови на трансакции за корисник]
     26- [wiki:AdvancedReport24 Неактивни тагови]
    131627
    131728==