= Employees ordered by total hours worked and total pay
{{{#!sql
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)