source: backend/docs/P6_Advanced_Reports.txt@ 89156c1

Last change on this file since 89156c1 was 89156c1, checked in by Andrej <asumanovski@…>, 3 months ago

Phase 6 documentation
  • Property mode set to 100644
File size: 27.0 KB
Line 
1= Напредни извештаи од базата (SQL, складирани процедури и релациона алгебра)
2
3=== 1. Детален годишен извештај за финансиска резилиентност, стабилност на приходи и буџетски притисок по корисник
4
5==== SQL
6{{{
7SET search_path TO trekr;
8
9WITH params AS (
10 SELECT 2026::int AS report_year
11),
12months AS (
13 SELECT generate_series(1, 12) AS month_no
14),
15finance_base AS (
16 SELECT
17 fu.user_id,
18 u.username,
19 u.email,
20 COALESCE(fu.spending_budget, 0) AS spending_budget,
21 COALESCE(fu.saving_budget, 0) AS saving_budget,
22 COALESCE(fu.investing_budget, 0) AS investing_budget,
23 COALESCE(fu.donation_budget, 0) AS donation_budget,
24 COALESCE(fu.credit, 0) AS credit
25 FROM finance_users fu
26 JOIN users u ON u.user_id = fu.user_id
27),
28monthly_income AS (
29 SELECT
30 fb.user_id,
31 m.month_no,
32 COALESCE(SUM(i.amount), 0) AS month_income
33 FROM finance_base fb
34 CROSS JOIN months m
35 LEFT JOIN incomes i
36 ON i.user_id = fb.user_id
37 AND EXTRACT(YEAR FROM i.date)::int = (SELECT report_year FROM params)
38 AND EXTRACT(MONTH FROM i.date)::int = m.month_no
39 GROUP BY fb.user_id, m.month_no
40),
41monthly_income_ranked AS (
42 SELECT
43 mi.*,
44 DENSE_RANK() OVER (PARTITION BY mi.user_id ORDER BY mi.month_income DESC, mi.month_no ASC) AS best_month_rank,
45 DENSE_RANK() OVER (PARTITION BY mi.user_id ORDER BY mi.month_income ASC, mi.month_no ASC) AS worst_month_rank
46 FROM monthly_income mi
47),
48annual_income AS (
49 SELECT
50 user_id,
51 SUM(month_income) AS total_income,
52 AVG(month_income) AS avg_monthly_income,
53 STDDEV_SAMP(month_income) AS income_stddev,
54 MAX(month_income) AS best_month_income,
55 MIN(month_income) AS worst_month_income,
56 COUNT(*) FILTER (WHERE month_income > 0) AS active_income_months
57 FROM monthly_income
58 GROUP BY user_id
59),
60best_worst_months AS (
61 SELECT
62 user_id,
63 MAX(month_no) FILTER (WHERE best_month_rank = 1) AS best_month_no,
64 MAX(month_no) FILTER (WHERE worst_month_rank = 1) AS worst_month_no
65 FROM monthly_income_ranked
66 GROUP BY user_id
67)
68SELECT
69 fb.user_id,
70 fb.username,
71 fb.email,
72 (fb.spending_budget + fb.saving_budget + fb.investing_budget + fb.donation_budget) * 12 AS planned_annual_budget,
73 ai.total_income AS actual_annual_income,
74 ai.avg_monthly_income,
75 ai.active_income_months,
76 ai.best_month_income,
77 ai.worst_month_income,
78 bwm.best_month_no,
79 bwm.worst_month_no,
80 ROUND(
81 (ai.income_stddev / NULLIF(ai.avg_monthly_income, 0))::numeric,
82 4
83 ) AS income_volatility_cv,
84 ROUND(
85 (ai.total_income - (fb.spending_budget * 12))::numeric,
86 2
87 ) AS annual_free_cash_after_spending,
88 ROUND(
89 ((fb.spending_budget * 12) / NULLIF(ai.total_income, 0))::numeric,
90 4
91 ) AS spending_pressure_ratio,
92 ROUND(
93 (fb.credit / NULLIF(ai.total_income, 0))::numeric,
94 4
95 ) AS leverage_ratio,
96 DENSE_RANK() OVER (
97 ORDER BY
98 (ai.total_income - (fb.spending_budget * 12)) DESC,
99 ((fb.spending_budget * 12) / NULLIF(ai.total_income, 0)) ASC,
100 fb.user_id ASC
101 ) AS finance_resilience_rank
102FROM finance_base fb
103JOIN annual_income ai ON ai.user_id = fb.user_id
104JOIN best_worst_months bwm ON bwm.user_id = fb.user_id
105ORDER BY finance_resilience_rank, fb.user_id;
106}}}
107
108==== Релациона Алгебра
109{{{
110FB <- pi_{fu.user_id, u.username, u.email,
111 COALESCE(fu.spending_budget,0)->spending_budget,
112 COALESCE(fu.saving_budget,0)->saving_budget,
113 COALESCE(fu.investing_budget,0)->investing_budget,
114 COALESCE(fu.donation_budget,0)->donation_budget,
115 COALESCE(fu.credit,0)->credit}
116 (finance_users fu bowtie_{fu.user_id = u.user_id} users u)
117
118FBM <- FB x M
119IY <- sigma_{YEAR(i.date)=Y}(incomes i)
120MI0 <- FBM leftouterjoin_{FBM.user_id = i.user_id AND FBM.month_no = MONTH(i.date)} IY
121MI <- gamma_{user_id, month_no;
122 SUM(COALESCE(i.amount,0))->month_income}(MI0)
123
124MIR <- omega_{PARTITION BY user_id ORDER BY month_income DESC, month_no ASC;
125 DENSE_RANK()->best_month_rank,
126 DENSE_RANK(PARTITION BY user_id ORDER BY month_income ASC, month_no ASC)->worst_month_rank}(MI)
127
128AI <- gamma_{user_id;
129 SUM(month_income)->total_income,
130 AVG(month_income)->avg_monthly_income,
131 STDDEV_SAMP(month_income)->income_stddev,
132 MAX(month_income)->best_month_income,
133 MIN(month_income)->worst_month_income,
134 COUNT_IF(month_income>0)->active_income_months}(MI)
135
136BWM <- gamma_{user_id;
137 MAX_IF(month_no, best_month_rank=1)->best_month_no,
138 MAX_IF(month_no, worst_month_rank=1)->worst_month_no}(MIR)
139
140R0 <- FB bowtie_{FB.user_id=AI.user_id} AI bowtie_{FB.user_id=BWM.user_id} BWM
141R1 <- alpha_{(spending_budget+saving_budget+investing_budget+donation_budget)*12->planned_annual_budget,
142 total_income->actual_annual_income,
143 income_stddev/NULLIF(avg_monthly_income,0)->income_volatility_cv,
144 total_income-(spending_budget*12)->annual_free_cash_after_spending,
145 (spending_budget*12)/NULLIF(total_income,0)->spending_pressure_ratio,
146 credit/NULLIF(total_income,0)->leverage_ratio}(R0)
147R <- omega_{ORDER BY annual_free_cash_after_spending DESC,
148 spending_pressure_ratio ASC,
149 user_id ASC;
150 DENSE_RANK()->finance_resilience_rank}(R1)
151}}}
152
153=== 2. Детален годишен извештај за конзистентност на тренинг, оптоварување и тренд на перформанс
154
155==== SQL
156{{{
157SET search_path TO trekr;
158
159WITH params AS (
160 SELECT 2026::int AS report_year
161),
162months AS (
163 SELECT generate_series(1, 12) AS month_no
164),
165training_base AS (
166 SELECT
167 tu.user_id,
168 u.username,
169 u.email,
170 tu.gender,
171 tu.age,
172 tu.weight
173 FROM training_users tu
174 JOIN users u ON u.user_id = tu.user_id
175),
176monthly_sessions AS (
177 SELECT
178 tb.user_id,
179 m.month_no,
180 COALESCE(COUNT(ts.training_id), 0) AS sessions_count,
181 COALESCE(SUM(ts.duration), 0) AS total_duration_minutes,
182 COALESCE(SUM(ts.calories), 0) AS total_calories,
183 COALESCE(AVG(ts.duration), 0) AS avg_session_duration,
184 COALESCE(AVG(ts.calories), 0) AS avg_session_calories
185 FROM training_base tb
186 CROSS JOIN months m
187 LEFT JOIN training_sessions ts
188 ON ts.training_user_id = tb.user_id
189 AND EXTRACT(YEAR FROM ts.date)::int = (SELECT report_year FROM params)
190 AND EXTRACT(MONTH FROM ts.date)::int = m.month_no
191 GROUP BY tb.user_id, m.month_no
192),
193monthly_ranked AS (
194 SELECT
195 ms.*,
196 DENSE_RANK() OVER (PARTITION BY ms.user_id ORDER BY ms.total_calories DESC, ms.month_no ASC) AS peak_calorie_month_rank,
197 DENSE_RANK() OVER (PARTITION BY ms.user_id ORDER BY ms.sessions_count DESC, ms.month_no ASC) AS peak_sessions_month_rank
198 FROM monthly_sessions ms
199),
200active_month_streaks AS (
201 SELECT
202 user_id,
203 month_no,
204 month_no - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY month_no) AS grp
205 FROM monthly_sessions
206 WHERE sessions_count > 0
207),
208longest_streak AS (
209 SELECT
210 user_id,
211 MAX(streak_len) AS longest_active_month_streak
212 FROM (
213 SELECT user_id, grp, COUNT(*) AS streak_len
214 FROM active_month_streaks
215 GROUP BY user_id, grp
216 ) s
217 GROUP BY user_id
218),
219annual_training AS (
220 SELECT
221 user_id,
222 SUM(sessions_count) AS annual_sessions,
223 SUM(total_duration_minutes) AS annual_duration_minutes,
224 SUM(total_calories) AS annual_calories,
225 AVG(total_duration_minutes) AS avg_monthly_duration,
226 AVG(total_calories) AS avg_monthly_calories,
227 COUNT(*) FILTER (WHERE sessions_count > 0) AS active_months,
228 REGR_SLOPE(total_calories::numeric, month_no::numeric) AS calories_trend_slope,
229 REGR_SLOPE(total_duration_minutes::numeric, month_no::numeric) AS duration_trend_slope
230 FROM monthly_sessions
231 GROUP BY user_id
232),
233peak_months AS (
234 SELECT
235 user_id,
236 MAX(month_no) FILTER (WHERE peak_calorie_month_rank = 1) AS peak_calorie_month_no,
237 MAX(month_no) FILTER (WHERE peak_sessions_month_rank = 1) AS peak_sessions_month_no
238 FROM monthly_ranked
239 GROUP BY user_id
240)
241SELECT
242 tb.user_id,
243 tb.username,
244 tb.email,
245 tb.gender,
246 tb.age,
247 tb.weight,
248 at.annual_sessions,
249 ROUND(at.annual_duration_minutes::numeric, 2) AS annual_duration_minutes,
250 ROUND(at.annual_calories::numeric, 2) AS annual_calories,
251 at.active_months,
252 ROUND((at.active_months / 12.0)::numeric, 4) AS consistency_ratio,
253 COALESCE(ls.longest_active_month_streak, 0) AS longest_active_month_streak,
254 pm.peak_calorie_month_no,
255 pm.peak_sessions_month_no,
256 ROUND(COALESCE(at.calories_trend_slope, 0)::numeric, 4) AS calories_trend_slope,
257 ROUND(COALESCE(at.duration_trend_slope, 0)::numeric, 4) AS duration_trend_slope,
258 DENSE_RANK() OVER (
259 ORDER BY
260 at.annual_calories DESC,
261 at.active_months DESC,
262 COALESCE(ls.longest_active_month_streak, 0) DESC,
263 tb.user_id ASC
264 ) AS training_annual_rank
265FROM training_base tb
266JOIN annual_training at ON at.user_id = tb.user_id
267JOIN peak_months pm ON pm.user_id = tb.user_id
268LEFT JOIN longest_streak ls ON ls.user_id = tb.user_id
269ORDER BY training_annual_rank, tb.user_id;
270}}}
271
272==== Релациона Алгебра
273{{{
274TB <- pi_{tu.user_id, u.username, u.email, tu.gender, tu.age, tu.weight}
275 (training_users tu bowtie_{tu.user_id = u.user_id} users u)
276
277TBM <- TB x M
278TSY <- sigma_{YEAR(ts.date)=Y}(training_sessions ts)
279MS0 <- TBM leftouterjoin_{TBM.user_id = ts.training_user_id AND TBM.month_no = MONTH(ts.date)} TSY
280MS <- gamma_{user_id, month_no;
281 COUNT(ts.training_id)->sessions_count,
282 SUM(COALESCE(ts.duration,0))->total_duration_minutes,
283 SUM(COALESCE(ts.calories,0))->total_calories,
284 AVG(COALESCE(ts.duration,0))->avg_session_duration,
285 AVG(COALESCE(ts.calories,0))->avg_session_calories}(MS0)
286
287MR <- omega_{PARTITION BY user_id ORDER BY total_calories DESC, month_no ASC;
288 DENSE_RANK()->peak_calorie_month_rank,
289 DENSE_RANK(PARTITION BY user_id ORDER BY sessions_count DESC, month_no ASC)->peak_sessions_month_rank}(MS)
290
291AMS <- sigma_{sessions_count>0}(MS)
292AMS1 <- omega_{PARTITION BY user_id ORDER BY month_no;
293 ROW_NUMBER()->rn}(AMS)
294AMS2 <- alpha_{month_no - rn -> grp}(AMS1)
295LS0 <- gamma_{user_id, grp; COUNT(*)->streak_len}(AMS2)
296LS <- gamma_{user_id; MAX(streak_len)->longest_active_month_streak}(LS0)
297
298AT <- gamma_{user_id;
299 SUM(sessions_count)->annual_sessions,
300 SUM(total_duration_minutes)->annual_duration_minutes,
301 SUM(total_calories)->annual_calories,
302 AVG(total_duration_minutes)->avg_monthly_duration,
303 AVG(total_calories)->avg_monthly_calories,
304 COUNT_IF(sessions_count>0)->active_months,
305 REGR_SLOPE(total_calories, month_no)->calories_trend_slope,
306 REGR_SLOPE(total_duration_minutes, month_no)->duration_trend_slope}(MS)
307
308PM <- gamma_{user_id;
309 MAX_IF(month_no, peak_calorie_month_rank=1)->peak_calorie_month_no,
310 MAX_IF(month_no, peak_sessions_month_rank=1)->peak_sessions_month_no}(MR)
311
312R0 <- TB bowtie_{TB.user_id=AT.user_id} AT
313 bowtie_{TB.user_id=PM.user_id} PM
314 leftouterjoin_{TB.user_id=LS.user_id} LS
315R1 <- alpha_{active_months/12.0->consistency_ratio,
316 COALESCE(longest_active_month_streak,0)->longest_active_month_streak_nz,
317 COALESCE(calories_trend_slope,0)->calories_trend_slope_nz,
318 COALESCE(duration_trend_slope,0)->duration_trend_slope_nz}(R0)
319R <- omega_{ORDER BY annual_calories DESC,
320 active_months DESC,
321 longest_active_month_streak_nz DESC,
322 user_id ASC;
323 DENSE_RANK()->training_annual_rank}(R1)
324}}}
325
326=== 3. Детален годишен извештај за дисциплина, квалитет на завршување и однесување преку streaks
327
328==== SQL
329{{{
330SET search_path TO trekr;
331
332WITH params AS (
333 SELECT 2026::int AS report_year
334),
335discipline_base AS (
336 SELECT
337 du.user_id,
338 u.username,
339 u.email
340 FROM discipline_users du
341 JOIN users u ON u.user_id = du.user_id
342),
343task_mix AS (
344 SELECT
345 COALESCE(t.discipline_user_id, c.user_id) AS user_id,
346 COUNT(*) AS total_tasks_defined,
347 COUNT(*) FILTER (WHERE t.custom_tracking_id IS NULL) AS core_tasks,
348 COUNT(*) FILTER (WHERE t.custom_tracking_id IS NOT NULL) AS custom_tasks,
349 COUNT(DISTINCT COALESCE(t.custom_tracking_id::text, 'core')) AS task_category_span
350 FROM tasks t
351 LEFT JOIN custom_tracking_categories c
352 ON c.custom_tracking_id = t.custom_tracking_id
353 WHERE t.discipline_user_id IS NOT NULL
354 OR t.custom_tracking_id IS NOT NULL
355 GROUP BY COALESCE(t.discipline_user_id, c.user_id)
356),
357annual_daily_completion AS (
358 SELECT
359 dc.user_id,
360 dc.date,
361 COALESCE(dc.procent, 0) AS procent,
362 CASE WHEN COALESCE(dc.procent, 0) >= 80 THEN 1 ELSE 0 END AS strong_day
363 FROM daily_completion dc
364 WHERE EXTRACT(YEAR FROM dc.date)::int = (SELECT report_year FROM params)
365),
366daily_completion_stats AS (
367 SELECT
368 adc.user_id,
369 COUNT(*) AS tracked_days,
370 AVG(adc.procent) AS avg_completion_percent,
371 PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY adc.procent) AS median_completion_percent,
372 COUNT(*) FILTER (WHERE adc.procent = 100) AS perfect_days,
373 COUNT(*) FILTER (WHERE adc.procent >= 80) AS strong_days,
374 STDDEV_SAMP(adc.procent) AS completion_variability
375 FROM annual_daily_completion adc
376 GROUP BY adc.user_id
377),
378strong_day_streaks AS (
379 SELECT
380 user_id,
381 date,
382 date - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date))::int AS grp
383 FROM annual_daily_completion
384 WHERE strong_day = 1
385),
386longest_strong_streak AS (
387 SELECT
388 user_id,
389 MAX(streak_len) AS longest_strong_day_streak
390 FROM (
391 SELECT user_id, grp, COUNT(*) AS streak_len
392 FROM strong_day_streaks
393 GROUP BY user_id, grp
394 ) s
395 GROUP BY user_id
396),
397annual_task_execution AS (
398 SELECT
399 dc.user_id,
400 COUNT(tdc.task_id) AS completed_task_events
401 FROM daily_completion dc
402 LEFT JOIN task_daily_completion tdc
403 ON tdc.daily_completion_id = dc.daily_completion_id
404 WHERE EXTRACT(YEAR FROM dc.date)::int = (SELECT report_year FROM params)
405 GROUP BY dc.user_id
406)
407SELECT
408 db.user_id,
409 db.username,
410 db.email,
411 COALESCE(tm.total_tasks_defined, 0) AS total_tasks_defined,
412 COALESCE(tm.core_tasks, 0) AS core_tasks,
413 COALESCE(tm.custom_tasks, 0) AS custom_tasks,
414 COALESCE(tm.task_category_span, 0) AS task_category_span,
415 COALESCE(dcs.tracked_days, 0) AS tracked_days,
416 ROUND(COALESCE(dcs.avg_completion_percent, 0)::numeric, 2) AS avg_completion_percent,
417 ROUND(COALESCE(dcs.median_completion_percent, 0)::numeric, 2) AS median_completion_percent,
418 COALESCE(dcs.perfect_days, 0) AS perfect_days,
419 COALESCE(dcs.strong_days, 0) AS strong_days,
420 ROUND(COALESCE(dcs.completion_variability, 0)::numeric, 4) AS completion_variability,
421 COALESCE(ate.completed_task_events, 0) AS completed_task_events,
422 COALESCE(lss.longest_strong_day_streak, 0) AS longest_strong_day_streak,
423 ROUND(
424 COALESCE((COALESCE(dcs.strong_days, 0) / NULLIF(COALESCE(dcs.tracked_days, 0), 0)::numeric), 0),
425 4
426 ) AS strong_day_ratio,
427 ROUND(
428 (
429 COALESCE(dcs.avg_completion_percent, 0) * 0.45
430 + COALESCE(lss.longest_strong_day_streak, 0) * 2.00
431 + COALESCE(ate.completed_task_events, 0) * 0.35
432 )::numeric,
433 2
434 ) AS discipline_composite_score,
435 DENSE_RANK() OVER (
436 ORDER BY
437 (
438 COALESCE(dcs.avg_completion_percent, 0) * 0.45
439 + COALESCE(lss.longest_strong_day_streak, 0) * 2.00
440 + COALESCE(ate.completed_task_events, 0) * 0.35
441 ) DESC,
442 db.user_id ASC
443 ) AS discipline_annual_rank
444FROM discipline_base db
445LEFT JOIN task_mix tm ON tm.user_id = db.user_id
446LEFT JOIN daily_completion_stats dcs ON dcs.user_id = db.user_id
447LEFT JOIN annual_task_execution ate ON ate.user_id = db.user_id
448LEFT JOIN longest_strong_streak lss ON lss.user_id = db.user_id
449ORDER BY discipline_annual_rank, db.user_id;
450}}}
451
452==== Релациона Алгебра
453{{{
454DB <- pi_{du.user_id, u.username, u.email}
455 (discipline_users du bowtie_{du.user_id = u.user_id} users u)
456
457TC <- tasks t leftouterjoin_{t.custom_tracking_id = c.custom_tracking_id} custom_tracking_categories c
458TM0 <- alpha_{COALESCE(t.discipline_user_id, c.user_id)->owner_user_id}(TC)
459TM1 <- sigma_{t.discipline_user_id IS NOT NULL OR t.custom_tracking_id IS NOT NULL}(TM0)
460TM <- gamma_{owner_user_id;
461 COUNT(*)->total_tasks_defined,
462 COUNT_IF(t.custom_tracking_id IS NULL)->core_tasks,
463 COUNT_IF(t.custom_tracking_id IS NOT NULL)->custom_tasks,
464 COUNT_DISTINCT(COALESCE(t.custom_tracking_id,'core'))->task_category_span}(TM1)
465
466ADC0 <- sigma_{YEAR(dc.date)=Y}(daily_completion dc)
467ADC <- alpha_{COALESCE(dc.procent,0)->procent,
468 CASE(procent>=80,1,0)->strong_day}(ADC0)
469
470DCS <- gamma_{user_id;
471 COUNT(*)->tracked_days,
472 AVG(procent)->avg_completion_percent,
473 PERCENTILE_CONT_0_5(procent)->median_completion_percent,
474 COUNT_IF(procent=100)->perfect_days,
475 COUNT_IF(procent>=80)->strong_days,
476 STDDEV_SAMP(procent)->completion_variability}(ADC)
477
478SDS0 <- sigma_{strong_day=1}(ADC)
479SDS1 <- omega_{PARTITION BY user_id ORDER BY date; ROW_NUMBER()->rn}(SDS0)
480SDS2 <- alpha_{date - rn -> grp}(SDS1)
481LSS0 <- gamma_{user_id, grp; COUNT(*)->streak_len}(SDS2)
482LSS <- gamma_{user_id; MAX(streak_len)->longest_strong_day_streak}(LSS0)
483
484ATE0 <- ADC0 leftouterjoin_{ADC0.daily_completion_id = tdc.daily_completion_id} task_daily_completion tdc
485ATE <- gamma_{ADC0.user_id; COUNT(tdc.task_id)->completed_task_events}(ATE0)
486
487R0 <- DB
488 leftouterjoin_{DB.user_id = TM.owner_user_id} TM
489 leftouterjoin_{DB.user_id = DCS.user_id} DCS
490 leftouterjoin_{DB.user_id = ATE.user_id} ATE
491 leftouterjoin_{DB.user_id = LSS.user_id} LSS
492R1 <- alpha_{COALESCE(total_tasks_defined,0)->total_tasks_defined_nz,
493 COALESCE(core_tasks,0)->core_tasks_nz,
494 COALESCE(custom_tasks,0)->custom_tasks_nz,
495 COALESCE(task_category_span,0)->task_category_span_nz,
496 COALESCE(tracked_days,0)->tracked_days_nz,
497 COALESCE(avg_completion_percent,0)->avg_completion_percent_nz,
498 COALESCE(median_completion_percent,0)->median_completion_percent_nz,
499 COALESCE(perfect_days,0)->perfect_days_nz,
500 COALESCE(strong_days,0)->strong_days_nz,
501 COALESCE(completion_variability,0)->completion_variability_nz,
502 COALESCE(completed_task_events,0)->completed_task_events_nz,
503 COALESCE(longest_strong_day_streak,0)->longest_strong_day_streak_nz,
504 COALESCE(strong_days/NULLIF(tracked_days,0),0)->strong_day_ratio,
505 (COALESCE(avg_completion_percent,0)*0.45 +
506 COALESCE(longest_strong_day_streak,0)*2.00 +
507 COALESCE(completed_task_events,0)*0.35)->discipline_composite_score}(R0)
508R <- omega_{ORDER BY discipline_composite_score DESC, user_id ASC;
509 DENSE_RANK()->discipline_annual_rank}(R1)
510}}}
511
512=== 4. Детален годишен извештај за инвестициска диверзификација, концентрација и темпо на вложување
513
514==== SQL
515{{{
516SET search_path TO trekr;
517
518WITH params AS (
519 SELECT 2026::int AS report_year
520),
521months AS (
522 SELECT generate_series(1, 12) AS month_no
523),
524investor_base AS (
525 SELECT
526 iu.user_id,
527 u.username,
528 u.email
529 FROM investor_users iu
530 JOIN users u ON u.user_id = iu.user_id
531),
532annual_asset_lots AS (
533 SELECT
534 a.user_id,
535 a.ticker_symbol,
536 COALESCE(a.quantity, 0) AS quantity,
537 COALESCE(a.buy_price, 0) AS buy_price,
538 COALESCE(a.quantity, 0) * COALESCE(a.buy_price, 0) AS invested_amount,
539 a.buy_date
540 FROM assets a
541 WHERE EXTRACT(YEAR FROM a.buy_date)::int = (SELECT report_year FROM params)
542),
543ticker_rollup AS (
544 SELECT
545 aal.user_id,
546 aal.ticker_symbol,
547 SUM(aal.quantity) AS total_quantity,
548 SUM(aal.invested_amount) AS total_invested_amount,
549 COUNT(*) AS lot_count,
550 MIN(aal.buy_date) AS first_buy_date,
551 MAX(aal.buy_date) AS last_buy_date
552 FROM annual_asset_lots aal
553 GROUP BY aal.user_id, aal.ticker_symbol
554),
555portfolio_totals AS (
556 SELECT
557 user_id,
558 SUM(total_invested_amount) AS annual_total_invested,
559 SUM(lot_count) AS annual_lot_count,
560 COUNT(*) AS distinct_tickers
561 FROM ticker_rollup
562 GROUP BY user_id
563),
564weights AS (
565 SELECT
566 tr.user_id,
567 tr.ticker_symbol,
568 tr.total_invested_amount,
569 pt.annual_total_invested,
570 (tr.total_invested_amount / NULLIF(pt.annual_total_invested, 0)) AS position_weight,
571 DENSE_RANK() OVER (
572 PARTITION BY tr.user_id
573 ORDER BY tr.total_invested_amount DESC, tr.ticker_symbol ASC
574 ) AS position_rank
575 FROM ticker_rollup tr
576 JOIN portfolio_totals pt ON pt.user_id = tr.user_id
577),
578concentration AS (
579 SELECT
580 user_id,
581 SUM(position_weight * position_weight) AS hhi_concentration,
582 MAX(position_weight) AS top_position_weight,
583 MAX(ticker_symbol) FILTER (WHERE position_rank = 1) AS top_ticker
584 FROM weights
585 GROUP BY user_id
586),
587monthly_investment AS (
588 SELECT
589 ib.user_id,
590 m.month_no,
591 COALESCE(SUM(a.quantity * a.buy_price), 0) AS monthly_invested_amount
592 FROM investor_base ib
593 CROSS JOIN months m
594 LEFT JOIN assets a
595 ON a.user_id = ib.user_id
596 AND EXTRACT(YEAR FROM a.buy_date)::int = (SELECT report_year FROM params)
597 AND EXTRACT(MONTH FROM a.buy_date)::int = m.month_no
598 GROUP BY ib.user_id, m.month_no
599),
600monthly_investment_stats AS (
601 SELECT
602 user_id,
603 AVG(monthly_invested_amount) AS avg_monthly_contribution,
604 STDDEV_SAMP(monthly_invested_amount) AS contribution_stddev,
605 COUNT(*) FILTER (WHERE monthly_invested_amount > 0) AS active_investing_months
606 FROM monthly_investment
607 GROUP BY user_id
608)
609SELECT
610 ib.user_id,
611 ib.username,
612 ib.email,
613 COALESCE(pt.annual_total_invested, 0) AS annual_total_invested,
614 COALESCE(pt.annual_lot_count, 0) AS annual_lot_count,
615 COALESCE(pt.distinct_tickers, 0) AS distinct_tickers,
616 ROUND(COALESCE(ms.avg_monthly_contribution, 0)::numeric, 2) AS avg_monthly_contribution,
617 COALESCE(ms.active_investing_months, 0) AS active_investing_months,
618 ROUND((COALESCE(ms.active_investing_months, 0) / 12.0)::numeric, 4) AS activity_ratio,
619 ROUND(COALESCE(c.hhi_concentration, 0)::numeric, 4) AS hhi_concentration,
620 ROUND((1 - COALESCE(c.hhi_concentration, 1))::numeric, 4) AS diversification_index,
621 ROUND(COALESCE(c.top_position_weight, 0)::numeric, 4) AS top_position_weight,
622 c.top_ticker,
623 ROUND((COALESCE(ms.contribution_stddev, 0) / NULLIF(ms.avg_monthly_contribution, 0))::numeric, 4) AS contribution_volatility_cv,
624 DENSE_RANK() OVER (
625 ORDER BY
626 (1 - COALESCE(c.hhi_concentration, 1)) DESC,
627 COALESCE(pt.annual_total_invested, 0) DESC,
628 COALESCE(ms.active_investing_months, 0) DESC,
629 ib.user_id ASC
630 ) AS investing_annual_rank
631FROM investor_base ib
632LEFT JOIN portfolio_totals pt ON pt.user_id = ib.user_id
633LEFT JOIN concentration c ON c.user_id = ib.user_id
634LEFT JOIN monthly_investment_stats ms ON ms.user_id = ib.user_id
635ORDER BY investing_annual_rank, ib.user_id;
636}}}
637
638==== Релациона Алгебра
639{{{
640IB <- pi_{iu.user_id, u.username, u.email}
641 (investor_users iu bowtie_{iu.user_id = u.user_id} users u)
642
643AAL <- pi_{a.user_id, a.ticker_symbol,
644 COALESCE(a.quantity,0)->quantity,
645 COALESCE(a.buy_price,0)->buy_price,
646 COALESCE(a.quantity,0)*COALESCE(a.buy_price,0)->invested_amount,
647 a.buy_date}
648 (sigma_{YEAR(a.buy_date)=Y}(assets a))
649
650TR <- gamma_{user_id, ticker_symbol;
651 SUM(quantity)->total_quantity,
652 SUM(invested_amount)->total_invested_amount,
653 COUNT(*)->lot_count,
654 MIN(buy_date)->first_buy_date,
655 MAX(buy_date)->last_buy_date}(AAL)
656
657PT <- gamma_{user_id;
658 SUM(total_invested_amount)->annual_total_invested,
659 SUM(lot_count)->annual_lot_count,
660 COUNT(*)->distinct_tickers}(TR)
661
662W0 <- TR bowtie_{TR.user_id = PT.user_id} PT
663W1 <- alpha_{total_invested_amount/NULLIF(annual_total_invested,0)->position_weight}(W0)
664W <- omega_{PARTITION BY user_id ORDER BY total_invested_amount DESC, ticker_symbol ASC;
665 DENSE_RANK()->position_rank}(W1)
666
667C <- gamma_{user_id;
668 SUM(position_weight*position_weight)->hhi_concentration,
669 MAX(position_weight)->top_position_weight,
670 MAX_IF(ticker_symbol, position_rank=1)->top_ticker}(W)
671
672IBM <- IB x M
673AY <- sigma_{YEAR(a.buy_date)=Y}(assets a)
674MI0 <- IBM leftouterjoin_{IBM.user_id=a.user_id AND IBM.month_no=MONTH(a.buy_date)} AY
675MI <- gamma_{user_id, month_no;
676 SUM(COALESCE(a.quantity,0)*COALESCE(a.buy_price,0))->monthly_invested_amount}(MI0)
677MS <- gamma_{user_id;
678 AVG(monthly_invested_amount)->avg_monthly_contribution,
679 STDDEV_SAMP(monthly_invested_amount)->contribution_stddev,
680 COUNT_IF(monthly_invested_amount>0)->active_investing_months}(MI)
681
682R0 <- IB
683 leftouterjoin_{IB.user_id=PT.user_id} PT
684 leftouterjoin_{IB.user_id=C.user_id} C
685 leftouterjoin_{IB.user_id=MS.user_id} MS
686R1 <- alpha_{COALESCE(annual_total_invested,0)->annual_total_invested_nz,
687 COALESCE(annual_lot_count,0)->annual_lot_count_nz,
688 COALESCE(distinct_tickers,0)->distinct_tickers_nz,
689 COALESCE(avg_monthly_contribution,0)->avg_monthly_contribution_nz,
690 COALESCE(active_investing_months,0)->active_investing_months_nz,
691 COALESCE(active_investing_months,0)/12.0->activity_ratio,
692 COALESCE(hhi_concentration,0)->hhi_concentration_nz,
693 1-COALESCE(hhi_concentration,1)->diversification_index,
694 COALESCE(top_position_weight,0)->top_position_weight_nz,
695 COALESCE(contribution_stddev/NULLIF(avg_monthly_contribution,0),0)->contribution_volatility_cv}(R0)
696R <- omega_{ORDER BY diversification_index DESC,
697 annual_total_invested_nz DESC,
698 active_investing_months_nz DESC,
699 user_id ASC;
700 DENSE_RANK()->investing_annual_rank}(R1)
701}}}
702
Note: See TracBrowser for help on using the repository browser.