Најпрофитабилни категории по месеци

Опис

Извештајот ги прикажува сите категории на производи, групирани по месец, со вкупниот приход и бројот на продадени артикли. Овозможува анализа на сезонските трендови по категорија.

SQL решение

CREATE OR REPLACE FUNCTION get_category_revenue_by_month()
RETURNS TABLE (
    category_id INT,
    category_name TEXT,
    month TEXT,
    total_items_sold BIGINT,
    total_revenue NUMERIC,
    order_count BIGINT
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT 
        c.category_id,
        c.name::TEXT AS category_name,
        TO_CHAR(o.created_at, 'YYYY-MM')::TEXT AS month,
        SUM(oi.quantity) AS total_items_sold,
        SUM(oi.quantity * oi.unit_price) AS total_revenue,
        COUNT(DISTINCT o.order_id) AS order_count
    FROM project.order_item oi
    JOIN project.orders o ON o.order_id = oi.order_id
    JOIN project.product p ON p.product_id = oi.product_id
    JOIN project.category c ON c.category_id = p.category_id
    WHERE o.status = 'ПЛАТЕНА'
      AND o.created_at >= NOW() - INTERVAL '12 months'
    GROUP BY c.category_id, c.name, TO_CHAR(o.created_at, 'YYYY-MM')
    ORDER BY month DESC, total_revenue DESC;
END;
$$;

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

P(product_id, category_id)
C(category_id, name)
O(order_id, status, created_at)
OI(order_id, product_id, quantity, unit_price)

J1 ← OI ⨝ OI.order_id = O.order_id O
J2 ← J1 ⨝ OI.product_id = P.product_id P
J3 ← J2 ⨝ P.category_id = C.category_id C

F1 ← σ status='ПЛАТЕНА' ∧ created_at ≥ NOW() - INTERVAL '12 months' (J3)

G ← γ category_id, category_name, month;
     SUM(quantity) → total_items_sold,
     SUM(quantity * unit_price) → total_revenue,
     COUNT(DISTINCT order_id) → order_count (F1)

R ← π category_id, category_name, month, total_items_sold, total_revenue, order_count (G)

R_final ← τ month DESC, total_revenue DESC (R)