Најпрофитабилни производи во последните 12 месеци

Опис

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

SQL решение

CREATE OR REPLACE FUNCTION get_top_products_last_12_months()
RETURNS TABLE (
    product_id INT,
    product_name TEXT,
    category_name TEXT,
    total_quantity BIGINT,
    total_revenue NUMERIC,
    avg_price NUMERIC,
    order_count BIGINT
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT 
        p.product_id,
        p.name::TEXT AS product_name,
        c.name::TEXT AS category_name,
        SUM(oi.quantity) AS total_quantity,
        SUM(oi.quantity * oi.unit_price) AS total_revenue,
        ROUND(AVG(oi.unit_price), 2) AS avg_price,
        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 p.product_id, p.name, c.name
    ORDER BY total_revenue DESC;
END;
$$;

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

P(product_id, name, 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 ← γ product_id, name, category_name;
     SUM(quantity) → total_quantity,
     SUM(quantity * unit_price) → total_revenue,
     AVG(unit_price) → avg_price,
     COUNT(DISTINCT order_id) → order_count (F1)

R ← π product_id, product_name, category_name, total_quantity, total_revenue, avg_price, order_count (G)

R_final ← τ total_revenue DESC (R)