Changes between Initial Version and Version 1 of AdvancedReport8

Timestamp:

08/21/26 06:01:40 (29 hours ago)

Author:

235018

Comment:

--

AdvancedReport8

@@ +1 @@
+= 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)
+