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

== Опис ==

Извештајот го споредува приходот во секој месец од тековната година со истиот месец од претходната година, со процент на промена. Користи CTE со две нивоа и прозорска функција RANK за рангирање на месеците.

== SQL решение ==

{{{
CREATE OR REPLACE FUNCTION project.get_yoy_monthly_comparison()
RETURNS TABLE (
    month_num INT,
    month_name TEXT,
    current_year_revenue NUMERIC,
    previous_year_revenue NUMERIC,
    change_pct NUMERIC,
    rank_in_year INT
)
LANGUAGE sql
AS $$
    WITH monthly_rev AS (
        SELECT 
            EXTRACT(YEAR FROM p.payment_date)::INT AS yr,
            EXTRACT(MONTH FROM p.payment_date)::INT AS mo,
            SUM(p.amount)::numeric AS revenue
        FROM project.payment p
        JOIN project.orders o ON o.order_id = p.order_id
        WHERE o.status = 'ПЛАТЕНА'
        GROUP BY 1, 2
    ),
    yoy AS (
        SELECT 
            curr.yr, curr.mo, curr.revenue AS curr_rev,
            prev.revenue AS prev_rev,
            ROUND((100.0 * (curr.revenue - prev.revenue) 
                  / NULLIF(prev.revenue, 0))::numeric, 2) AS change_pct
        FROM monthly_rev curr
        LEFT JOIN monthly_rev prev 
            ON prev.yr = curr.yr - 1 AND prev.mo = curr.mo
        WHERE curr.yr = EXTRACT(YEAR FROM NOW())::INT
    ),
    ranked AS (
        SELECT yr, mo, curr_rev, prev_rev, change_pct,
               RANK() OVER (ORDER BY curr_rev DESC) AS rnk
        FROM yoy
    )
    SELECT 
        mo, 
        TO_CHAR(TO_DATE(mo::TEXT, 'MM'), 'Month')::TEXT,
        curr_rev, 
        COALESCE(prev_rev, 0), 
        COALESCE(change_pct, 0),
        rnk::INT
    FROM ranked
    WHERE curr_rev > (
        SELECT AVG(revenue) FROM monthly_rev
        WHERE yr = EXTRACT(YEAR FROM NOW())::INT
    )
    ORDER BY 1;
$$;
}}}

== Релациона алгебра ==

{{{
P(payment_id, order_id, amount, payment_date)
O(order_id, status)

J1 ← P ⨝ P.order_id = O.order_id O
F1 ← σ status='ПЛАТЕНА' (J1)

G1 ← γ yr, mo; SUM(amount) → revenue (F1)

J2 ← G1 ⨝ G1.yr = G1b.yr + 1 ∧ G1.mo = G1b.mo G1b
F2 ← σ yr = YEAR(NOW()) (J2)

W ← γ yr, mo; RANK() OVER (ORDER BY curr_rev DESC) → rnk (F2)
F3 ← σ curr_rev > AVG(revenue) (W)

R ← π month_num, month_name, current_year_revenue,
      previous_year_revenue, change_pct, rank_in_year (F3)

R_final ← τ month_num ASC (R)
}}}