Changes between Initial Version and Version 1 of AdvancedReports

Timestamp:

12/09/24 23:32:50 (5 weeks ago)

Author:

211101

Comment:

--

AdvancedReports

@@ +1 @@
+= Напредни извештаи од базата (SQL и складирани процедури)
+
+===== Просечно трошење во последните 3 дена
+Просечно дневно трошење за последните три дена, земајќи ги предвид сите трансакции:
+{{{#!sql
+SELECT 
+    AVG(daily_spending) AS average_spending_last_3_days
+FROM (
+    SELECT 
+        t.date::date AS transaction_date, 
+        SUM(tb.spent_amount) AS daily_spending
+    FROM 
+        transaction t
+    JOIN 
+        transaction_breakdown tb ON t.transaction_id = tb.transaction_id
+    WHERE 
+        t.date >= CURRENT_DATE - INTERVAL '2 DAY'
+        AND t.date < CURRENT_DATE + INTERVAL '1 DAY'
+    GROUP BY 
+        t.date::date
+) daily_totals;
+}}}
+
+===== Вкупно потрошено во тековниот месец
+Вкупна сума на трошоци во тековниот месец
+{{{#!sql
+SELECT 
+    SUM(tb.spent_amount) AS total_spent
+FROM 
+    transaction_breakdown tb
+JOIN transaction t ON tb.transaction_id = t.transaction_id
+WHERE 
+    EXTRACT(MONTH FROM t.date) = EXTRACT(MONTH FROM CURRENT_DATE)
+    AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE);
+}}}
+
+===== Просечно дневно трошење за тековниот месец
+{{{#!sql
+SELECT 
+    SUM(tb.spent_amount) / 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;
+}}}
+
+===== Дневен буџет до крајот на месецот
+Дневен буџет за преостанатите денови од месецот врз основа на вкупниот баланс на сите сметки
+{{{#!sql
+SELECT 
+    SUM(ta.balance) / (DATE_PART('day', DATE_TRUNC('month', CURRENT_DATE + INTERVAL '1 MONTH') - INTERVAL '1 DAY') - DATE_PART('day', CURRENT_DATE)) AS daily_budget
+FROM 
+    transaction_account ta;
+}}}
+
+===== Долг на кредитна картичка од минатиот месец
+Вкупна сума потрошена од сметките обележани како „кредитна картичка“ за минатиот месец
+{{{#!sql
+SELECT 
+    SUM(tb.spent_amount) AS credit_card_debt
+FROM 
+    transaction_breakdown tb
+JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id
+JOIN transaction t ON tb.transaction_id = t.transaction_id
+WHERE 
+    (ta.account_name ILIKE '%кредитна%' OR ta.account_name ILIKE '%credit%')
+    AND EXTRACT(MONTH FROM t.date) = EXTRACT(MONTH FROM CURRENT_DATE - INTERVAL '1 MONTH')
+    AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE - INTERVAL '1 MONTH');
+}}}
+
+===== Трендови на трошење според тагови
+Трендови на трошење за секој таг во последните шест месеци
+{{{#!sql
+SELECT 
+    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;
+}}}
+
+===== Вкупно трошење според тагови
+Вкупно трошење групирано според тагови за тековниот месец
+{{{#!sql
+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;
+}}}
+
+===== Сметки со највисоко вкупно трошење во изминатата година
+{{{#!sql
+SELECT 
+    ta.account_name, 
+    SUM(tb.spent_amount) AS total_spent
+FROM 
+    transaction_breakdown tb
+JOIN transaction t ON tb.transaction_id = t.transaction_id
+JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id
+WHERE 
+    t.date >= NOW() - INTERVAL '1 YEAR'
+GROUP BY 
+    ta.account_name
+ORDER BY 
+    total_spent DESC
+LIMIT 10;
+}}}
+
+===== Најчести времиња за трансакции
+Во кој час од денот корисниците најчесто вршат трансакции
+{{{#!sql
+SELECT 
+    EXTRACT(HOUR FROM t.date) AS transaction_hour, 
+    COUNT(*) AS transaction_count
+FROM 
+    transaction t
+GROUP BY 
+    transaction_hour
+ORDER BY 
+    transaction_count DESC;
+}}}
+
+===== Трансакции според тагови со највисоко трошење
+{{{#!sql
+SELECT 
+    tg.tag_name, 
+    SUM(tb.spent_amount) AS total_spent
+FROM 
+    transaction_breakdown tb
+JOIN transaction t ON tb.transaction_id = t.transaction_id
+JOIN tag tg ON t.tag_id = tg.tag_id
+GROUP BY 
+    tg.tag_name
+ORDER BY 
+    total_spent DESC;
+}}}
+
+===== Годишни трендови на трансакции
+Трошења според сметки за секој квартал од тековната година
+{{{#!sql
+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);
+}}}
+
+===== Неактивни тагови
+Идентификување тагови кои не биле користени во изминатиот месец
+{{{#!sql
+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';
+}}}