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

= Напредни извештаи од базата (SQL, складирани процедури и релациона алгебра)

=== 1. Детален годишен извештај за финансиска резилиентност, стабилност на приходи и буџетски притисок по корисник

==== SQL
{{{
SET search_path TO trekr;

WITH params AS (
    SELECT 2026::int AS report_year
),
months AS (
    SELECT generate_series(1, 12) AS month_no
),
finance_base AS (
    SELECT
        fu.user_id,
        u.username,
        u.email,
        COALESCE(fu.spending_budget, 0) AS spending_budget,
        COALESCE(fu.saving_budget, 0) AS saving_budget,
        COALESCE(fu.investing_budget, 0) AS investing_budget,
        COALESCE(fu.donation_budget, 0) AS donation_budget,
        COALESCE(fu.credit, 0) AS credit
    FROM finance_users fu
    JOIN users u ON u.user_id = fu.user_id
),
monthly_income AS (
    SELECT
        fb.user_id,
        m.month_no,
        COALESCE(SUM(i.amount), 0) AS month_income
    FROM finance_base fb
    CROSS JOIN months m
    LEFT JOIN incomes i
        ON i.user_id = fb.user_id
       AND EXTRACT(YEAR FROM i.date)::int = (SELECT report_year FROM params)
       AND EXTRACT(MONTH FROM i.date)::int = m.month_no
    GROUP BY fb.user_id, m.month_no
),
monthly_income_ranked AS (
    SELECT
        mi.*,
        DENSE_RANK() OVER (PARTITION BY mi.user_id ORDER BY mi.month_income DESC, mi.month_no ASC) AS best_month_rank,
        DENSE_RANK() OVER (PARTITION BY mi.user_id ORDER BY mi.month_income ASC, mi.month_no ASC) AS worst_month_rank
    FROM monthly_income mi
),
annual_income AS (
    SELECT
        user_id,
        SUM(month_income) AS total_income,
        AVG(month_income) AS avg_monthly_income,
        STDDEV_SAMP(month_income) AS income_stddev,
        MAX(month_income) AS best_month_income,
        MIN(month_income) AS worst_month_income,
        COUNT(*) FILTER (WHERE month_income > 0) AS active_income_months
    FROM monthly_income
    GROUP BY user_id
),
best_worst_months AS (
    SELECT
        user_id,
        MAX(month_no) FILTER (WHERE best_month_rank = 1) AS best_month_no,
        MAX(month_no) FILTER (WHERE worst_month_rank = 1) AS worst_month_no
    FROM monthly_income_ranked
    GROUP BY user_id
)
SELECT
    fb.user_id,
    fb.username,
    fb.email,
    (fb.spending_budget + fb.saving_budget + fb.investing_budget + fb.donation_budget) * 12 AS planned_annual_budget,
    ai.total_income AS actual_annual_income,
    ai.avg_monthly_income,
    ai.active_income_months,
    ai.best_month_income,
    ai.worst_month_income,
    bwm.best_month_no,
    bwm.worst_month_no,
    ROUND(
        (ai.income_stddev / NULLIF(ai.avg_monthly_income, 0))::numeric,
        4
    ) AS income_volatility_cv,
    ROUND(
        (ai.total_income - (fb.spending_budget * 12))::numeric,
        2
    ) AS annual_free_cash_after_spending,
    ROUND(
        ((fb.spending_budget * 12) / NULLIF(ai.total_income, 0))::numeric,
        4
    ) AS spending_pressure_ratio,
    ROUND(
        (fb.credit / NULLIF(ai.total_income, 0))::numeric,
        4
    ) AS leverage_ratio,
    DENSE_RANK() OVER (
        ORDER BY
            (ai.total_income - (fb.spending_budget * 12)) DESC,
            ((fb.spending_budget * 12) / NULLIF(ai.total_income, 0)) ASC,
            fb.user_id ASC
    ) AS finance_resilience_rank
FROM finance_base fb
JOIN annual_income ai ON ai.user_id = fb.user_id
JOIN best_worst_months bwm ON bwm.user_id = fb.user_id
ORDER BY finance_resilience_rank, fb.user_id;
}}}

==== Релациона Алгебра
{{{
FB <- pi_{fu.user_id, u.username, u.email,
          COALESCE(fu.spending_budget,0)->spending_budget,
          COALESCE(fu.saving_budget,0)->saving_budget,
          COALESCE(fu.investing_budget,0)->investing_budget,
          COALESCE(fu.donation_budget,0)->donation_budget,
          COALESCE(fu.credit,0)->credit}
      (finance_users fu bowtie_{fu.user_id = u.user_id} users u)

FBM <- FB x M
IY <- sigma_{YEAR(i.date)=Y}(incomes i)
MI0 <- FBM leftouterjoin_{FBM.user_id = i.user_id AND FBM.month_no = MONTH(i.date)} IY
MI <- gamma_{user_id, month_no;
             SUM(COALESCE(i.amount,0))->month_income}(MI0)

MIR <- omega_{PARTITION BY user_id ORDER BY month_income DESC, month_no ASC;
              DENSE_RANK()->best_month_rank,
              DENSE_RANK(PARTITION BY user_id ORDER BY month_income ASC, month_no ASC)->worst_month_rank}(MI)

AI <- gamma_{user_id;
             SUM(month_income)->total_income,
             AVG(month_income)->avg_monthly_income,
             STDDEV_SAMP(month_income)->income_stddev,
             MAX(month_income)->best_month_income,
             MIN(month_income)->worst_month_income,
             COUNT_IF(month_income>0)->active_income_months}(MI)

BWM <- gamma_{user_id;
              MAX_IF(month_no, best_month_rank=1)->best_month_no,
              MAX_IF(month_no, worst_month_rank=1)->worst_month_no}(MIR)

R0 <- FB bowtie_{FB.user_id=AI.user_id} AI bowtie_{FB.user_id=BWM.user_id} BWM
R1 <- alpha_{(spending_budget+saving_budget+investing_budget+donation_budget)*12->planned_annual_budget,
             total_income->actual_annual_income,
             income_stddev/NULLIF(avg_monthly_income,0)->income_volatility_cv,
             total_income-(spending_budget*12)->annual_free_cash_after_spending,
             (spending_budget*12)/NULLIF(total_income,0)->spending_pressure_ratio,
             credit/NULLIF(total_income,0)->leverage_ratio}(R0)
R  <- omega_{ORDER BY annual_free_cash_after_spending DESC,
                    spending_pressure_ratio ASC,
                    user_id ASC;
             DENSE_RANK()->finance_resilience_rank}(R1)
}}}

=== 2. Детален годишен извештај за конзистентност на тренинг, оптоварување и тренд на перформанс

==== SQL
{{{
SET search_path TO trekr;

WITH params AS (
    SELECT 2026::int AS report_year
),
months AS (
    SELECT generate_series(1, 12) AS month_no
),
training_base AS (
    SELECT
        tu.user_id,
        u.username,
        u.email,
        tu.gender,
        tu.age,
        tu.weight
    FROM training_users tu
    JOIN users u ON u.user_id = tu.user_id
),
monthly_sessions AS (
    SELECT
        tb.user_id,
        m.month_no,
        COALESCE(COUNT(ts.training_id), 0) AS sessions_count,
        COALESCE(SUM(ts.duration), 0) AS total_duration_minutes,
        COALESCE(SUM(ts.calories), 0) AS total_calories,
        COALESCE(AVG(ts.duration), 0) AS avg_session_duration,
        COALESCE(AVG(ts.calories), 0) AS avg_session_calories
    FROM training_base tb
    CROSS JOIN months m
    LEFT JOIN training_sessions ts
        ON ts.training_user_id = tb.user_id
       AND EXTRACT(YEAR FROM ts.date)::int = (SELECT report_year FROM params)
       AND EXTRACT(MONTH FROM ts.date)::int = m.month_no
    GROUP BY tb.user_id, m.month_no
),
monthly_ranked AS (
    SELECT
        ms.*,
        DENSE_RANK() OVER (PARTITION BY ms.user_id ORDER BY ms.total_calories DESC, ms.month_no ASC) AS peak_calorie_month_rank,
        DENSE_RANK() OVER (PARTITION BY ms.user_id ORDER BY ms.sessions_count DESC, ms.month_no ASC) AS peak_sessions_month_rank
    FROM monthly_sessions ms
),
active_month_streaks AS (
    SELECT
        user_id,
        month_no,
        month_no - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY month_no) AS grp
    FROM monthly_sessions
    WHERE sessions_count > 0
),
longest_streak AS (
    SELECT
        user_id,
        MAX(streak_len) AS longest_active_month_streak
    FROM (
        SELECT user_id, grp, COUNT(*) AS streak_len
        FROM active_month_streaks
        GROUP BY user_id, grp
    ) s
    GROUP BY user_id
),
annual_training AS (
    SELECT
        user_id,
        SUM(sessions_count) AS annual_sessions,
        SUM(total_duration_minutes) AS annual_duration_minutes,
        SUM(total_calories) AS annual_calories,
        AVG(total_duration_minutes) AS avg_monthly_duration,
        AVG(total_calories) AS avg_monthly_calories,
        COUNT(*) FILTER (WHERE sessions_count > 0) AS active_months,
        REGR_SLOPE(total_calories::numeric, month_no::numeric) AS calories_trend_slope,
        REGR_SLOPE(total_duration_minutes::numeric, month_no::numeric) AS duration_trend_slope
    FROM monthly_sessions
    GROUP BY user_id
),
peak_months AS (
    SELECT
        user_id,
        MAX(month_no) FILTER (WHERE peak_calorie_month_rank = 1) AS peak_calorie_month_no,
        MAX(month_no) FILTER (WHERE peak_sessions_month_rank = 1) AS peak_sessions_month_no
    FROM monthly_ranked
    GROUP BY user_id
)
SELECT
    tb.user_id,
    tb.username,
    tb.email,
    tb.gender,
    tb.age,
    tb.weight,
    at.annual_sessions,
    ROUND(at.annual_duration_minutes::numeric, 2) AS annual_duration_minutes,
    ROUND(at.annual_calories::numeric, 2) AS annual_calories,
    at.active_months,
    ROUND((at.active_months / 12.0)::numeric, 4) AS consistency_ratio,
    COALESCE(ls.longest_active_month_streak, 0) AS longest_active_month_streak,
    pm.peak_calorie_month_no,
    pm.peak_sessions_month_no,
    ROUND(COALESCE(at.calories_trend_slope, 0)::numeric, 4) AS calories_trend_slope,
    ROUND(COALESCE(at.duration_trend_slope, 0)::numeric, 4) AS duration_trend_slope,
    DENSE_RANK() OVER (
        ORDER BY
            at.annual_calories DESC,
            at.active_months DESC,
            COALESCE(ls.longest_active_month_streak, 0) DESC,
            tb.user_id ASC
    ) AS training_annual_rank
FROM training_base tb
JOIN annual_training at ON at.user_id = tb.user_id
JOIN peak_months pm ON pm.user_id = tb.user_id
LEFT JOIN longest_streak ls ON ls.user_id = tb.user_id
ORDER BY training_annual_rank, tb.user_id;
}}}

==== Релациона Алгебра
{{{
TB <- pi_{tu.user_id, u.username, u.email, tu.gender, tu.age, tu.weight}
      (training_users tu bowtie_{tu.user_id = u.user_id} users u)

TBM <- TB x M
TSY <- sigma_{YEAR(ts.date)=Y}(training_sessions ts)
MS0 <- TBM leftouterjoin_{TBM.user_id = ts.training_user_id AND TBM.month_no = MONTH(ts.date)} TSY
MS <- gamma_{user_id, month_no;
             COUNT(ts.training_id)->sessions_count,
             SUM(COALESCE(ts.duration,0))->total_duration_minutes,
             SUM(COALESCE(ts.calories,0))->total_calories,
             AVG(COALESCE(ts.duration,0))->avg_session_duration,
             AVG(COALESCE(ts.calories,0))->avg_session_calories}(MS0)

MR <- omega_{PARTITION BY user_id ORDER BY total_calories DESC, month_no ASC;
             DENSE_RANK()->peak_calorie_month_rank,
             DENSE_RANK(PARTITION BY user_id ORDER BY sessions_count DESC, month_no ASC)->peak_sessions_month_rank}(MS)

AMS <- sigma_{sessions_count>0}(MS)
AMS1 <- omega_{PARTITION BY user_id ORDER BY month_no;
               ROW_NUMBER()->rn}(AMS)
AMS2 <- alpha_{month_no - rn -> grp}(AMS1)
LS0 <- gamma_{user_id, grp; COUNT(*)->streak_len}(AMS2)
LS  <- gamma_{user_id; MAX(streak_len)->longest_active_month_streak}(LS0)

AT <- gamma_{user_id;
             SUM(sessions_count)->annual_sessions,
             SUM(total_duration_minutes)->annual_duration_minutes,
             SUM(total_calories)->annual_calories,
             AVG(total_duration_minutes)->avg_monthly_duration,
             AVG(total_calories)->avg_monthly_calories,
             COUNT_IF(sessions_count>0)->active_months,
             REGR_SLOPE(total_calories, month_no)->calories_trend_slope,
             REGR_SLOPE(total_duration_minutes, month_no)->duration_trend_slope}(MS)

PM <- gamma_{user_id;
             MAX_IF(month_no, peak_calorie_month_rank=1)->peak_calorie_month_no,
             MAX_IF(month_no, peak_sessions_month_rank=1)->peak_sessions_month_no}(MR)

R0 <- TB bowtie_{TB.user_id=AT.user_id} AT
         bowtie_{TB.user_id=PM.user_id} PM
         leftouterjoin_{TB.user_id=LS.user_id} LS
R1 <- alpha_{active_months/12.0->consistency_ratio,
             COALESCE(longest_active_month_streak,0)->longest_active_month_streak_nz,
             COALESCE(calories_trend_slope,0)->calories_trend_slope_nz,
             COALESCE(duration_trend_slope,0)->duration_trend_slope_nz}(R0)
R  <- omega_{ORDER BY annual_calories DESC,
                    active_months DESC,
                    longest_active_month_streak_nz DESC,
                    user_id ASC;
             DENSE_RANK()->training_annual_rank}(R1)
}}}

=== 3. Детален годишен извештај за дисциплина, квалитет на завршување и однесување преку streaks

==== SQL
{{{
SET search_path TO trekr;

WITH params AS (
    SELECT 2026::int AS report_year
),
discipline_base AS (
    SELECT
        du.user_id,
        u.username,
        u.email
    FROM discipline_users du
    JOIN users u ON u.user_id = du.user_id
),
task_mix AS (
    SELECT
        COALESCE(t.discipline_user_id, c.user_id) AS user_id,
        COUNT(*) AS total_tasks_defined,
        COUNT(*) FILTER (WHERE t.custom_tracking_id IS NULL) AS core_tasks,
        COUNT(*) FILTER (WHERE t.custom_tracking_id IS NOT NULL) AS custom_tasks,
        COUNT(DISTINCT COALESCE(t.custom_tracking_id::text, 'core')) AS task_category_span
    FROM tasks t
    LEFT JOIN custom_tracking_categories c
        ON c.custom_tracking_id = t.custom_tracking_id
    WHERE t.discipline_user_id IS NOT NULL
       OR t.custom_tracking_id IS NOT NULL
    GROUP BY COALESCE(t.discipline_user_id, c.user_id)
),
annual_daily_completion AS (
    SELECT
        dc.user_id,
        dc.date,
        COALESCE(dc.procent, 0) AS procent,
        CASE WHEN COALESCE(dc.procent, 0) >= 80 THEN 1 ELSE 0 END AS strong_day
    FROM daily_completion dc
    WHERE EXTRACT(YEAR FROM dc.date)::int = (SELECT report_year FROM params)
),
daily_completion_stats AS (
    SELECT
        adc.user_id,
        COUNT(*) AS tracked_days,
        AVG(adc.procent) AS avg_completion_percent,
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY adc.procent) AS median_completion_percent,
        COUNT(*) FILTER (WHERE adc.procent = 100) AS perfect_days,
        COUNT(*) FILTER (WHERE adc.procent >= 80) AS strong_days,
        STDDEV_SAMP(adc.procent) AS completion_variability
    FROM annual_daily_completion adc
    GROUP BY adc.user_id
),
strong_day_streaks AS (
    SELECT
        user_id,
        date,
        date - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date))::int AS grp
    FROM annual_daily_completion
    WHERE strong_day = 1
),
longest_strong_streak AS (
    SELECT
        user_id,
        MAX(streak_len) AS longest_strong_day_streak
    FROM (
        SELECT user_id, grp, COUNT(*) AS streak_len
        FROM strong_day_streaks
        GROUP BY user_id, grp
    ) s
    GROUP BY user_id
),
annual_task_execution AS (
    SELECT
        dc.user_id,
        COUNT(tdc.task_id) AS completed_task_events
    FROM daily_completion dc
    LEFT JOIN task_daily_completion tdc
        ON tdc.daily_completion_id = dc.daily_completion_id
    WHERE EXTRACT(YEAR FROM dc.date)::int = (SELECT report_year FROM params)
    GROUP BY dc.user_id
)
SELECT
    db.user_id,
    db.username,
    db.email,
    COALESCE(tm.total_tasks_defined, 0) AS total_tasks_defined,
    COALESCE(tm.core_tasks, 0) AS core_tasks,
    COALESCE(tm.custom_tasks, 0) AS custom_tasks,
    COALESCE(tm.task_category_span, 0) AS task_category_span,
    COALESCE(dcs.tracked_days, 0) AS tracked_days,
    ROUND(COALESCE(dcs.avg_completion_percent, 0)::numeric, 2) AS avg_completion_percent,
    ROUND(COALESCE(dcs.median_completion_percent, 0)::numeric, 2) AS median_completion_percent,
    COALESCE(dcs.perfect_days, 0) AS perfect_days,
    COALESCE(dcs.strong_days, 0) AS strong_days,
    ROUND(COALESCE(dcs.completion_variability, 0)::numeric, 4) AS completion_variability,
    COALESCE(ate.completed_task_events, 0) AS completed_task_events,
    COALESCE(lss.longest_strong_day_streak, 0) AS longest_strong_day_streak,
    ROUND(
        COALESCE((COALESCE(dcs.strong_days, 0) / NULLIF(COALESCE(dcs.tracked_days, 0), 0)::numeric), 0),
        4
    ) AS strong_day_ratio,
    ROUND(
        (
            COALESCE(dcs.avg_completion_percent, 0) * 0.45
            + COALESCE(lss.longest_strong_day_streak, 0) * 2.00
            + COALESCE(ate.completed_task_events, 0) * 0.35
        )::numeric,
        2
    ) AS discipline_composite_score,
    DENSE_RANK() OVER (
        ORDER BY
            (
                COALESCE(dcs.avg_completion_percent, 0) * 0.45
                + COALESCE(lss.longest_strong_day_streak, 0) * 2.00
                + COALESCE(ate.completed_task_events, 0) * 0.35
            ) DESC,
            db.user_id ASC
    ) AS discipline_annual_rank
FROM discipline_base db
LEFT JOIN task_mix tm ON tm.user_id = db.user_id
LEFT JOIN daily_completion_stats dcs ON dcs.user_id = db.user_id
LEFT JOIN annual_task_execution ate ON ate.user_id = db.user_id
LEFT JOIN longest_strong_streak lss ON lss.user_id = db.user_id
ORDER BY discipline_annual_rank, db.user_id;
}}}

==== Релациона Алгебра
{{{
DB <- pi_{du.user_id, u.username, u.email}
      (discipline_users du bowtie_{du.user_id = u.user_id} users u)

TC <- tasks t leftouterjoin_{t.custom_tracking_id = c.custom_tracking_id} custom_tracking_categories c
TM0 <- alpha_{COALESCE(t.discipline_user_id, c.user_id)->owner_user_id}(TC)
TM1 <- sigma_{t.discipline_user_id IS NOT NULL OR t.custom_tracking_id IS NOT NULL}(TM0)
TM <- gamma_{owner_user_id;
             COUNT(*)->total_tasks_defined,
             COUNT_IF(t.custom_tracking_id IS NULL)->core_tasks,
             COUNT_IF(t.custom_tracking_id IS NOT NULL)->custom_tasks,
             COUNT_DISTINCT(COALESCE(t.custom_tracking_id,'core'))->task_category_span}(TM1)

ADC0 <- sigma_{YEAR(dc.date)=Y}(daily_completion dc)
ADC <- alpha_{COALESCE(dc.procent,0)->procent,
              CASE(procent>=80,1,0)->strong_day}(ADC0)

DCS <- gamma_{user_id;
              COUNT(*)->tracked_days,
              AVG(procent)->avg_completion_percent,
              PERCENTILE_CONT_0_5(procent)->median_completion_percent,
              COUNT_IF(procent=100)->perfect_days,
              COUNT_IF(procent>=80)->strong_days,
              STDDEV_SAMP(procent)->completion_variability}(ADC)

SDS0 <- sigma_{strong_day=1}(ADC)
SDS1 <- omega_{PARTITION BY user_id ORDER BY date; ROW_NUMBER()->rn}(SDS0)
SDS2 <- alpha_{date - rn -> grp}(SDS1)
LSS0 <- gamma_{user_id, grp; COUNT(*)->streak_len}(SDS2)
LSS  <- gamma_{user_id; MAX(streak_len)->longest_strong_day_streak}(LSS0)

ATE0 <- ADC0 leftouterjoin_{ADC0.daily_completion_id = tdc.daily_completion_id} task_daily_completion tdc
ATE  <- gamma_{ADC0.user_id; COUNT(tdc.task_id)->completed_task_events}(ATE0)

R0 <- DB
      leftouterjoin_{DB.user_id = TM.owner_user_id} TM
      leftouterjoin_{DB.user_id = DCS.user_id} DCS
      leftouterjoin_{DB.user_id = ATE.user_id} ATE
      leftouterjoin_{DB.user_id = LSS.user_id} LSS
R1 <- alpha_{COALESCE(total_tasks_defined,0)->total_tasks_defined_nz,
             COALESCE(core_tasks,0)->core_tasks_nz,
             COALESCE(custom_tasks,0)->custom_tasks_nz,
             COALESCE(task_category_span,0)->task_category_span_nz,
             COALESCE(tracked_days,0)->tracked_days_nz,
             COALESCE(avg_completion_percent,0)->avg_completion_percent_nz,
             COALESCE(median_completion_percent,0)->median_completion_percent_nz,
             COALESCE(perfect_days,0)->perfect_days_nz,
             COALESCE(strong_days,0)->strong_days_nz,
             COALESCE(completion_variability,0)->completion_variability_nz,
             COALESCE(completed_task_events,0)->completed_task_events_nz,
             COALESCE(longest_strong_day_streak,0)->longest_strong_day_streak_nz,
             COALESCE(strong_days/NULLIF(tracked_days,0),0)->strong_day_ratio,
             (COALESCE(avg_completion_percent,0)*0.45 +
              COALESCE(longest_strong_day_streak,0)*2.00 +
              COALESCE(completed_task_events,0)*0.35)->discipline_composite_score}(R0)
R  <- omega_{ORDER BY discipline_composite_score DESC, user_id ASC;
             DENSE_RANK()->discipline_annual_rank}(R1)
}}}

=== 4. Детален годишен извештај за инвестициска диверзификација, концентрација и темпо на вложување

==== SQL
{{{
SET search_path TO trekr;

WITH params AS (
    SELECT 2026::int AS report_year
),
months AS (
    SELECT generate_series(1, 12) AS month_no
),
investor_base AS (
    SELECT
        iu.user_id,
        u.username,
        u.email
    FROM investor_users iu
    JOIN users u ON u.user_id = iu.user_id
),
annual_asset_lots AS (
    SELECT
        a.user_id,
        a.ticker_symbol,
        COALESCE(a.quantity, 0) AS quantity,
        COALESCE(a.buy_price, 0) AS buy_price,
        COALESCE(a.quantity, 0) * COALESCE(a.buy_price, 0) AS invested_amount,
        a.buy_date
    FROM assets a
    WHERE EXTRACT(YEAR FROM a.buy_date)::int = (SELECT report_year FROM params)
),
ticker_rollup AS (
    SELECT
        aal.user_id,
        aal.ticker_symbol,
        SUM(aal.quantity) AS total_quantity,
        SUM(aal.invested_amount) AS total_invested_amount,
        COUNT(*) AS lot_count,
        MIN(aal.buy_date) AS first_buy_date,
        MAX(aal.buy_date) AS last_buy_date
    FROM annual_asset_lots aal
    GROUP BY aal.user_id, aal.ticker_symbol
),
portfolio_totals AS (
    SELECT
        user_id,
        SUM(total_invested_amount) AS annual_total_invested,
        SUM(lot_count) AS annual_lot_count,
        COUNT(*) AS distinct_tickers
    FROM ticker_rollup
    GROUP BY user_id
),
weights AS (
    SELECT
        tr.user_id,
        tr.ticker_symbol,
        tr.total_invested_amount,
        pt.annual_total_invested,
        (tr.total_invested_amount / NULLIF(pt.annual_total_invested, 0)) AS position_weight,
        DENSE_RANK() OVER (
            PARTITION BY tr.user_id
            ORDER BY tr.total_invested_amount DESC, tr.ticker_symbol ASC
        ) AS position_rank
    FROM ticker_rollup tr
    JOIN portfolio_totals pt ON pt.user_id = tr.user_id
),
concentration AS (
    SELECT
        user_id,
        SUM(position_weight * position_weight) AS hhi_concentration,
        MAX(position_weight) AS top_position_weight,
        MAX(ticker_symbol) FILTER (WHERE position_rank = 1) AS top_ticker
    FROM weights
    GROUP BY user_id
),
monthly_investment AS (
    SELECT
        ib.user_id,
        m.month_no,
        COALESCE(SUM(a.quantity * a.buy_price), 0) AS monthly_invested_amount
    FROM investor_base ib
    CROSS JOIN months m
    LEFT JOIN assets a
        ON a.user_id = ib.user_id
       AND EXTRACT(YEAR FROM a.buy_date)::int = (SELECT report_year FROM params)
       AND EXTRACT(MONTH FROM a.buy_date)::int = m.month_no
    GROUP BY ib.user_id, m.month_no
),
monthly_investment_stats AS (
    SELECT
        user_id,
        AVG(monthly_invested_amount) AS avg_monthly_contribution,
        STDDEV_SAMP(monthly_invested_amount) AS contribution_stddev,
        COUNT(*) FILTER (WHERE monthly_invested_amount > 0) AS active_investing_months
    FROM monthly_investment
    GROUP BY user_id
)
SELECT
    ib.user_id,
    ib.username,
    ib.email,
    COALESCE(pt.annual_total_invested, 0) AS annual_total_invested,
    COALESCE(pt.annual_lot_count, 0) AS annual_lot_count,
    COALESCE(pt.distinct_tickers, 0) AS distinct_tickers,
    ROUND(COALESCE(ms.avg_monthly_contribution, 0)::numeric, 2) AS avg_monthly_contribution,
    COALESCE(ms.active_investing_months, 0) AS active_investing_months,
    ROUND((COALESCE(ms.active_investing_months, 0) / 12.0)::numeric, 4) AS activity_ratio,
    ROUND(COALESCE(c.hhi_concentration, 0)::numeric, 4) AS hhi_concentration,
    ROUND((1 - COALESCE(c.hhi_concentration, 1))::numeric, 4) AS diversification_index,
    ROUND(COALESCE(c.top_position_weight, 0)::numeric, 4) AS top_position_weight,
    c.top_ticker,
    ROUND((COALESCE(ms.contribution_stddev, 0) / NULLIF(ms.avg_monthly_contribution, 0))::numeric, 4) AS contribution_volatility_cv,
    DENSE_RANK() OVER (
        ORDER BY
            (1 - COALESCE(c.hhi_concentration, 1)) DESC,
            COALESCE(pt.annual_total_invested, 0) DESC,
            COALESCE(ms.active_investing_months, 0) DESC,
            ib.user_id ASC
    ) AS investing_annual_rank
FROM investor_base ib
LEFT JOIN portfolio_totals pt ON pt.user_id = ib.user_id
LEFT JOIN concentration c ON c.user_id = ib.user_id
LEFT JOIN monthly_investment_stats ms ON ms.user_id = ib.user_id
ORDER BY investing_annual_rank, ib.user_id;
}}}

==== Релациона Алгебра
{{{
IB <- pi_{iu.user_id, u.username, u.email}
      (investor_users iu bowtie_{iu.user_id = u.user_id} users u)

AAL <- pi_{a.user_id, a.ticker_symbol,
           COALESCE(a.quantity,0)->quantity,
           COALESCE(a.buy_price,0)->buy_price,
           COALESCE(a.quantity,0)*COALESCE(a.buy_price,0)->invested_amount,
           a.buy_date}
       (sigma_{YEAR(a.buy_date)=Y}(assets a))

TR <- gamma_{user_id, ticker_symbol;
             SUM(quantity)->total_quantity,
             SUM(invested_amount)->total_invested_amount,
             COUNT(*)->lot_count,
             MIN(buy_date)->first_buy_date,
             MAX(buy_date)->last_buy_date}(AAL)

PT <- gamma_{user_id;
             SUM(total_invested_amount)->annual_total_invested,
             SUM(lot_count)->annual_lot_count,
             COUNT(*)->distinct_tickers}(TR)

W0 <- TR bowtie_{TR.user_id = PT.user_id} PT
W1 <- alpha_{total_invested_amount/NULLIF(annual_total_invested,0)->position_weight}(W0)
W  <- omega_{PARTITION BY user_id ORDER BY total_invested_amount DESC, ticker_symbol ASC;
             DENSE_RANK()->position_rank}(W1)

C <- gamma_{user_id;
            SUM(position_weight*position_weight)->hhi_concentration,
            MAX(position_weight)->top_position_weight,
            MAX_IF(ticker_symbol, position_rank=1)->top_ticker}(W)

IBM <- IB x M
AY  <- sigma_{YEAR(a.buy_date)=Y}(assets a)
MI0 <- IBM leftouterjoin_{IBM.user_id=a.user_id AND IBM.month_no=MONTH(a.buy_date)} AY
MI  <- gamma_{user_id, month_no;
              SUM(COALESCE(a.quantity,0)*COALESCE(a.buy_price,0))->monthly_invested_amount}(MI0)
MS  <- gamma_{user_id;
              AVG(monthly_invested_amount)->avg_monthly_contribution,
              STDDEV_SAMP(monthly_invested_amount)->contribution_stddev,
              COUNT_IF(monthly_invested_amount>0)->active_investing_months}(MI)

R0 <- IB
      leftouterjoin_{IB.user_id=PT.user_id} PT
      leftouterjoin_{IB.user_id=C.user_id} C
      leftouterjoin_{IB.user_id=MS.user_id} MS
R1 <- alpha_{COALESCE(annual_total_invested,0)->annual_total_invested_nz,
             COALESCE(annual_lot_count,0)->annual_lot_count_nz,
             COALESCE(distinct_tickers,0)->distinct_tickers_nz,
             COALESCE(avg_monthly_contribution,0)->avg_monthly_contribution_nz,
             COALESCE(active_investing_months,0)->active_investing_months_nz,
             COALESCE(active_investing_months,0)/12.0->activity_ratio,
             COALESCE(hhi_concentration,0)->hhi_concentration_nz,
             1-COALESCE(hhi_concentration,1)->diversification_index,
             COALESCE(top_position_weight,0)->top_position_weight_nz,
             COALESCE(contribution_stddev/NULLIF(avg_monthly_contribution,0),0)->contribution_volatility_cv}(R0)
R  <- omega_{ORDER BY diversification_index DESC,
                    annual_total_invested_nz DESC,
                    active_investing_months_nz DESC,
                    user_id ASC;
             DENSE_RANK()->investing_annual_rank}(R1)
}}}