Employees ordered by total hours worked and total pay

CREATE OR REPLACE FUNCTION get_employees_by_hours_and_pay()
RETURNS TABLE (
    ssn BIGINT,
    employee_name TEXT,
    total_hours_worked NUMERIC,
    total_pay NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT
        e.ssn,
        p.name AS employee_name,
        COALESCE(SUM(w.working_hours), 0) AS total_hours_worked,
        COALESCE(SUM(w.wage), 0) AS total_pay
    FROM employees e
    JOIN personal p
        ON e.ssn = p.ssn
    LEFT JOIN worked w
        ON e.ssn = w.ssn
    GROUP BY
        e.ssn,
        p.name
    ORDER BY
        total_hours_worked DESC,
        total_pay DESC;
END;
$$;

Relational Algebra

• E(employee_id, date_of_hire)
• P(id, name, first_name, last_name, email, password, permissions, type, authorisation)
• W(id, date, store_ID, week, total_week, pay_method, wage, working_hours)

JOIN employees with personal information:

• J1 ← E ⨝E.employee_id = P.idP

JOIN employees with work records:

• J2 ← J1 ⟕E.employee_id= W.id W

Calculate total hours and pay for each employee:

• total_hours_worked = Σ(working_hours)
• total_pay = Σ(wage)
• R ← γSSN, name;

Σ(working_hours) → total_hours_worked, Σ(wage) → total_pay (J2)

Sort by total hours worked and total pay:

• R_final ← τtotal_hours_worked DESC,

total_pay DESC(R)