List of reports which haven't been approved

CREATE OR REPLACE FUNCTION get_unapproved_reports()
RETURNS TABLE (
    report_date DATE,
    store_id INT,
    overall_profit NUMERIC,
    month_and_year TEXT,
    profit NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT
        r.date AS report_date,
        r.store_id,
        r.overall_profit,
        r.month_and_year,
        r.profit
    FROM report r
    LEFT JOIN approves a
        ON r.date = a.date
        AND r.store_id = a.store_id
    WHERE a.date IS NULL
    ORDER BY
        r.date DESC;
END;
$$;

Relational Algebra

• R(date, store_ID, overall_profit, monthly_profit, month_and_year, profit, sales_trend, marketing_growth, owner_signature)
• A(date, id)

JOIN approves with store owner:

• J1 ← R ⟕R.date = A.date A

FILTER out all the reports for which no corresponding approval exists:

• F1 ← σSSN IS NULL(J1)

Sort by report date:

• R_final ← τdate DESC(F1)