= Each store's average pay
{{{#!sql
CREATE OR REPLACE FUNCTION get_stores_average_pay()
RETURNS TABLE (
    store_id INT,
    store_name TEXT,
    average_pay NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT
        s.store_id,
        s.name AS store_name,
        COALESCE(AVG(w.wage), 0) AS average_pay
    FROM store s
    LEFT JOIN worked w
        ON s.store_id = w.store_id
    GROUP BY
        s.store_id,
        s.name
    ORDER BY
        average_pay DESC;
END;
$$;

}}}

== Relational Algebra
- S(store_id, name, date_of_founding, physical_address, store_email, rating)
- W(id, date, store_ID, week, total_week, pay_method, wage, working_hours)

**JOIN stores with employee records:**
- J1 ← S ⟕S.store_id = W.store_ID W

**Calculate average wage for each store:**
- R ← γstore_id, name;
     AVG(wage) → average_pay
     (J1)

**Sort by average wage:**
- R_final ← τaverage_pay DESC(R)