Changes between Initial Version and Version 1 of AdvancedReport11

Timestamp:

08/21/26 06:21:02 (29 hours ago)

Author:

235018

Comment:

--

AdvancedReport11

@@ +1 @@
+= Approximate number of orders per client
+{{{#!sql
+CREATE OR REPLACE FUNCTION get_approximate_orders_per_client()
+RETURNS TABLE (
+    total_clients BIGINT,
+    total_orders BIGINT,
+    approximate_orders_per_client NUMERIC
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    RETURN QUERY
+    SELECT
+        (SELECT COUNT(*) FROM client) AS total_clients,
+        (SELECT COUNT(*) FROM "order") AS total_orders,
+        ROUND(
+            (SELECT COUNT(*) FROM "order")::NUMERIC
+            / NULLIF((SELECT COUNT(*) FROM client), 0),
+            2
+        ) AS approximate_orders_per_client;
+END;
+$$;
+
+}}}
+
+== Relational Algebra
+- O(order_num, quantity, status, last_modified_date, payment_method, discount)
+- C(client_id, name, first_name, last_name, email, password, delivery_address)
+
+**Calculate total number of orders:**
+- O_total ← γCOUNT(order_num) → total_orders(O)
+
+**Calculate total number of clients:**
+- C_total ← γCOUNT(client_id) → total_clients(C)
+
+**Calculate approximate number of orders per client:**
+- R ← γclient_id, name;
+  R ← πtotal_clients,
+     total_orders,
+     total_orders / total_clients
+     → approximate_orders_per_client
+     (C_total × O_total)
+
+