Changes between Initial Version and Version 1 of AdvancedReport10

Timestamp:

08/21/26 06:13:28 (29 hours ago)

Author:

235018

Comment:

--

AdvancedReport10

@@ +1 @@
+= Clients ordered by number of orders
+{{{#!sql
+CREATE OR REPLACE FUNCTION get_clients_by_number_of_orders()
+RETURNS TABLE (
+    client_id INT,
+    client_name TEXT,
+    number_of_orders BIGINT
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    RETURN QUERY
+    SELECT
+        c.client_id,
+        c.name AS client_name,
+        COUNT(DISTINCT o.order_num) AS number_of_orders
+    FROM client c
+    JOIN makes_order mo
+        ON c.client_id = mo.client_id
+    JOIN "order" o
+        ON mo.order_num = o.order_num
+    GROUP BY
+        c.client_id,
+        c.name
+    ORDER BY
+        number_of_orders DESC;
+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)
+- MO(client_id, order_num)
+
+
+**JOIN clients with their orders:**
+- J1 ← C ⨝C.client_id = MO.client_id MO
+- J2 ← J1 ⨝MO.order_num = O.order_num O
+
+**Calculate number of distinct orders for each lient:**
+- R ← γclient_id, name;
+     COUNT(DISTINCT order_num) → number_of_orders
+     (J2)
+
+**Sort by total numebr of orders:**
+- R_final ← τnumber_of_orders DESC(R)
+
+