Changes between Initial Version and Version 1 of AdvancedReport13

Timestamp:

08/21/26 06:33:05 (29 hours ago)

Author:

235018

Comment:

--

AdvancedReport13

@@ +1 @@
+= Each store's number of request, including how many have been solved, and how many are still in progress
+
+{{{#!sql
+CREATE OR REPLACE FUNCTION get_store_request_statistics()
+RETURNS TABLE (
+    store_id INT,
+    store_name TEXT,
+    total_requests BIGINT,
+    solved_requests BIGINT,
+    requests_in_progress BIGINT
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    RETURN QUERY
+    SELECT
+        s.store_id,
+        s.name AS store_name,
+        COUNT(r.request_num) AS total_requests,
+        COUNT(
+            CASE
+                WHEN r.customer_satisfaction IS NOT NULL
+                THEN 1
+            END
+        ) AS solved_requests,
+        COUNT(
+            CASE
+                WHEN r.customer_satisfaction IS NULL
+                THEN 1
+            END
+        ) AS requests_in_progress
+    FROM store s
+    LEFT JOIN for_store fs
+        ON s.store_id = fs.store_id
+    LEFT JOIN request r
+        ON fs.request_num = r.request_num
+    GROUP BY
+        s.store_id,
+        s.name
+    ORDER BY
+        total_requests DESC;
+END;
+$$;
+
+}}}
+
+== Relational Algebra
+- S(store_id, name, date_of_founding, physical_address, store_email, rating)
+- FS(request_num, store_id)
+- R(request_num, date_and_time, problem, notes_of_communication, customer_satisfaction)
+
+**JOIN stores with their requests:**
+- J1 ← S ⟕S.store_id = FS.store_id FS
+- J2 ← J1 ⟕FS.request_num = R.request_num R
+
+**Calculate total number of requests for each store:**
+- total_requests = COUNT(request_num)
+
+**Calculate solved and unsolved requests:**
+- solved_requests = COUNT(request_num) WHERE customer_satisfaction IS NOT NULL
+- requests_in_progress = COUNT(request_num) WHERE customer_satisfaction IS NULL
+- R1 ← γstore_id, name;
+     COUNT(request_num) → total_requests,
+     COUNT(customer_satisfaction) → solved_requests,
+     COUNT(request_num) - COUNT(customer_satisfaction)
+         → requests_in_progress
+     (J2)
+
+**Sort by total number of requests:**
+- R_final ← τtotal_requests DESC(R1)
+
+