Top 10 employees who have answered the most amount of request in the last month

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)