Number of product changes each employee has made in the last month

CREATE OR REPLACE FUNCTION get_employee_product_changes_last_month()
RETURNS TABLE (
    ssn BIGINT,
    employee_name TEXT,
    number_of_product_changes BIGINT
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT
        e.ssn,
        p.name AS employee_name,
        COUNT(DISTINCT mc.date_and_time) AS number_of_product_changes
    FROM employees e
    JOIN personal p
        ON e.ssn = p.ssn
    LEFT JOIN makes_change mc
        ON e.ssn = mc.ssn
    LEFT JOIN change ch
        ON mc.date_and_time = ch.date_and_time
    WHERE
        mc.date_and_time >= date_trunc('month', CURRENT_DATE) - INTERVAL '1 month'
        AND mc.date_and_time < date_trunc('month', CURRENT_DATE)
    GROUP BY
        e.ssn,
        p.name
    ORDER BY
        number_of_product_changes DESC;
END;
$$;

Relational Algebra

• E(id, date_of_hire)
• P(id, name, first_name, last_name, email, password, permissions, type, authorisation)
• MC(id, date_and_time, permission, type, authorisation)
• CH(date_and_time, product_code, changes_made)

JOIN employees with their personal information:

• J1 ← E ⨝E.SSN = P.SSN P

JOIN employees with the changes they have made:

• J2 ← J1 ⨝E.SSN = MC.SSN MC

JOIN the changes with the corresponding product:

• J3 ← J2 ⨝MC.date_and_time = CH.date_and_time CH

SELECT only changes made during the last calendar month:

• F1 ← σ

date_and_time ≥ START_OF_CURRENT_MONTH - 1 MONTH ∧ date_and_time < START_OF_CURRENT_MONTH (J3)

Calculate total number of distinct changes made by each employee:

• R ← γSSN, name;

COUNT(DISTINCT date_and_time) → number_of_product_changes (F1)

Sort by total number of changes made:

• R_final ← τnumber_of_product_changes DESC(R)