Месечни приходи и расходи

Опис

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

SQL решение

CREATE OR REPLACE FUNCTION get_monthly_revenue()
RETURNS TABLE (
    month TEXT,
    order_count BIGINT,
    total_revenue NUMERIC,
    avg_payment NUMERIC,
    tables_used BIGINT,
    waiters_active BIGINT
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT 
        TO_CHAR(p.payment_date, 'YYYY-MM')::TEXT AS month,
        COUNT(DISTINCT o.order_id) AS order_count,
        SUM(p.amount) AS total_revenue,
        ROUND(AVG(p.amount), 2) AS avg_payment,
        COUNT(DISTINCT o.table_id) AS tables_used,
        COUNT(DISTINCT o.user_id) AS waiters_active
    FROM project.payment p
    JOIN project.orders o ON o.order_id = p.order_id
    WHERE o.status = 'ПЛАТЕНА'
    GROUP BY TO_CHAR(p.payment_date, 'YYYY-MM')
    ORDER BY month DESC;
END;
$$;

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

P(payment_id, order_id, amount, payment_date)
O(order_id, table_id, user_id, status)

J1 ← P ⨝ P.order_id = O.order_id O

F1 ← σ status='ПЛАТЕНА' (J1)

G ← γ month;
     COUNT(DISTINCT order_id) → order_count,
     SUM(amount) → total_revenue,
     AVG(amount) → avg_payment,
     COUNT(DISTINCT table_id) → tables_used,
     COUNT(DISTINCT user_id) → waiters_active (F1)

R ← π month, order_count, total_revenue, avg_payment, tables_used, waiters_active (G)

R_final ← τ month DESC (R)