source: database/Advanced Reports for database/Each store's average pay.txt@ 62b2964

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;
$$;