Changes between Initial Version and Version 1 of AdvancedReport14

Timestamp:

08/21/26 06:40:22 (28 hours ago)

Author:

235018

Comment:

--

AdvancedReport14

@@ +1 @@
+= Top 10 employees who have answered the most amount of request in the last month
+{{{#!sql
+CREATE OR REPLACE FUNCTION get_top_10_employees_by_requests_last_month()
+RETURNS TABLE (
+    ssn BIGINT,
+    employee_name TEXT,
+    number_of_requests BIGINT
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    RETURN QUERY
+    SELECT
+        e.ssn,
+        p.name AS employee_name,
+        COUNT(DISTINCT a.request_num) AS number_of_requests
+    FROM employees e
+    JOIN personal p
+        ON e.ssn = p.ssn
+    JOIN answers a
+        ON e.ssn = a.ssn
+    JOIN request r
+        ON a.request_num = r.request_num
+    WHERE r.date_and_time >= date_trunc('month', CURRENT_DATE) - INTERVAL '1 month'
+      AND r.date_and_time < date_trunc('month', CURRENT_DATE)
+    GROUP BY
+        e.ssn,
+        p.name
+    ORDER BY
+        number_of_requests DESC
+    LIMIT 10;
+END;
+$$;
+
+}}}
+
+== Relational Algebra
+- E(employee_id, date_of_hire)
+- P(id, name, first_name, last_name, email, password, permissions, type, authorisation)
+- A(request_num, id)
+- R(request_num, date_and_time, problem, notes_of_communication, customer_satisfaction)
+
+**JOIN employees with their personal information:**
+- J1 ← E ⨝E.empployee_id = P.id P
+- J2 ← J1 ⨝E.emplyee_id = A.id A
+
+**JOIN employees with corresponding requests:**
+- J3 ← J2 ⨝A.request_num = R.request_num R
+
+**SELECT requests completed from the last calendar month:**
+- F1 ← σ
+date_and_time ≥ START_OF_CURRENT_MONTH - 1 MONTH
+∧
+date_and_time < START_OF_CURRENT_MONTH
+(J3)
+
+**Calculate total number of distinct requests each employee has answered:**
+- R1 ← γSSN, name;
+     COUNT(DISTINCT request_num) → number_of_requests
+     (F1)
+
+**Sort by number of requests answered:**
+- R2 ← τnumber_of_requests DESC(R1)
+
+**Return only TOP 10 employees:**
+- R_final ← LIMIT 10(R2)
+
+