= Stores ordered by highest approximate product review
{{{#!sql
CREATE OR REPLACE FUNCTION get_stores_by_average_review()
RETURNS TABLE (
    store_id INT,
    store_name TEXT,
    average_review NUMERIC,
    number_of_reviews BIGINT
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT
        s.store_id,
        s.name AS store_name,
        COALESCE(AVG(r.rating), 0) AS average_review,
        COUNT(r.order_num) AS number_of_reviews
    FROM store s
    LEFT JOIN sells se
        ON s.store_id = se.store_id
    LEFT JOIN includes i
        ON se.code = i.code
    LEFT JOIN "order" o
        ON i.order_num = o.order_num
    LEFT JOIN review r
        ON o.order_num = r.order_num
    GROUP BY
        s.store_id,
        s.name
    ORDER BY
        average_review DESC;
END;
$$;

}}}

== Relational Algebra
- O(order_num, quantity, status, last_modified_date, payment_method, discount)
- I(code, order_num)
- R(order_num, comment, rating, last_modified_date)
- S(store_id, name, date_of_founding, physical_address, store_email, rating)
- SE(code, store_id, quantity, discount)

**JOIN stores with their products:**
- J1 ← S ⟕S.store_id = SE.store_id SE

**JOIN products with the orders that they have been included in:**
- J2 ← J1 ⟕SE.code = I.code I
- J3 ← J2 ⟕I.order_num = O.order_num O

**JOIN orders with REVIEWS:**
- J4 ← J3 ⟕O.order_num = R.order_num R

**Calculate number of reviews and average review rating for each store:**
- R1 ← γstore_id, name;
     AVG(rating) → average_review,
     COUNT(order_num) → number_of_reviews
     (J4)

**Sort by average review:**
- R_final ← τaverage_review DESC(R1)